Prevent SQL Injection Attacks with PHP

Jun

10th

Prevent SQL Injection Attacks with PHP

Author: admin | Files under php, tutorials

What is sql injection?

Recently my web sites got hacked and believe me, every database file and others on my server are deleted by the hacker. After serious search of reason I identified the threat as, sql injection.

One good think I done is, logging the sql queries into a file for auditing and debugging. After Walking thru the files I came across a line like this:
Recently my web sites got hacked and believe me, every database file and others on my server are deleted by the hacker. After serious search of reason I identified the threat as, sql injection.

One good think I done is, logging the sql queries into a file for auditing and debugging. After Walking thru the files I came across a line like this:

……. ‘exec master..xp_cmdshell ‘del *.* /Q’ – ………….

See the line it executes shell command to delete all the files.

This is called sql injection. After a detailed study of sql injection methods and its prevention techniques, I practiced them and now, my web site is running good and secure.

I taken time to write these tutorials to help the needed one and prevent sql injection type of hacking, any more.

SQL injection can be defined in a simple way as follows:

“It is trick of adding the malicious code to the sql query, so that, hacker can retrieve your data, insert, delete and can do anything with your database.”

For example:

<?php
$query = "SELECT * FROM users_table WHERE username=’{$_GET['username']}’ AND password=’{$_GET['password']}’";
mysql_query($query, $link);
if (mysql_affected_rows($link) > 0) {
echo "you are logged in successfully\n";
}
?>

URL:

../Login.php?username=admin&password=xyz

The intention of the above code snippet is to check whether the username (admin) and password (xyz) is matching, if so, the user is logged in to the web site.

But, consider the following URL which affected with sql injection:

../Login.php?username=admin&password=z%20or%201=1—-

Look at the URL carefully, we got like this:

SELECT * FROM users_table WHERE username=admin AND password = ‘z’ OR 1=1 –

So, the hacker can login now as user admin, whether the password is correct or not. Because, the OR statements always return true.

It is simple right! But, giving these kind chances to hackers will bring disaster to the web site.

Some examples of sql injection

SQL injection using select statement

<?PHP
$query = "SELECT id, name FROM users_table
WHERE id = $id;
mysql_query($query, $link);
?>

The $id is combined with another SELECT statement which reveals all passwords as follows:

O union select ‘1′, concat(uname||‘-’||passwd) as name, ‘1971-01-01′, ‘0′ from users_table;

So, now the query is,

SELECT id, name FROM users_table
WHERE id = 0 union select ‘1′, concat(uname||‘-’||passwd) as name, ‘1971-01-01′, ‘0′ from users_table;

SQL injection using the update statement

<?php
$query = "UPDATE users_table SET pwd=’$pwd’ WHERE uid=’$uid’;";
?>

The $uid is combined with malicious sql code like this:

” or uid like ‘%admin%’; –

So, now the query will be as follows:

UPDATE usertable SET pwd=‘…’ WHERE uid=” or uid like ‘%admin%’; –

Sql Injection using the DROP Statement

<?PHP
$query = "SELECT id, name FROM users_table
WHERE id = $id;
mysql_query($query, $link);
?>

If the $id is appended with ‘x’; DROP TABLE users_table; –’

Now the query will be as follows:

SELECT id, name FROM users_table
WHERE id = ‘x’; DROP TABLE users_table; –‘

Sql Injection using INSERT statement

<?PHP
$query = "SELECT id, name FROM users_table
WHERE id = $id;
mysql_query($query, $link);
?>

If the $id is appended with ‘x’;

INSERT INTO members (‘email’,‘passwd’,‘login_id’,‘full_name’)
VALUES (’steve@unixwiz.net’,‘hello’,’steve’,‘Steve Friedl’);–‘

Now the sql query will be as follows:

SELECT id, name FROM users_table
WHERE id = ‘x’;
INSERT INTO members (‘email’,‘passwd’,‘login_id’,‘full_name’)
VALUES (’steve@unixwiz.net’,‘hello’,’steve’,‘Steve Friedl’);–‘

PHP Magic Quotes

The better solution to the sql injection problem is using the input escape mechanisms. PHP supports these kinds of input escape mechanisms like magic_quotes_gpc. If enabled, magic_quotes_gpc, adds backslashes in front of single-quotes, double-quotes, and other characters that could be used to break out of a value identifier.

But, magic quotes is a generic solution that doesn’t include all of the characters that require escaping, and the feature isn’t always enabled (it is deprecated in current PHP versions ). Ultimately, it’s up to you to implement safeguards to protect against SQL injection.

