By Kai2008 on

Hi,

*apologised for posting at the wrong forum*

I would like to enquire on the appropriate steps to be taken to prevent SQL injection.

I have created a form but when I try the input '' into the field, it gives me a SQL error.

I have taken a look at the user.module in the Drupal 5 Core and realised that the it is done through the function below.

Personally, I found this solution unsatisfactory for my purpose. What if a user wishes to declare his name as O'Reily? The ' sign will prevent him from doing so while the name is perfectly legitimate.

I have done a search on the site but to no avail. Can any kind souls point me to the right direction?

Basically, I am looking for some contributed module or code snippet. Thanks in advance =) 

function user_validate_name($name) {
  if (!strlen($name)) return t('You must enter a username.');
  if (substr($name, 0, 1) == ' ') return t('The username cannot begin with a space.');
  if (substr($name, -1) == ' ') return t('The username cannot end with a space.');
  if (strpos($name, '  ') !== FALSE) return t('The username cannot contain multiple spaces in a row.');
  if (ereg("[^\x80-\xF7 [:alnum:]@_.-]", $name)) return t('The username contains an illegal character.');
  if (preg_match('/[\x{80}-\x{A0}'.          // Non-printable ISO-8859-1 + NBSP
                   '\x{AD}'.                 // Soft-hyphen
                   '\x{2000}-\x{200F}'.      // Various space characters
                   '\x{2028}-\x{202F}'.      // Bidirectional text overrides
                   '\x{205F}-\x{206F}'.      // Various text hinting characters
                   '\x{FEFF}'.               // Byte order mark
                   '\x{FF01}-\x{FF60}'.      // Full-width latin
                   '\x{FFF9}-\x{FFFD}'.      // Replacement characters
                   '\x{0}]/u',               // NULL byte
                   $name)) {
    return t('The username contains an illegal character.');
  }
  if (strpos($name, '@') !== FALSE && !eregi('@([0-9a-z](-?[0-9a-z])*.)+[a-z]{2}([zmuvtg]|fo|me)?$', $name)) return t('The username is not a valid authentication ID.');
  if (strlen($name) > USERNAME_MAX_LENGTH) return t('The username %name is too long: it must be %max characters or less.', array('%name' => $name, '%max' => USERNAME_MAX_LENGTH));
}

Comments

thmo’s picture

thmo commented

I would recommend reading the drupal security pages: http://drupal.org/writing-secure-code

There are a few tips on how to prevent stuff like - SQL injection attacks. There is a database and a validate input section in the left menu.

deekayen’s picture

deekayen commented

Injections are prevented with printf style replacement in the SQL strings. See http://api.drupal.org/api/function/db_query for the specific modifiers.

Inserting a blank string using the SQL injection modifiers would be like:

$string = '';
$integer = 0;
db_query("INSERT INTO table (field, field2) VALUES ('%s', %d)", $string, $integer);
phiz118’s picture

phiz118 commented

Try converting the characters to their Hex equivalents.

Byapti’s picture

Byapti commented

Hi,

I think the “Testing For SQL Injections” article on http://www.stickyminds.com/sitewide.asp?Function=edetail&ObjectType=ART&...
may be helpful in this discussion.

This popular white paper is written by a software engineer from our organization Mindfire Solutions (http://www.mindfiresolutions.com).

I hope you find it useful!

Cheers,
Byapti

jporter892’s picture

jporter892 commented

There are some good ideas on preventing sql injection here - a nice explanation of all the things involved with sql injection prevention in case you aren't familiar with the issue itself:

http://www.programmerinterview.com/index.php/database-sql/sql-injection-...

Don't personally know of any modules.