SQL injection (or SQLi) is often referenced as the most common type of attack on websites. It is being used extensively by hackers and pen-testers on web applications. The OWASP Top Ten lists SQL Injection, along with other types of injections, as the first security risk facing web applications.
Despite being around for more than 20 years, SQL injection is still effective on many websites and web applications that do not implement secure coding practices.
This article is aimed at complete beginners who do not have any knowledge about SQL injection. We will first walk through the basics that we need to know before jumping into SQLi.
We will start by introducing relational databases. Then, we will learn what SQL is and how a SQL statement is formed. And finally, we will explore how SQL injections work and what makes them so dangerous on web applications.
Relational Databases
Before we start talking about SQL (and SQL injection), we first need to learn about relational databases.
A relational database is a type of database that stores pieces of data that can be identified in relation to each other. Based on this model, data in a relational database is often stored in tables. Each table contains a list of columns and a list of rows.
As an example, let’s consider the following table, named “users”:
The “users” table contains three columns (also called attributes): Id, User, and Password. Each row in the table is called a record. In our users’ table, we have two records.
A quick look at this table reveals that it most likely holds the credentials for all the users. If a user provides their credential on an authentication form, then the web application would check if they are present in the table above. This is how the application knows if the user should then be granted or refused access to a restricted member-only web page.
This is only a simple example, relational databases are normally more complicated than this, they have a lot more tables, each with many attributes and records (sometimes in the millions). Tables can also have relations with other tables.
For our purposes here, this is all you need to know about relational databases to understand the rest of this post.
SQL
Structured Query Language (or SQL) is a language that allows the management of relational databases. It is used by web applications to view, add, update or remove data from a database.
A Basic SQL Statement
Web applications interact with databases using SQL statements. Each SQL statement starts with a command.
Here is an example that will help us better understand the syntax of these statements:
SELECT * FROM users;In this example, the SQL statement starts with the command ‘SELECT’, which retrieves data from a table.
Following the ‘SELECT’ command, we have an asterisk(*) sign. In this position, this sign implies all columns. This means that we are going to extract data from all columns.
If you can understand basic English, then the ‘FROM’ keyword is self-explanatory.
And finally, ‘users’ is the name of the table from which we are extracting data.
In short, the above SQL statement can be translated into the following:
“Retrieve data of all columns from the ‘users’ table.”So now, let’s make the above SQL statement more interesting:
SELECT * FROM users WHERE user=’Fyodor’;In this case, we have specified that we’ll only retrieve the records that have the name ‘Fyodor’ at their user field instead of extracting all records. This will become more interesting when we cover SQL injection in the next section.
SQL Commands and Keywords
Now, to conclude this section, I’m going to leave you with a list of some of the most commonly used SQL commands and keywords. You won’t need to know all of them to understand the rest of this post, but I highly recommend that you look them up once you finish reading this article :
- SELECT: It retrieves data from a table.
- INSERT INTO: It adds a record to the table.
- UPDATE: It updates a record from the table
- DELETE: It deletes a record from the table.
- CREATE TABLE: It creates a new table.
- WHERE: It filters the record depending on whether they respect a condition or not.
- ORDER BY: It sorts the data retrieved by SELECT.
Now that we have covered the basics, let’s go ahead and explore our main topic.
SQL Injection
A SQL Injection (or SQLi) is a type of attack against a web application. It attempts to change the SQL statement that the application sends to the database by providing maliciously crafted data on a user input field.
In order to make this definition more clear, we’ll consider the following case.
A Basic SQL Injection Case
Let’s say we have an authentication form that asks the user to type in their username and password. The application grants access to the user depending on whether the provided inputs are correct. To be more specific, the application will send a SQL statement to the database to check if those credentials are present there. This statement will look something similar to this:
SELECT * FROM users WHERE username=’ ProvidedUsername’ and password=’ProvidedPassword’;If you’ve paid attention to the previous section, then this SQL statement should be clear. It extracts all records from the “users” database where the username field is equal to ‘ProvidedUsername’ and the password field is equal to ‘ProvidedPassword’. If there is a record that satisfies these 2 conditions, then the username and password are correct and the application grants access to the user.
Now, imagine if instead of typing a username, the user inputs the following :
a’ OR 1=1;-And then, for the password, the user types in a random value (This password value won’t matter here, you’ll see why in a moment).
The web application will then form the following SQL statement :
SELECT * FROM users WHERE username=’a’ OR 1=1;--’ and password=’RandomPass’;This query will retrieve any record from the users’ table that either has a username of ‘a’ or 1=1 is true.
Since 1=1 is always true, then this statement will also always be true, and therefore, this query will retrieve all records from the table.
In addition, since the double dash ‘--’ is used for comments in SQL, the rest of the line is commented out and will not be processed by the database.
Here is how this query looks like now:
SELECT * FROM users WHERE username=’a’ OR 1=1;--’ and password=’RandomPass’;Following this statement, the database will return a non-NULL list of records, and therefore, the web application will grant access to the user.
To Conclude
We have seen here only one example of a SQL injection. There are many other ways you can inject SQL code into a web application. If you want a more detailed list, I recommend that you check the Netsparker or Portiswigger SQL Injection Cheat Sheets.
Also, if you want real hands-on practice on SQLi, I invite you to try your luck on the Natas wargame in Overthewire. If you don’t know what this website is, you can read this other article that I wrote about it.
Originally published at https://patchthenet.com on April 9, 2021.