mysql_real_escape_string

mysql_real_escape_string function is a mysql extension for the PHP, to escape input characters that are special to mysql. Internally, mysql_real_escape_string function calls the Mysql’s library function mysql_real_escape_string, which prepends backslashes to the following characters: \x00, \n, \r, \, ‘, ” and \x1a.

Below is the simple example which uses this fuction:

<?php
$link = mysql_connect(‘mysql_host’, ‘mysql_user’, ‘mysql_password’)
OR die(mysql_error());
$query = sprintf("SELECT * FROM users_table WHERE username=’%s’ ",
mysql_real_escape_string($username,$link));
mysql_query($query);
?>

NOTE: A MySQL connection is required before using mysql_real_escape_string() otherwise the function returns FALSE.

In addition to securing input, a database-specific escape function prevents data corruption.For example, the escape function provided in the MySQL extension is aware of connection Characters and encodes those (and others) to ensure that data isn’t corrupted by the MySQL storage mechanism and vice versa. As this function takes into account the current character set of the connection, it is safe to place it in a mysql_query().

Here one more important thing is, if the magic quotes is enabled, it automatically adds the backslashes to the strings and when we use mysql_real_escape_string() function it adds another backslash which leads to data corruption. So, when ever you are using the mysql_real_escape_string() function it is a good practice to check for the magic quotes is enabled and stripes the slashes as follows:

<?php
$link = mysql_connect(‘mysql_host’, ‘mysql_user’, ‘mysql_password’)
OR die(mysql_error());
if(get_magic_quotes_gpc())
{
$username = stripslashes($username);
}
$query = sprintf("SELECT * FROM users_table WHERE username=’%s’ ",
mysql_real_escape_string($username,$link));
mysql_query($query);
?>

There are many things we have to consider while using the mysql_real_escape_string function, like; we should check whether the variable is not numeric because there is no need to add quotes to the numeric variable. Right!.

And another thing is we should check whether it has only spaces etc. Keeping all these things into consideration, I came across a good function in the php.net that is as follows:

<?php
function quote_smart($value)
{
if (get_magic_quotes_gpc())
{
$value = stripslashes($value);
}
if($value == ”)
{
$value = ‘NULL’;
}
else if (!is_numeric($value) || $value[0] == ‘0′)
{
$value = "’" . mysql_real_escape_string($value) . "’"; //Quote if not integer
}
return $value;
}
$link = mysql_connect(‘mysql_host’, ‘mysql_user’, ‘mysql_password’)
OR die(mysql_error());
$query = sprintf("SELECT * FROM users_table WHERE username=’%s’ ",
quote_smart ($username));
mysql_query($query);
?>

Now, we will go thru another scenario like, how to use the mysql_real_escape_string function with the array’s. There is function called array_map which iterates thru the array values and calls the callback function which is provided as an argument to this function like as follows:

$_POST = array_map(‘mysql_real_escape_string’,$_POST);

Now we put this in our example,

<?php
function quote_smart($value)
{
if (get_magic_quotes_gpc())
{
$value = stripslashes($value);
}
if($value == ”)
{
$value = ‘NULL’;
}
else if (!is_numeric($value) || $value[0] == ‘0′)
{
$value = "’" . mysql_real_escape_string($value) . "’"; //Quote if not integer
}
return $value;
}
$link = mysql_connect(‘mysql_host’, ‘mysql_user’, ‘mysql_password’)
OR die(mysql_error());
$_POST = array_map(‘quote_smart’,$_POST);
$query = sprintf("SELECT * FROM users_table WHERE username=’%s’ ",
$_POST[$username]);
mysql_query($query);
?>

Encoding Binary Data

Escape function as explained above are suitable for most of the cases. But consider the binary data, even you escape the binary data some times it becomes a miscellious code such way that it may conflict with the database’s own storage format, leading to the corruption or loss of a table or the entire database.

Taking into consideration of these kinds of problems, some database systems, such as PostgreSQL, offer a dedicated function to encode binary data. Rather than escape problematic characters, the function applies an internal encoding. For instance, PostgreSQL’s pg_escape_bytea() function applies a Base64-like encoding to binary data:

<?PHP
// for binary data use:
pg_escape_bytea($binary_data);
?>

A binary data escaping mechanism should also be used to process multi-byte languages that aren’t supported natively by the database system. (Multi-byte languages such as Japanese use multiple bytes to represent a single character; some of those bytes overlap with the ASCII range normally only used by binary data.)

