SQL Injection And Its Types
- July 10, 2023
- Posted by: Netrika
- Category: Information security
Introduction
An injection is a technique of entering malicious data into the input parameters, which concatenates with the actual code and, when executed, performs unintended actions. This is a major problem for developers to avoid injection attacks. Injection attacks arise due to the lack of separation between actual code instructions (i.e. code) and the user-provided (external) input, which allows malicious actors to inject malicious content into the parameters and execute malicious code crafted by the attacker.
Injection attacks are some of the most successful and widespread types of attacks. Some injection attacks are SQL Injection, XML Injection, XSS etc.
For example: A login page consists of parameters Username and Password. The Username values only consist of alphanumeric values to be entered by the user. If anyhow user is able to inject malicious values other than alphanumeric values, which become part of the query is called Injection, where the application does not sanitise the user input and takes any values and parses to perform unintended actions.
In this section, we will discuss
- SQL Injection
- Types of SQL Injection
- How does an Attacker find and Exploit SQL Injection?
- Automated Tools used for Exploitation of SQL Injection
- Mitigation of SQL injection
- Difference between Stored Procedure and Prepared statements/Parametrised query
- How prepared statements work?
- Major Pros and Cons of stored procedures and prepared statements
- Mitigation of SQL injection
What is SQL Injection?
Before we start, let’s understand what SQL is. SQL is a structured query language which is used to query data from the database. These SQL queries are used to retrieve data from the database and can be used to add, delete, or modify data within the database.
When an attacker interferes with these SQL queries and injects the maliciously crafted code within the SQL statement to perform the unintended action, this is known as SQL injection. In this scenario, the malicious attacker code becomes part of the actual SQL query and parses by the SQL server. When this malicious code is parsed and executed results in unintended actions, which allows the attacker to retrieve the data that is not normally able to retrieve. This vulnerability, in many cases, allows attackers to view the data of other users. With the help of automated tools like SQLmap, the whole data from the database can be dumped.
When an application obtains user input from an HTTP request and, while processing that request, unsafely merges the input into a SQL query, this is known as first-order SQL injection occurs.
Second-Order SQL Injection:
When user-provided data is saved by the programme and then unsafely incorporated into SQL queries, second-order SQL injection occurs. It is typically necessary to provide appropriate data in one spot and then use another application function that analyses the data in an unsafe manner to detect the vulnerability.
Second-order SQL injection, also referred to as stored SQL injection, occurs when an application stores user data from an HTTP request. Normally, this would be accomplished by inserting the input into a database, but no vulnerability is created at this time. The programme then executes a different HTTP request, retrieving the previously saved data and maybe combining it into a SQL query.
A Successful SQL Injection attack results:
- Unauthorised Access: SQLi allows the attacker to bypass the application authentication mechanism and have illegitimate access to the data.
- Data Loss: SQLi allows the attacker to delete the data records from the database.
- Data Integrity Compromised: SQLi allows the attacker to modify or add data stored in a database. This leads to a compromise on the integrity of data.
- Information Disclosure: SQLi allows complete data leakage.
Various High profile data breaches in recent years have resulted from SQL exploitation leading to reputational damage and regulatory fines. In some circumstances, the attacker can gain access to a persistent backdoor, which can result in a long-term breach that can go undetected for a long period.
Types of SQL Injection:
Basically, SQLi is classified into three major categories.
- In-band SQLi (Error-based SQLi)
- Interferential SQLi (Blind SQLi)
- Out-of-band SQLi
In-band SQLi (Classic SQLi):
The most common and easy-to-exploit SQL Injection is In-band SQL Injection, which allows an attacker to directly launch an attack and retrieve results. Error-based SQLi and Union-based SQLi are the two basic types of In-band SQLi attacks.
Error-based SQLi: Error-based SQLi forces the server to go through SQL syntax error messages to gather details about the database structure. In this case, the server throws a direct SQL error message where the SQL query is broken—different SQL databases through different SQL syntax error messages. In rare circumstances, an attacker can enumerate an entire database using only error-based SQL injection.
Union-based SQLi: Union-based SQLi uses the union operator to combine two or more SELECT statements into a single result to retrieve the results. Two conditions should be satisfied for the Union query:
- The malicious query returns the same number of columns as the original query.
- The data types in all columns should be compatible with each SQL query.
Inferential SQLi (Blind SQLi):
In Inferential SQLi attacks, the web application does not transfer data, and the attacker cannot see the results of the attack to enumerate the database. This is referred to as blind SQL Injection attacks, where the attacker uses payloads and observes the behaviour of the application instead of getting results on the webpage while tampering with parameters within the application.
There are two types of Inferential SQL Injection:
- Boolean-based SQLi
- Time-based SQLi
Boolean-based SQLi: Boolean-based SQLi is a technique in which a TRUE or FALSE SQL query is sent to the database server. The query is parsed by the SQL server and forces the application to return different results based on the TRUE or False query. This behaviour is observed if the payload used returns true or false, even though no data from the database is returned, which confirms the SQL injection is within the application.
Time-based SQLi: Time-based SQL Injection is an inferential SQL Injection method that depends on submitting a SQL query to the database that makes it wait for a predetermined period of time (measured in seconds) before responding. If the query returns TRUE or FALSE, the attacker will be informed of the response time.
Out-of-band SQLi: Out-of-band SQLi is not very common because it depends on features to be enabled on the database server used by the web application. In order to convey data to an attacker, Out-of-band SQLi attacks would depend on the database server’s capacity to send DNS or HTTP requests. The xp_dirtree command in Microsoft SQL Server can be used to send DNS requests to a server that the attacker controls, and the UTL_HTTP package in Oracle Database can send HTTP requests from SQL and PL/SQL to a server that the attacker controls.
How do Attackers find and exploit SQLi?
Whenever the SQL query is written, various SQL-reserved keywords are used to balance the query. Such as single quote (‘), double quote (“), equal (=), comment (- -), etc. These SQL characters help to retrieve the data from the database.
The below code shows how the SQL query is generated while authenticating the users with valid credentials.
SQL code Vulnerable to SQL Injection
Public Boolean authenticate (String name, String pass)
Statement stmt = this.conn.createStatement
{
String sql = “SELECT name FROM user WHERE name=’ ” + name + ” ‘ AND passwd = ‘ ” pass + ” ‘ “ ;
ResultSet results =stmt.executeQuery(sql);
return results.first ();
}
This query executes against the database and authenticates the user due to valid credentials.
The attacker tries to break this query by using the SQL-reserved characters to interfere with the dynamically generated query. Putting an extra single quote within the query disbalances the SQL query structure, and the SQL server will throw an SQL syntax error.
“SELECT name FROM user WHERE name= ’ ‘ ” + helloworld + ” ‘ AND passwd = ‘ ” 12345 + ” ‘ “ ;
This extra quote will disbalance the SQL query and throw the SQL syntax error:
If you have an error in your SQL syntax, check the manual corresponding to your MySQL server version for the right syntax to use near “VALUE.”
This message confirms and gives information that the database used is MySQL within the application.
The above Error message confirms that the application is vulnerable to SQL Injections.
Manual Exploitation of SQL Injection:
Consider that the user is authenticating to an application with username=helloworld and password=12345. These are the valid credentials.
When a user logs in to the application, the below SQL query is generated and executed against the database server, which authenticates the user due to valid credentials.
“SELECT name FROM user WHERE name = ‘ ” + helloworld + ” ‘ AND passwd = ‘ ” 12345 + ” ‘ “ ;
As the attacker does not know the valid credentials, so he uses various authentication bypass SQL payloads in the vulnerable input parameter.
Now attacker attempts to inject the SQL payload ‘ OR ‘1’=’1 — into the vulnerable input field used here is the name parameter which is vulnerable to SQLi
This payload always returns a true value as 1 always equals 1 and shows the results.
SELECT name FROM user WHERE name= ‘helloworld ’ OR ‘1’=’1– AND passwd=‘12345’
The above query is parsed by the SQL server, and the payloads ‘ OR ‘1’=’1– returns a true value resulting in authentication bypass, and the attacker successfully authenticates to an application.
SQL Injection can be exploited by using the automated tools listed below:
SQL Injection Payload List: https://github.com/payloadbox/sql-injection-payload-list
Automated Tools Used for Exploitation of SQL Injection
- SQLmap
- BSQLhacker
- SQLninja
- SQLSus
- Mole
Mitigation of SQL injection:
Some methods to mitigate the SQL injection vulnerability are discussed below.
Prepared statements with parameterised queries: A powerful technique to reduce attacks is using prepared statements with parameterised queries. Prepared statements require developers to utilise static SQL queries and feed in the external input as a query parameter rather than writing dynamic queries, which fail to distinguish between application code and data. With this strategy, the SQL interpreter is guaranteed to always distinguish between code and data.
Public Boolean authenticate (string name, String pass)
{
PreparedStatement pstmt;
String sql = “SELECT name FROM user WHERE name = ? passws =? “;
pstmt = this.conn.preparedStatement(sql);
pstmt.setString (0, name)
pst.setString(1, pass);
ResultSet results = pstmt.execteQuery ();
return results.first();
}
Let’s examine the same authenticate () method that was previously addressed in its re-implementation. However, in this instance, utilising the PreparedStatement object’s parameterisation feature: Runtime variables name and pass cannot impact how the query behaves regardless of user input. It should be noted that relying solely on the PreparedStatement object is a weak defence. For all runtime components, it must be used in conjunction with the parameterisation feature (“?”). Even when a PreparedStatement object is present, string concatenation without the parameterisation feature can result in SQL injection.
Stored Procedures: The SQL statements that are created, saved, and then invoked from the application are known as stored procedures. Developers are typically just required to create SQL queries with automatically parameterised parameters. However, inside stored procedures, a developer is able to create dynamic SQL queries and implement stored procedures securely by staying away from internal dynamic SQL creation.
Principle of least privileges: This common security measure aids in reducing the possible harm caused by a successful attack. DBA or admin access should not be granted to application accounts on the database server. They should also be limited to the least privileged access based on the access requirements. For instance, read-only accounts are only permitted access to the table they need to access. This ensures that even if an application is attacked, the compromised application won’t provide the attacker access to the database.
Input Validations: Malicious external input is a frequent source of SQL injection. Therefore, it’s always a good idea to accept only authorised input—a strategy called as input validation. There are two types of input validation to guard against it: avoid list validation and preferlist validation.
Avoid list validation compares the external input to a list of inputs known to be dangerous. The external input is checked against a list of all harmful inputs a programme has built before it is verified. As a result, it is simple for an attacker to circumvent list validation because they can create numerous variations of malicious input that might not be on the assembled avoid list.
Preferlisting is a far superior strategy for risk reduction. Preferlist validation compares an outside input to a list of previously known, authorised input. The application rejects other input values that fail a test against the known, allowed input when preferlist input validation is used since it knows exactly what is desired.
SQL Prevention Sheet:
Difference between Stored Procedure and Prepared statements/Parametrised query.
Stored Procedures:
Stored procedures are the SQL code statements that are prepared by the programmer, which are then stored in the database and can be reused over and again. The stored procedure removes the headache of the programmer to write the SQL query repeatedly. They just prepare the SQL code statement and save it in the database, and when required, just execute the stored prepared statements.
Below SQL Code creates a stored procedure named “SelectAllStudents” that selects all records from the “Customers” table:
CREATE PROCEDURE SelectAllStudents
AS SELECT * FROM Customers
GO;
Execute the stored procedure above as follows:
EXEC SelectAllStudents;
Prepared Statements:
When the SQL code statements use place holders instead of actual values. Programmer writes the set of queries with placeholders for the actual value. This query is compiled by the database only once there and after the actual value is passed at the time of execution.
Prepared statements can be stored in the database as in the case of stored procedures.
Example: “INSERT INTO MyStudents (firstname, lastname, email) VALUES (?, ?, ?)”
MySQL Server:
<?php
$servername = “localhost”;
$username = “username”;
$password = “password”;
$dbname = “myDB”;
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die(“Connection failed: ” . $conn->connect_error);
}
// prepare and bind
$stmt = $conn->prepare(“INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)”);
$stmt->bind_param(“sss”, $firstname, $lastname, $email);
// set parameters and execute
$firstname = “Hello”;
$lastname = “world”;
$email = “security@testing.com”;
$stmt->execute();
How prepared statements work?
The SQL statements are prepared: When the SQL statements are created and sent to the database, at that time, some values are kept unspecified called parameters which are labelled with ” ? “, which is defined at a latter stage.
The SQL statement is parsed, compiled by the database to perform query optimisation and store the results. When the submit action is performed from the application side, the SQL query is executed, actual values are bound to the parameters, and the database executes the SQL statements.
Major Pros and Cons of stored procedures and prepared statements:
Stored Procedures:
| S.No | Pros | Cons |
| 1 | Only once the query is written and executed a number of times as required with different session. | Programming knowledge is required respective to the database used. |
| 2 | Allows queries to include programming constructs (such as loops, conditional statements, and error handling) that are either impossible or difficult to write in SQL alone. | In some cases, require special permissions to write them or call them. |
| 3 | As the server precompiles the query, the query is processed more quickly than the direct query. |
Prepared statements:
| S. no | Pros | Cons |
| 1 | Reduces the parsing time as the preparation of query is done once. | Are not stored in databases as in the case of stored procedures. |
| 2 | A query can be used multiple times. | To be recompiled with each connection or session. |
| 3 | Useful against SQL Injections |
Netrika is a renowned organisation that specialises in cybersecurity services with a proficient team of testers that are highly trained and well-equipped with professional expertise in web application security, web application testing, mobile application testing, mobile application security, web application penetration and network VAPT.
– Abhishek Kataria
(Penetration tester and researcher)
Reference Links :