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:
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)– –
“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)– –
“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 🙂
you can find some more information here.
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 !!
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.
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?
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?