Explanation of particular sql injection

Question

Browsing through the more dubious parts of the web, I happened to come across this particular SQL injection:

http://server/path/page.php?id=1+union+select+0,1,concat_ws(user(),0x3a,database(),0x3a,version()),3,4,5,6--

My knowledge of SQL - which I thought was half decent - seems very limiting as I read this.

Since I develop extensively for the web, I was curious to see what this code actually does and more importantly how it works.

Answer 1

It seems to retrieve the user used to connect to the database, the database adress and port, the version of it. And it will be put by the error message.

Answer 2

It replaces an improperly written parametrized query like this:

$sql = '
SELECT  *
FROM    products
WHERE   id = ' . $_GET['id'];

with this query:

SELECT  *
FROM    products
WHERE   id = 1
UNION ALL
select 0,1,concat_ws(user(),0x3A,database(),0x3A,version()),3,4,5,6

, which gives you information about the database name, version and username connected.

Answer 3

this code adds an additional union query to the select statement that is being executed on page.php. The injector has determined that the original query has 6 fields, thus the selection of the numeric values (column counts must match with a union). the concat_ws just makes one field with the values for the database user , the database, and the version, separated by colons.

Answer 4

The injection result relies on some assumptions about the underlying query syntax.

What is being assumed here is that there is a query somewhere in the code which will take the "id" parameter and substitute it directly into the query, without bothering to sanitize it.

It's assuming a naive query syntax of something like:

select * from records where id = {id param}

What this does is result in a substituted query (in your above example) of:

select * from records where id = 1 union select 0, 1 , concat_ws(user(),0x3a,database(),0x3a,version()), 3, 4, 5, 6 --

Now, what this does that is useful is that it manages to grab not only the record that the program was interested in, but also it UNIONs it with a bogus dataset that tells the attacker (these values appear separated by colons in the third column):

• the username with which we are connected to the database
• the name of the database
• the version of the db software

You could get the same information by simply running:

select concat_ws(user(),0x3a,database(),0x3a,version())

Directly at a sql prompt, and you'll get something like:

joe:production_db:mysql v. whatever

Additionally, since UNION does an implicit sort, and the first column in the bogus data set starts with a 0, chances are pretty good that your bogus result will be at the top of the list. This is important because the program is probably only using the first result, or there is an additional little bit of SQL in the basic expression I gave you above that limits the result set to one record.

The reason that there is the above noise (e.g. the select 0,1,...etc) is that in order for this to work, the statement you are calling the UNION with must have the same number of columns as the first result set. As a consequence, the above injection attack only works if the corresponding record table has 7 columns. Otherwise you'll get a syntax error and this attack won't really give you what you want. The double dashes (--) are just to make sure anything that might happen afterwords in the substitution is ignored, and I get the results I want. The 0x3a garbage is just saying "separate my values by colons".

Now, what makes this query useful as an attack vector is that it is easily re-written by hand if the table has more or less than 7 columns.

For example if the above query didn't work, and the table in question has 5 columns, after some experimentation I would hit upon the following query url to use as an injection vector:

http://server/path/page.php?id=1+union+select+0,1,concat_ws(user(),0x3a,database(),0x3a,version()),3,4--

The number of columns the attacker is guessing is probably based on an educated look at the page. For example if you're looking at a page listing all the Doodads in a store, and it looks like:

Name        |        Type        |        Manufacturer
Doodad Foo   Shiny                Shiny Co.
Doodad Bar   Flat                 Simple Doodads, Inc.

It's a pretty good guess that the table you're looking at has 4 columns (remember there's most likely a primary key hiding somewhere if we're searching by an 'id' parameter).

Sorry for the wall of text, but hopefully that answers your question.