SQL injection or SQLi is a web application security weakness that allows attackers to control an application’s database by tempering with the database query. An SQL injection flaw simply allows an attacker to inject or tamper with certain parts of a database query in a web application to perform attacker-specified operations such as exfiltration of data, writing files to the database server, or even achieving server side code execution.
SQL injection usually occurs when a web application uses untrusted data, such as data entered into web form fields, as part of a database query. When an application fails to properly sanitize this untrusted data before adding it to a SQL query an attacker can include their own SQL commands which the database will execute.
SQL Injection Basics
In order to understand SQL injection you have some basic knowledge of Structured Query Language (SQL) and need to be familiar with SQL queries. SQL language used for storing, retrieving, modifying and removing data from a database. Now lets see an example of SQL injection vulnerability. As we know that the SELECT Statement used to retrieve data from the database :
SELECT column FROM table WHERE condition;
If the condition is true the the query will be successfully executed. Now lets see the below login system SQL query :
SELECT username, password FROM users WHERE username='USER_INPUT_1' AND password='USER_INPUT_2';
At above the USER_INPUT_1 and USER_INPUT_2 are user supplied data. So according to above query the database will only return the username and password when the username and password will matched for that row. In this scenario an attacker can put malicious input in the USER_INPUT_1 to bypass the login. For example the malicious code
aaa' or 1=1 --+ will bypass the login query.
SELECT username, password FROM users WHERE username='aaa' or 1=1 --+' AND password='USER_INPUT_2';
At the above query the
--+ (double dash with plus sign) acts like a comment sign and it comments out the rest of the query, and the username
= aaa is invalid but just after that, the attacker will used
or 1=1, where 1=1 is always true and as we know that in or operation, if one side is true then it always return true. So by this way the database will returns all the username passwords on the database.
Example of SQL Injection
Now lets see some exmple of SQL injection vulnerability in web application. For demonstration we are going to use DVWA Vulnerable Web App. To know more about DVWA or install it on your system pleas visit the article Setting up Web Pentesting Lab.
Now first change the security level of DVWA to low and then start to follow the below steps.
In DVWA at the SQL Injection tab, when we provide the user ID (1, 2, 3 ..) and press submit button, then the page will return the user’s First name and surname.
Now we need to put the invalid inputs, in order to break the query. For example like single column (‘). Now when we submit the invalid input, then the page will show the below error message :
The output is :
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 '''''
Now we know that the page is vulnerable to SQL injection attack. Then Lets try to balance the query with 1=1 and comment out rest of the query by using –+.
When we submit the above data, then it will again shows the error message, now lets try to comment rest of the query by # instead –+.
Now this time our query works and the page will show all users data.
When we check the source code of the page, then we can see that the sql query is :
SELECT first_name, last_name FROM users WHERE user_id = '$id'
Now when we submit our crafted input the the above SQL query will be look like this :
SELECT first_name, last_name FROM users WHERE user_id = '' or 1=1 # $id'
And as we can see the last part of the query $id’ will be commented, and when the query executes it will returns all users information on the page. The above vulnerability is Error-Based SQL injection.
Impact of SQL Injection Vulnerability
A successful SQL injection attack can result in unauthorized access to sensitive data, such as passwords, credit card details, or personal user information. Some example of exploitation of sql injection vulnerabilities are :
- An attacker can use SQL Injection to bypass authentication or even impersonate specific users.
- SQL Injection vulnerability could allow the complete disclosure of data residing on a database server.
Since web applications use SQL to alter data within a database, an attacker could use SQL Injection to alter data stored in a database. Altering data affects data integrity and could cause repudiation issues, for instance, issues such as voiding transactions, altering balances and other records.
- An attacker could use an SQL Injection vulnerability to delete data from a database. Even if an appropriate backup strategy is employed, deletion of data could affect an application’s availability until the database is restored. Some database servers are configured (intentional or otherwise) to allow arbitrary execution of operating system commands on the database server. Given the right conditions, an attacker could use SQL Injection as the initial vector in an attack of an internal network that sits behind a firewall.
Detecting SQL Injection
SQL injection can be detected manually by using a systematic set of tests against every entry point in the application. This typically involves:
- Submitting the single quote character ‘ and looking for errors or other anomalies.
- Submitting some SQL-specific syntax that evaluates to the base (original) value of the entry point, and to a different value, and looking for systematic differences in the resulting application responses.
- Submitting Boolean conditions such as OR 1=1 and OR 1=2, and looking for differences in the application’s responses.
- Submitting payloads designed to trigger time delays when executed within an SQL query, and looking for differences in the time taken to respond.
- Submitting OAST payloads designed to trigger an out-of-band network interaction when executed within an SQL query, and monitoring for any resulting interactions.
Types of SQL Injection
Error Based SQL Injection
Error based SQL injection is a technique of exploiting database servers, that relies on error messages thrown by the database server to obtain information about the structure of the database. Error based sql injection comes under In-band Injection. When an attacker is able to use the same communication channel to both launch the attack and gather results. The Error-based SQL injection is applied in those scenario when The web application is only response on an error otherwise not, means when the database query is ran successfully then it does not responded, but if any error occurred during query execution then it will through some error. Visit Error Based SQL Injection for more details.
Union Based SQL Injection
Union-based SQLi is an in-band SQL injection technique that leverages the UNION SQL operator to combine the results of two or more SELECT statements into a single result which is then returned as part of the HTTP response. Union based sql injection comes under in-band sql injection. Visit Union Based SQL Injection for more details.
Blind SQL Injection
Blind SQL (Structured Query Language) injection is a type of SQL Injection where the attacker asks the database true or false questions and determines the answer based on the applications response. This attack is often used when in the case or error, the web application is configured to show generic page specified by the developer instead of useful error messages.
Blind sql injection comes under inferential sql injection. In an inferential SQLi attack, no data is actually transferred via the web application and the attacker would not be able to see the result of an attack in-band, instead, an attacker is able to reconstruct the database structure by sending payloads, observing the web application’s response and the resulting behavior of the database server.
Blind SQL injection is nearly identical to normal SQL Injection, the only difference being the way the data is retrieved from the database. When the database does not output data to the web page, an attacker is forced to steal data by asking the database a series of true or false questions. This makes exploiting the SQL Injection vulnerability more difficult, but not impossible. Visit Blind SQL Injection for more details.
Boolean Based SQL Injection
Sometimes there is no visible error message on the page when an SQL query fails, making it difficult for an attacker to get information from the vulnerable application. However there is still a way to extract information. Boolean based sql injection comes under inferential sql injection.
In a Boolean-based SQL injection attack, we simply ask questions from the database in the form of “true or false” statements. A true statement returns a different result than a false statement, so based upon this, we are able to enumerate and extract information present in the database. A true statement means that the information that we are asking for is present inside the database; a false statement would mean it is not present. To generate a true or false statement, we can use the AND/OR statement and inspect the response that the website returns. Visit Boolean Based SQL Injection for more details.
Time Based SQL Injection
The time-based SQL injection 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 and the response time will indicate to the attacker whether the result of the query is TRUE or FALSE. If the page is not vulnerable, it will load quickly; if it is vulnerable it will take longer than usual to load. This enables the attacker to extract data, even though there are no visible changes on the page. Time based sql injection comes under inferential sql injection. Visit Time Based SQL Injection for more details.
Out-of-band SQL Injection
Out-of-band SQL Injection is not very common, mostly because it depends on features being enabled on the database server being used by the web application. Out-of-band SQL Injection occurs when an attacker is unable to use the same channel to launch the attack and gather results. Out-of-band techniques, offer an attacker an alternative to inferential time-based techniques, especially if the server responses are not very stable (making an inferential time-based attack unreliable). Visit Out of Bound SQL Injection for more details.
Remediation of SQL Injection
- Use parameterized queries (prepared statements) instead of string concatenation within sql query.
- Treat all user input as untrusted. Any user input that is used in an SQL query introduces a risk of an SQL Injection.
- Treat input from authenticated and/or internal users the same way that you treat public input.
- Don’t filter user input based on blacklists. A clever attacker will almost always find a way to circumvent your blacklist. If possible, verify and filter user input using strict whitelists only.