SQL injection

SQL injection

If you take user input from a Web page and insert it into a SQL database, you are probably already exposed to a security risk called SQL Injection.

This section will teach you how to prevent SQL injection and how to protect programs and SQL statements in server-side scripts like Perl.

Injection usually occurs when you get user input, such as when you expect to get the user's name, but you get a SQL statement that is likely to run without your knowledge.

Never trust user-provided data, which must be validated before it can be processed;

In the following example, name consists only of letters, numbers, and underscores, and is between 8 and 20 in length (you can modify these rules as needed).

if (preg_match("/^\w{8,20}$/", $_GET['username'], $matches))
{
   $result = mysql_query("SELECT * FROM CUSTOMERS 
                          WHERE name=$matches[0]");
}
else 
{
   echo "user name not accepted";
}

To show what the problem is, consider this code:

// supposed input
$name = "Qadir'; DELETE FROM CUSTOMERS;";
mysql_query("SELECT * FROM CUSTOMSRS WHERE name='{$name}'");

The following function call was intended to get a record from the CUSTOMERS table that matched the name field to the user's given input. T ypically, $name contains only letters and numbers, and perhaps spaces, such as string ilia. However, by attaching a completely new query statement to $name, the original function call becomes a database disaster: the injected DELETE statement deletes all records in the table.

Fortunately, if you're using MySQL, the mysql_query() function doesn't allow query stacking, or performs SQL queries multiple times in a function call. If you try to make a stacked query, the function call will fail.

However, other PHP database extensions, such as SQLite and PostgreSQL, happily accept stacked queries, execute all queries in the string, and thus create serious security issues.

Block SQL injection

You can subtly handle all escape characters in scripting languages such as Perl or PHP. PHP's MySQL extension provides a mysql_real_escape_string() function to escape characters that have a special meaning to MySQL.

if (get_magic_quotes_gpc()) 
{
  $name = stripslashes($name);
}
$name = mysql_real_escape_string($name);
mysql_query("SELECT * FROM CUSTOMERS WHERE name='{$name}'");

LIKE dilemma

To solve the LIKE dilemma, there must be a dedicated escape mechanism that converts user-provided '%' and ''' to literal values. To do this, you can use the addcslashes() function, which allows you to specify the range of characters to escape.

$sub = addcslashes(mysql_real_escape_string("%str"), "%_");
// $sub == \%str\_
mysql_query("SELECT * FROM messages 
             WHERE subject LIKE '{$sub}%'");