This post will explain SQL injection, the impact of successful SQL attacks, examples of SQL injection techniques, and how to prevent SQL injection.
There are several applications that you can use to learn SQL injection.
In this particular post, we will use the Damn Vulnerable Web Application (DVWA). It’s a web app developed in PHP and MySQL and intentionally made to be vulnerable.
If you don’t have DVWA installed yet, feel free to check out our post on How to set up DVWA on Kali Linux.
Table of Contents
- What is SQL Injection (SQLI)?
- The Impact of a Successful SQL Injection Attack
- How an SQL Injection Attack Works
- Setup DVWA for SQL Injection
- View the Vulnerable Code
- Basic Injection
- Always True Injection
- Display RDBMS and Version
- Display the hostname of our web app
- Display Database User
- Display the Database Name
- List all tables in the information schema.
- List all user tables in the information schema.
- List all Column fields in the information schema users table
- Display all the column contents in the information schema users table
- How To Prevent SQL Injection Attacks
- Summing Up
What is SQL Injection (SQLI)?
SQL injection, commonly referred to as SQLI, is an attack where an application allows unauthorized users to send SQL queries to the database and gain access to information they shouldn’t.
In most cases, hackers use SQL injection to retrieve user/company data, modify database contents or delete the entire database, thus bringing down the whole web system.
In fatal cases, attackers can escalate the SQL injection attack thus, gaining access to the entire underlying back-end infrastructure, server or even perform a Denial of Service attack (DoS).
As of 2021, OWASP Top 10 is a standard awareness framework for developers, and web application security listed Injection (SQL, NoSQL, OS, and LDAP) as the number one vulnerability.
The Impact of a Successful SQL Injection Attack
SQL injection is one of the popular attacks behind the data leaks that we see on the internet and the Dark Web. That includes information like user emails, usernames, passwords, and even credit card information. This attack leads to reputational damage and loss of revenue in regulatory fines. In other cases, attackers can escalate the SQL injection attack and create a persistent backdoor. That allows them to compromise the system for a long time and remain unnoticed.
How an SQL Injection Attack Works
Think of a website with a simple login form with two fields – a username, password, and a Login or Submit button. After you enter the required credentials, when you hit the Submit button, the query sent to the database has this syntax:
SELECT username, password FROM usersdb WHERE username=$user;
E.g., If your name is JohnDoe,
SELECT username, password FROM usersdb WHERE username='Johndoe';
Anyone with a hacker’s mindset can decide to manipulate the application by entering a value different from the username. This value will be an SQL query to reveal or modify the database’s contents. For example, if the attacker entered abc’ OR 1=1–‘ instead of the actual username, the resulting SQL query would look like this:
SELECT username, password FROM usersdb WHERE username='abc' OR 1=1--';
Let’s dissect this input
1=1--' and see how it manipulates the database.
abc': Here we just guessed any username but we added a single quote ‘ at the end. The single quote closes our username field, and the following part becomes an SQL query.
ORis a conjunction in SQL, and 1=1 will always be true. Therefore, no matter what you put in the username field, the query will always be True and return all the records of the userdb database.
--'(double dash) is a comment in SQL. It tells the SQL server not to execute any query past this point. In this particular example, we are using double dash to comment out errors that would arise because of the trailing single quote at the end. You can also use
I believe up to this point; you have a good understanding of what SQL injection is. Let’s dive in and exploit actual SQL injection queries on our DVWA.
Setup DVWA for SQL Injection
As stated above, if you haven’t configured DVWA on your system, please check out our post on How to set up DVWA on Kali Linux, which gives you a step-by-step procedure.
If you set up DVWA on your localhost, start Apache Web server and MySQl using the commands below:
sudo systemctl start apache2
systemctl start mysql
- Username: admin
- Password: password
After a successful login, you will see the DVWA main page. First, click on the DVWA Security on the bottom left, set security to Low, and click Submit.
On the left section of the page, you will see the various vulnerable pages to exploit. Click SQL Injection. You should see a page similar to this below.
View the Vulnerable Code
On the SQL injection page, click the View Source button at the bottom right. That will open a page with the SQL Injection source code written in PHP. When you go through the code, you will see a line like:
$query = "SELECT first_name, last_name FROM users WHERE user_id = '$id'";
That is the vulnerable line of code. At the end of the line, you can see the user input is concatenated to the SQL query without being validated. That allows us to pass arbitrary commands into the database. Let’s get started.
On the SQL Injection page, we have a USER ID field. When we enter number 1, the application returns the Firstname and Surname of the user with ID 1.
If we continue trying numbers like 2,3,4 and 5, we still get an output. However, any number from 6 doesn’t return anything. Therefore, our web app has only five users. Behind the scenes, the SQL query that will execute in the database is:
SELECT First_Name,Last_Name FROM users WHERE ID='1';
Other than using the USER ID field, we can also use the URL to pass our queries. When you first enter ID 1 and click submit, the URL will look like this:
The injectable part in this URL is the id field. Delete the number 1 and enter a different value like 2 or 3, as shown below. Hit Enter.
You will notice that this will also return the username and surname of the user with ID 2.
Always True Injection
We looked at this when talking about How an SQL Injection attack works. Enter an input like
test' OR 1=1# and hit Enter. That will return the username and surname of all users in the database.
This query will display all records that are True or False. The
test' parameter will probably not be equal to any user in the Database and will equal to False. The other part
1=1 will be True since 1 (one) is equal to 1 (one). The
# sign to comments out any SQL code or error. The query that executes in the database looks like this;
SELECT first_name, last_name FROM users WHERE user_id = 'test' or '1'='1';
Display RDBMS and Version
By knowing the RDMS (Relational Database Management System) running under the hood, we can successfully send malicious SQL queries. Most Web application technologies like Java, ASP.NET, PHP, etc., can give us a vivid idea of the database powering the web system. For example, PHP web apps will likely use MySQL, ASP.NET apps will most likely run on Microsoft SQL Server, while Java web systems will most likely run on Oracle or MySQL. Additionally, we can try using a combination of web technology and the Operating system to determine the database. For example, a web application running on Apache and PHP on a Linux host is probably using MySQL database.
However, we cannot entirely rely on this information. If the web app is vulnerable to SQL injection, then there are queries we can use to know the RDBMS and RDBMS-version running behind the scenes.
To know the RDBMS, we will enter anything that will make the database throw an error. In this case, we enter a single quote in the USER ID field. That will make the database read anything past the quote as a string instead of an SQL query.
That will throw an error, as shown below.
This error gave us the RDBMS name but not the version. In MySQL, we have two queries that you can use to return the database version –
Select version() and
Select @@version. We will use the SQL query below to get the database version.
test'union select null, version()#
We can also use:
test'union select null, @@version()#
Display the hostname of our web app
To get the hostname on MySQL, we use the
@@hostname query. Enter the input below in the USER ID field.
' union select null, @@hostname#
From the output above, we can see the hostname under the surname as metasploitable. Yours might be different from my mine.
Display Database User
To know the database user, we will enter the input below in the USER ID field. We will use the user() SQL function.
test' union select null, user() #
[analogy]Note: We are using Null to make the starting query valid.[/analogy]
From the output above, we can see the hostname under the surname as [email protected]
Display the Database Name
To get the database name, we will use the database() function in our SQL query. Please note; this is not the RDBMS but the database on which our web app is running. Enter the query below:
test' union select null, database() #
You can see the name of the database under the Surname – dvwa.
List all tables in the information schema.
The Information Schema is a record that holds information about all other databases maintained by MySQL RDBMS. Enter the query below in the USER ID field.
test' and 1=0 union select null, table_name from information_schema.tables #
The tables are listed under Surname.
List all user tables in the information schema.
To display all user tables, we will start in the informarion_schema database. Enter the query below in the USER ID field and click Submit.
test' and 1=0 union select null, table_name from information_schema.tables where table_name like 'user%'#
The user tables are listed under the Surname field.
List all Column fields in the information schema users table
Enter the query below in the USER ID field and click Submit.
test' and 1=0 union select null, concat(table_name,0x0a,column_name) from information_schema.columns where table_name = 'users' #
From the output above, you see we have the First_name, Surname, and user_id fields listed.
Display all the column contents in the information schema users table
This is much more interesting. We will display all the authentication information of all users in the database. That includes password hashes. Enter the query below.
test' and 1=0 union select null, concat(first_name,0x0a,last_name,0x0a,user,0x0a,password) from users #
From the output above, you can see the hashed password. We can go ahead and crack the hash to reveal the actual password. Some of the password cracking tools that come in handy include John the Ripper and Medusa. There are also websites where you can paste the password hash to reveal the actual password.
In this example, we will use crackstation.net to crack the password hash for the second user with the surname – Gordon.
How To Prevent SQL Injection Attacks
The main reason that makes web applications vulnerable to SQL injections dates back to the development (coding) stage. Here are some factors developers can consider to develop secure web systems.
- Validate user input
- Limit the use of special characters such as string concatenation
- Use stored procedures in the database
- Actively install security patches and updates
- Implement a Web Application Firewall
- Harden your Operating System and Applications
As of 2021, OWASP Top 10, a Security Framework, listed SQL injection as the number one attack mainly used by hackers and poses a significant impact on businesses and organizations. From the examples above, I believe you now understand how and why SQL injection attacks are the leading cause of massive data leaks.
The DVWA is a reliable platform where penetration testers can practice their skills and understand how various web vulnerabilities are exploited.