Everything You Need to Know About SQL Injection Attacks & Types, SQLi Code Example, Variations, Vulnerabilities & More

SQL Injection Attacks are one of the most popular attacks against web servers, websites and web applications. A fairly popular website can expect to receive anywhere between 80 and 250 SQL injection attacks on a daily basis and these figures can easily reach thousands when an SQL vulnerability is disclosed to the public.

This article aims to help network engineers, administrators, security experts and IT professionals understand what an SQL injection is by taking you step-by-step on how an HTTP SQL injection attack is executed using real code. 

Here is a list of topics we’ll cover:

• SQL Injection Attacks – Basics
• Example of an SQL Injection Vulnerability
• Exploiting SQL Injection Vulnerabilities
• Variations of SQL Injection Attacks
• In-Band SQL Injection
• Blind SQL Injection
• Out-of-Band SQL Injection
• Summary

Additional related articles:

• What is Cross-site Scripting (XSS)? How does XSS Work? Different Types of XSS Attacks

• Understanding, Avoiding & Protecting Against Cross Site Request Forgery Attacks

• Automate Web Application Security - Why, How & The Necessary Tools

• Creating a Cross-site Scripting (XSS) Attack. Understanding How XSS Attacks Work & Identifying Web Vulnerabilities

SQL Injection Attacks - Basics

SQL Injection, or SQLi for short, refers to an attack vector that exploits a web application by abusing the inherent trust between the web application and the database. An SQL injection attack would allow an attacker to perform malicious actions on the database through the use of specially crafted SQL commands. SQL is the most commonly used database query language, making it ideal for an attacker to target.

Since SQL Injection attacks can be performed against a wide array of applications, this attack is one of the most widely common and most critical of web vulnerabilities. So much so that injection attacks, such as SQL Injection, have placed first in OWASP’s Top 10 list,  several times in a row.

SQL Injection attacks can allow an attacker to extract, modify, add and delete data from a database, in turn affecting data confidentiality, integrity and availability (since an attacker could potentially delete data and disrupt operations). In addition, an SQL Injection attack can be used as a springboard to escalate the attack.

Example of an SQL Injection Vulnerability

A web application would typically communicate with a variety of back-end systems, including a database. Let’s take an HTML form, which inserts values into a database, as an example. 

Once the form is filled out and submitted, an HTTP request (usually a POST request) is sent to the web application, where the input values are directly included into the SQL statement that will insert these values into the database. 

The only way an SQL Injection vulnerability could occur is if the web application trusts the user’s input without parameterizing it and using prepared statements. This is done by instructing the database that a certain part of the query should be executed while the rest is to be treated as the user’s input. 

Prepared statements ensure that the database does not interpret certain characters in the user’s input as part of the SQL statement, therefore allowing the attacker to submit their own SQL statements.

SQL Injection example: The following pseudo code is a simple example showing how a user can be authenticated:

// Get username and password from POST request
username = request.post['username']
password = request.post['password']

// Statement vulnerable to SQL injection
sql = “SELECT id FROM users WHERE username=’” + username + “’ AND password=’” + password + “’”

// SQL statement executed by database
db.exec(sql)

If the user inputs foo as the username and bar as the password, the following SQL statement will be processed by the database server:

SELECT id FROM users WHERE username=’foo’ AND password=’bar’

When executed, as expected, this will return the value of the ID column that is associated with the database entry of the corresponding username and password.

Exploiting SQL Injection Vulnerabilities 

The example above is vulnerable to SQL Injection, since whatever the user inputs in the form will be interpreted by the database server as a command. For instance, an attacker could bypass this form by setting the password field to ’ OR 1=1

The following is what the SQL statement would look like.

SELECT id FROM users WHERE username=’foo’ AND password=’pass’ OR 1=1

From the above statement we can see that the user’s input has changed the statement’s functionality. Now, the value of the ID column is being returned if the submitted username is equal to foo, and password is equal to pass, or if 1is equal to 1 (which will always be the case).

With this statement only the username needs to match the value in the database because, for the password condition to be met, the submitted password can either match the value in the database or it can be validated if 1=1. With this trick, the attacker can bypass the website’s authentication mechanism for any user whose username is known. 

To further control the SQL statement, an attacker can even comment out the rest of the statement. For example, an attacker can use the double-dash (--) notation to comment out the rest of the statement:

SELECT id FROM users WHERE username=’username’ --’ AND password=’bar’

The highlighted part of the above statement, or anything after the double-dash, will be commented out and therefore not considered during execution. This will once again allow an attacker to bypass authentication. 

Variations of SQL Injection Attacks

It is important to note that there are three major classifications of SQL Injection attacks, each of which has its own particular use and can only be used under a specific circumstance. These categories are:

In-Band SQL Injection

The example that we saw earlier was an in-band attack since the same channel was used to launch the attack and obtain the result which, in this case, was being authenticated. In-band attacks are the most common and easiest to exploit in comparison to other SQL injection attacks. 

Data exfiltration using in-band attacks can either be done through error messages that are reported on the web application triggered by an SQL Injection attack or by using the UNION operator thereby allowing an attacker to insert their own SQL statements. 

Blind SQL Injection

Blind or Inferential SQL Injection attacks may take longer to execute, since the only response returned is in the form of a boolean. The attacker can exploit this to make requests and identify differences in the response being returned, which will confirm if the requests sent had a true or false result and then reconstruct the database structure and data.

Content based attacks focus on the response being returned, such as an HTTP response status code or the response data itself. On the other hand, time based attacks measure delays in the response being sent by the server where, for example, a ten second delay may confirm that the request returned a true result, while no delay means that the result was false. 

Out-of-Band SQL Injection

Out-of-Band attacks are the least common of the SQLi attacks and generally the most difficult to execute because the attack requires that the server hosting the database will communicate with the attacker’s infrastructure. This attack would normally be used if the channel through which the requests are being made is not consistent or stable enough for an in-band or blind SQLi attack to succeed. 

Summary

SQL Injection attacks require that the web application passes an attacker’s input to the database without making use of prepared SQL statements. Exploiting an SQL Injection vulnerability can, potentially, even allow an attacker to gain access to resources that are only reachable on local networks.

Since SQL Injection has been around since 1998 it is widely understood and easily exploitable using free and readily available tools. Most development frameworks have protection mechanisms built in that assist web developers to produce web applications that are not prone to SQL Injection attacks.

This goes to show that preventing SQL Injection vulnerabilities has become a necessity. Manually testing each form and parameter does not work well, which is why it makes sense to automate web application security testing with a tool such as Acunetix, which will not only find instances of SQL Injection but also other known vulnerabilities.