SQL Injections Demystified

Photo by diana_pole on unsplash

SQL which stands for Structured Query Language is a programming language that was specifically designed to communicate with relational databases. It is a “command” that is sent to the database in order to retrieve a set of records, insert/update records or perform a certain action.

• For example: SELECT name, cost FROM products. This returns the name and cost of all products.
• We can be more specific by adding a WHERE clause: SELECT name, cost FROM products WHERE id = 2. This will return the name and cost of the product with an id equal to 2.

What is an SQL Injection (SQLi) ?

A SQL injection is a technique used to gain unauthorized access to a database by manipulating SQL queries used in an application. Malicious users can use this type of attack to get access to protected resources such as user credentials, or execute malicious SQL statements in order to manipulate the application.

If SQL queries are used in your application in such a way that, an attacker can inject their own SQL into the query, it allows space for SQLi attacks on your application.

Most common way to perform an SQLi on a vulnerable SQL query is to use the UNION clause to combine the results of multiple SELECT statements.

For example assume that following query was used to retrieve the list products from the products table by using the id from a direct user input field of the application.

SELECT name, cost FROM products WHERE id = ? 

An attacker can use the UNION clause in the input field along with the id in order to obtain records from a restricted table in the database.

SELECT name, cost FROM products WHERE id = 2 UNION SELECT username, password FROM users

The above query will combine the two queries and return them in one result set.

+-------------------------+---------------------------+
| name                    | cost                      |
+-------------------------+---------------------------+
| L86 Fingerprint Scanner | 73.55                     |
| user1                   | Secret123                 |
| user2                   | myPaswd                   |
+-------------------------+---------------------------+

This way, an attacker will get the credentials of all the users in the database and he can use those to perform malicious actions on the application.

Testing an Application for SQLi Vulnerability

The best way to test for SQL injections is to follow a structured approach such as follows.

Step 1: Determine number of columns

SQL assumes the select statements on both sides of the UNION clause return the same number of columns. In order to determine how many columns the 1st query uses, we can start with querying for 1 column, then 2 columns etc in the user input field of the application’s page that is susceptible to SQLi. We use numbers so we do not have to guess table names or column names. You keep adding numbers until we see the results of the 1st query on the application page again (make sure no error messages, no empty pages are shown).

• 2 UNION SELECT 1
• 2 UNION SELECT 1,2
• …
• 2 UNION SELECT 1,2,3,4,5

Step 2: Determine location of columns in output

The application now receives the result of the first query and the numbers returned by the second query. Since we’ll never want to look at the results of the first query, we change that first number into a number that never exists: -1

-1 UNION SELECT 1,2,3,4,5

Step 3: Use INFORMATION_SCHEMA to retrieve the column and table names

Now we can simply ask the database to show us all table and column names by using the meta table INFORMATION_SCHEMA.columns!

-1 UNION SELECT 1,table_name,column_name,4,5 FROM INFORMATION_SCHEMA.columns

Note that we still need to match the number of columns with the 1st select statement.

Once we get the list of table names and the column names, we can access data in any of those tables in the database.

Assume the user credentials are available in a table called Users under column names username and password. Then we can list the data as shown below.

-1 UNION SELECT 1,username,password,4,5 FROM Users

How to Prevent SQLi in Your Application?

Following lists some of the techniques you can use to prevent SQLi vulnerabilities in your application. These can be used with any kind of programming language with any kind of database system.

• Using Prepared Statements (with Parameterized SQL Queries)
• Using Properly Constructed Stored Procedures
• Allow-list Input Validation
• Escaping All User Supplied Input