There’s a disadvantage to encoding binary data: it prevents persisted data from being searched other than by a direct match. This means that a partial match query such as LIKE ‘foo%’ won’t work, since the encoded value stored in the database won’t necessarily match the initial encoded portion looked for by the query.

For most applications, though, this limitation isn’t a major problem, as partial searches are generally reserved for human readable data and not binary data, such as images and compressed files.

Use PHP cast / settype / sprintf functions

Even thou we use the escape functions they always doesn’t guarantee the data safety, see for example the following scenario:

$id = “0; DELETE FROM users_table”;
$id = mysql_real_escape_string($id); // 0; DELETE FROM users
mysql_query(“SELECT * FROM users_table WHERE id={$id}”);

mysql_real_escape_tring function sees the semicolon in the above $id, but, Since the semicolon doesn’t have any “special” meaning, it’s left as-is by both the database escape function and addslashes() and more over the query is expecting some interger value for the $id.

To avoid these kind of problems it is always better to check or to force, type checking as follows:

$id = “123; DELETE FROM users”;
$id = (int) $id; // 123
pg_query($conn, “SELECT * FROM users WHERE id={$id}”); // safe

A cast forces PHP to perform a type conversion. If the input is not entirely numeric, only the leading numeric portion is used. If the input doesn’t start with a numeric value or if the input is only alphabetic and punctuation characters, the result of the cast is 0. On the other hand, if the cast is successful, the input is a valid numeric value and no further escaping is needed.Numeric casting is not only very effective, it’s also efficient, since a cast is a very fast, function free operation that also obviates the need to call an escape routine.

We can also use another type checking function from PHP as follows:

$id = “123; DELETE FROM users”;
$id = settype($id, ‘integer’);
pg_query($conn, “SELECT * FROM users WHERE id={$id}”); // safe

The best practice is to use the sprintf, I always prefer to use that, (in the above examples I used it). Sprintf function forces to specify the type and creates a prefect query as follows:

<?php
$link = mysql_connect(‘mysql_host’, ‘mysql_user’, ‘mysql_password’)
OR die(mysql_error());
if(get_magic_quotes_gpc())
{
$username = stripslashes($username);
}
$query = sprintf("SELECT * FROM users_table WHERE userid=%d and username=’%s’ ", $userid, mysql_real_escape_string($username,$link));
mysql_query($query);
?>

Be carefull with LIKE opeator

The SQL LIKE operator is extremely valuable: it uses % and _ (underscore) qualifiers:

% - match 0 or more characters
_ - any single character

For a scenario, if there are thousands of records in table and we search for sub string with these qualifiers how much costly is that query right! So be careful. With a few iterations, a compromised LIKE query could launch a Denial of Service attack by overloading the database.

Now, as we learned about the database escape function and php escape functions, we will check whether they are usefull with the LIKE:

$search_string = mysql_real_escape_string(“%james”);
mysql_query(“SELECT * FROM users messages_table WHERE subject LIKE ‘{$search_string}%’ ”);

The intent of the SELECT above is to find those messages that begin with the user-specified string, $search_string. Uncompromised, that SELECT query would be quite fast, because the index for subject facilitates the search. But if $search_string is altered to include a leading % qualifier (as shown in the above example), the query can’t use the index and the query takes far longer to execute—indeed, the query progressively slower as the amount of data in the table grows.

The underscore qualifier presents both a similar and a different problem. A leading underscore
in a search pattern, as in _ish, cannot be accelerated by the index, slowing the query. And
a trailing underscore may substantially alter the results of the query. To complicate matters further,underscore is a very common character and is frequently found in perfectly valid input.

To address the LIKE quandary, a custom escaping mechanism must convert user-supplied % and _ characters to literals. Use addcslashes(), a function that let’s you specify a character range to escape.

$sub = addcslashes(mysql_real_escape_string(“%something_”), “%_”);
// $sub == \%something\_
mysql_query(“SELECT * FROM messages WHERE subject LIKE ‘{$sub}%’”);

Here, the input is processed by the database’s prescribed escape function and is then filtered through addcslashes() to escape all occurrences of % and _. addcslashes() works like a custom addslashes(), is fairly efficient, and much faster alternative that str_replace() or the equivalent regular expression.

Remember to apply manual filters after the SQL filters to avoid escaping the backslashes; otherwise, the escapes are escaped, rendering the backslashes as literals and causing special characters to re-acquire special meanings.

High On MicroSoft