WHAT IS SQL INJECTION? TYPES, EXAMPLES, PREVENTION
Published: 2021-09-06 20:07:09

Code injections are the oldest known web application attack vectors, with successful hacks leading to a **denial of service, loss of data integrity, data loss,** and the **compromise of entire networks.** As a result, [injection-based security vulnerabilities](https://crashtest-security.com/what-are-the-different-types-of-injection-attacks/) rank number one on the [OWASP Top 10 Security Risks](https://crashtest-security.com/owasp/). They allow attackers to apply malicious code into information systems through user input interfaces. One such mechanism is a **SQL Injection** attack that involves the insertion of SQL queries to client input to access and manage backend databases. SQL Injection attacks are mostly carried out on web applications that rely on dynamic databases but lack sufficient input validation.

This article delves into SQL Injection based security risks and the various best practices and tools to prevent such attacks.

1. [A Deep Dive into SQL Injection Vulnerabilities](https://crashtest-security.com/sql-injections/#sqlinjectiondeepdive)
2. [SQLi Security Assessment](https://crashtest-security.com/sql-injections/#sqlinjectionassessment)
3. [Types of SQL Injection Attacks](https://crashtest-security.com/sql-injections/#sqlinjectionattacktypes)
4. [How Attackers Perform SQLi](https://crashtest-security.com/sql-injections/#sqlinjectionperformattack)
5. [Commonly Known SQLi Attack Examples & Technique](https://crashtest-security.com/sql-injections/#sqlinjectionattackexamples)
6. [How to Prevent SQL injection Attacks](https://crashtest-security.com/sql-injections/#sqlinjectionprevention)
7. [Tools to Prevent, Detect and Remediate SQLi Vulnerabilities](https://crashtest-security.com/sql-injections/#sqlinjectiontools)
8. [Summary](https://crashtest-security.com/sql-injections/#summary)

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ezcpw19830jmp1kiujm7.png)

## A DEEP DIVE INTO SQL INJECTION VULNERABILITIES

When a malicious user performs an SQL Injection (SQLi) attack, they typically intend to control the web application’s database server using malicious SQL statements. This allows them to bypass authentication controls required to access and retrieve the contents of the database. This usually is initiated by hackers scouting the application firewall for vulnerable user input points. Once identified, the attacker creates input content known as a **malicious payload,** executing unauthorized SQL injection commands at the back-end database. 

Attackers commonly develop SQL injection commands to perform a wide variety of malicious acts, such as:

* Retrieving user credentials within the database
* Selecting and outputting crucial system data
* Appending or adding new data to a database
* Deleting tables and records from the database
* Using the back-end database server to access the Operating System

## SQLI SECURITY ASSESSMENT

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5p4edng48sf9i7cogfqe.png)

CVSS Vector: [AV:N/AC:L/PR:N/UI:N/S:U/C:H/I:H/A:N](https://nvd.nist.gov/vuln-metrics/cvss/v3-calculator?vector=AV:N/AC:L/PR:N/UI:N/S:U/C:H/I:H/A:N)

## TYPES OF SQL INJECTION ATTACKS

SQLi is a common and well-documented attack strategy whose success has far-reaching business consequences such as unauthorized viewing of credentials and gaining administrative access to the application’s database. SQLi attacks are categorized based on the following methods used to gain database access:

### IN-BAND SQLI

The attacker gathers their results using the same communication channel they use to launch attacks. This code injection technique is common since it offers a simple, efficient way to access the database server. There are several types of in-band SQLi, including:

### ERROR-BASED SQLI

The attacker relies on error messages relayed by the database server to learn about the database structure. Sometimes the error messages can provide sufficient data to enumerate the entire database.

### UNION-BASED SQLI

In this case, the malicious payload uses SQLs UNION operator to combine the results of several SELECT statements to one output, which is returned along with the HTTP response. 

### BLIND SQL INJECTION/INFERENTIAL SQLI

In a Blind SQL injection technique, the hacker sends malicious data payloads, then reconstructs the database server’s structure using the web application’s response. In Blind SQLi, data is not actually retrieved by the web application, so the attacker cannot view the results of an attack using the same communication channel they used to launch this type of attack. Blind SQL Injection, also commonly referred to as the Inferential method, is of several types:

### CONTENT-BASED SQLI

Content/Boolean-based SQLi attacks force the web application to return different results depending on whether the malicious SQL query returns a TRUE or FALSE result. The query result determines whether the content in the HTTP response stays the same or changes. As a result, the attacker can determine whether the malicious payload returns a true or false result even though the query returns no data from the database.

### TIME-BASED SQLI

The attacker sends a query that forces the application to wait for a specific duration before returning a response. The attacker uses the response time to determine whether the result of a query is TRUE or FALSE. 

### OUT-OF-BAND SQLI

The attacker uses the same channel to launch the attack and gather results. While this attack is uncommon since it relies on certain  database server features being enabled, it offers an alternative for orchestrating injection attacks if the server responses are unstable. 

## HOW ATTACKERS PERFORM SQLI

### WEB-PAGE SQLI

When a web application asks for user input, some attackers supply SQL statements that are run unknowingly on the database. This section explores ways in which attackers create payloads that generate valid SQL statements at the user input.

### SQLI BASED ON 1=1 IS ALWAYS TRUE

The attacker uses an OR statement to access all records in a table. When prompted for a User ID, the attacker inputs 105 OR 1=1. If the web application lacks sufficient input validation, this statement is accepted the generates a SQL command of the form:

```
SELECT * FROM Users WHERE UserId = 105 OR 1=1;
```

This query accesses and displays all records in the Users table since 1=1 is always TRUE.

### SQLI BASED ON “=” IS ALWAYS TRUE

Attackers use an OR statement to get combinations of related data. When prompted for a Username and Password, they key in ” or ” “=” on both fields. The database server then executes a command of the form:

```
SELECT * FROM Users WHERE Name =”” or “”=”” AND Pass =”” or “”=””
```

This command returns every row in the Users table since OR “=” is always true for Usernames and Passwords.

### SQLI BASED ON BATCHED STATEMENTS

Modern database servers accept batch statements, which hackers may exploit to target particular records or tables. For instance, when prompted for their User ID, they input 105; DROP TABLE Suppliers. This executes a valid statement of the form:

```
SELECT * FROM Users WHERE UserId = 105; DROP TABLE Suppliers;
```

This deletes the table Suppliers from User records.

## COMMONLY KNOWN SQLI ATTACK EXAMPLES & TECHNIQUE

Successful SQLi attacks can be used to tamper with existing data, disclose crucial information, gain administrative rights to networks and cause repudiation issues. Examples of SQLi attacks on enterprise systems include:

### THE 2019 BULGARIAN NATIONAL REVENUE AGENCY DATA BREACH

On 15th July 2019, an anonymous hacker announced that they successfully deployed an SQL injection on the national tax authority’s servers. The hacker extracted 11 GB of data in 57 folders of .csv files that contained names and identification information of over 6 million people. The files also included citizens’ social security payments, taxes, online betting data, debts, company activities, and revenues collected for over 12 years.

### THE 2020/2021 ACCELLION DATA BREACH

Attackers used an SQL injection vulnerability to gain initial access to the Accellion File Transfer Appliance (FTA) and cause a massive data breach affecting multiple companies in different sectors and multiple countries. The victims’ data began to appear on CLOP LEAKS, a ransomware forum. The zero-day vulnerabilities that enabled a successful attack are yet to be fixed as more companies keep reporting compromises of user data.

## HOW TO PREVENT SQL INJECTION ATTACKS

Preventing SQLi attacks is a complex and rigorous process since prevention techniques vary according to the programming language used, the SQL database engine, and the SQLi subtype being addressed. This section explores the tools and best practices to prevent SQL Injection vulnerabilities.

### BEST PRACTICES TO PREVENT SQLI VULNERABILITIES

Some strategic principles and practices to keep web applications safe from SQLi attacks include:

#### TRAINING & AWARENESS

Everyone involved in developing and managing the application should understand the risk and impacts of SQL injections. Training should also be extended to users to understand why it is important only to include valid inputs when prompted.

#### FILTER USER INPUT

A database administrator should never trust user input. Internal and public user inputs should all be filtered and validated before being exposed to the database server.

#### USE WHITELIST-BASED FILTERS

Attackers will always develop clever methods to circumvent blacklists. Whitelisting prevents attacks using a list that only allows certain users to access the protected system. In addition, malicious payloads deployed by SQLi injections cannot execute when they don’t exist in the whitelist.

#### USE UPDATED WEB TECHNOLOGIES

Software updates often include patches for discovered vulnerabilities. Hackers typically rely on these vulnerabilities to deploy malicious payloads. Using the latest patched versions of development environments and frameworks will meet compliance standards and keep the web application safe from exploits as most software organizations try to stay ahead of hackers. 

#### REGULAR SCANNING

Attackers inject malicious input through vulnerabilities they discover in the system code. Therefore, security teams should undertake a SQL vulnerability assessment using the right tools to find any possible vulnerabilities before attackers can take advantage of them.

### SQL INJECTION PRACTICAL PREVENTION TIPS

To prevent SQL injection attacks, treat all user input as potentially malicious and follow some programming guidelines:

#### FILTER USER INPUT

For an attacker to successfully execute an SQL injection, he needs to plant some code run by the web application’s database. Therefore all user input should be validated first and limited to the needed characters. E.g., you may ask a user to input a username, password, and e-mail address in a registration form. You can limit the allowed characters of these input fields to characters that do not interfere with the database. The following example filters out user input for the three values in PHP:

```
if (preg_match("/[^A-Za-z0-9]/", $username) ||
  (preg_match("/[^A-Za-z0-9\!_-]/", $password) ||
  (preg_match("/[^A-Za-z0-9_-@]/", $email)) {
    echo "Invalid Characters!";
} else {
    # Run Database Command
}
```

#### DATABASE MAPPERS

Most modern web frameworks provide some abstraction of database handling. E.g., Laravel provides Eloquent queries. Created objects are automatically converted and stored or retrieved from the database. In the example of the user registration form, one could create the user object in the following way:

```
$user = new User;
$user->username = $request->username;
$user->password = $request->password;
$user->email = $request->email;
$user->save();
```

The resulting SQL statement is automatically sanitized and will prevent SQL injections.

#### SANITIZE USER INPUT / PREPARED STATEMENTS

It may not always be possible to use a database mapper. In these cases, use prepared statements to create your SQL queries. These forms of statements validate and sanitize the user-provided values and therefore prevent SQL injections. E.g., in PHP, you can create a prepared statement the following way:

```
$stmt = $mysqli->prepare("INSERT INTO users(username, password, email) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $username, $password, $email) # "sss" here states, that three strings are expected.
$username = $request->username;
$password = $request->password;
$email = $request->email;
$stmt->execute();
```
## TOOLS TO PREVENT, DETECT AND REMEDIATE SQLI VULNERABILITIES

Regular SQL vulnerability assessment scan is the first actionable remediation to keeping web applications safe from SQLi vulnerabilities. Here is a list of popular vulnerability scanners that can help security teams identify and resolve SQLi vulnerabilities:

### [CRASHTEST SECURITY](https://crashtest-security.com/)

**Crashtest Security** is an end-to-end security testing suite that simplifies vulnerability scanning through quick security assessments, risk reduction, and rich vulnerability reports. In addition, Crashtest integrates with multiple layers of the development stack, thereby allowing teams to establish a continuous testing process and eliminate attack surfaces on APIs and web applications.

The security platform also automates vulnerability scanning and seamlessly delivers security information on the web application, allowing development teams to focus on clean code. 

### ACUNETIX

A complete security testing solution that can be used for both distributed and standalone systems. [Acunetix](https://www.acunetix.com/) integrates seamlessly with market-leading development frameworks and comes with in-built vulnerability scanning and management functions. Acunetix also interfaces with third-party issue trackers to help with end-to-end vulnerability management.

### BURP SUITE

A Web Application Security Testing solution developed by [PortSwigger](https://www.imperva.com/) to help organizations fight zero-day threats through automated scanning. The suite also includes penetration testing functions that can be used to identify the impact of SQLi attacks on web servers. The suite comes in an Enterprise and Professional Edition that offers different vulnerability management tools for web application security. 

### IMPERVA DATABASE SECURITY

[Imperva](https://www.imperva.com/) is a database risk & compliance management platform that delivers analytics, response, and protection for organizational data assets. The platform integrates with any database so organizations can use its global preconfigured reports, policies, and templates as soon as Imperva is deployed. 

## SUMMARY

Despite extensive efforts to remediate issues, SQLi vulnerabilities are still popular. It is estimated that **8% of websites and web applications** have at least one vulnerability. In addition, attackers leverage SQLi vulnerabilities in many ways to compromise a web application through unauthorized database access. 

To help with this, the [Crashtest Security Suite](https://crashtest-security.com/vulnerability-scanner/) helps organizations protect their web applications and APIs from injection attacks through continuous vulnerability scans and testing. In addition, the security platform fits seamlessly in the DevOps toolchain, enabling organizations to develop and deploy safer javascript, web applications, and APIs.

[Sign up for free here](https://crashtest.cloud/registration?utm_source=blog&utm_medium=referral&utm_campaign=blog_reg&_ga=2.249044103.1096107355.1630931897-1082379764.1630931897) to know how Crashtest Security can help with a rapid vulnerability assessment scan to achieve a robust security model. 

*This article has already been published on https://crashtest-security.com/sql-injections/ and has been authorized by Crashtest Security for a republish.*