Web Security 101: SQL Injection Attacks Explained

What Is a SQL Injection Attack?

An SQL injection attack (SQLi) is a method that allows attackers to manipulate a backend database by injecting SQL statements to execute malicious actions. SQLi attacks are a widely-used attack vector because they can bypass application security measures such as logins. 

An SQL injection attack can manipulate an application’s queries to a database and enables the attacker to retrieve otherwise protected data. An attacker might use SQL injection to sabotage a database or steal confidential data such as private customer information or sensitive business data. 

How Does a SQL Injection Attack Work?

A SQL query is a request for an operation to be performed on a database. You can also use SQL queries to run operating system commands. Each query contains a set of parameters that ensures when the query is run, only the records the user needs are returned or modified. During SQL injection, an attacker exploits this by injecting malicious code into a query input form.

The first step in a SQL injection attack is to investigate how the target database is behaving. This is done by using random values in the query to see how the server responds.

Attackers then use their knowledge of the database to construct a query that the server interprets and executes as a SQL command. 

For example, a database that contains information about customers with unique customer ID numbers. Instead of searching for a specific customer ID, an attacker could insert the a value like:

CustomerID = 2536 OR 1 = 1

The 1 = 1 statement is always true, so the SQL query will return all available customer IDs and their data. This allows an attacker to bypass authentication and gain administrator-level access.

In addition to returning incorrect information, SQL injections can be used to delete entire database tables, delete specific records, modify or add unwanted data. Depending on the permissions the database has for the underlying operating system, attackers may be able to escalate privileges and gain control over the entire server.

See this blog post for real life examples of SQL injection attacks and additional code examples.

Types of SQL Injections

There are three types of SQL injection attacks based on the techniques used to compromise the backend. 

In-Band SQL Injection

With in-band SQLi, an attacker launches the attack and collects results using the same communication channel. It is simple and efficient, making it a common attack vector. 

There are two main techniques within this category:

• Error-based—induces the database to generate error messages, which may help inform the attacker about the database structure.
• Union-based—exploits the UNION SQL operator to fuse multiple database statements to produce an HTTP response, which may expose data the attacker can leverage.

Out-of-Band SQL Injection

Out-of-band SQLi is only possible with specific database server features enabled. Attackers use this method when they cannot launch attacks and collect data using the same channel (for example, if the server is slow and cannot support these actions). Out-of-band SQLi relies on the server’s capacity to generate HTTP or DNS requests transferring data to the attacker.

Blind SQL Injection

With blind or inferential SQLi, an attacker learns about a server’s structure by sending data payloads and observing the server’s responses. The attacker can’t see in-band information because the server doesn’t transfer data to the attacker from the database. This attack type is slow to execute but can cause significant damage.

There are two main techniques within this category:

• Boolean—the database receives SQL queries prompting the app to return results, which differ based on the queries being true or false. The results let the attacker determine if the result generated by a query is true or false.
• Time-based—the database receives SQL queries and has to wait before responding. The response time (in seconds) lets the attacker determine if a query is true or false without accessing data from the database.

SQL Injection Prevention

There are several ways you can protect your applications against SQL injection attacks.

Endpoint Protection

Endpoint protection involves securing endpoint devices that may serve as an entry point for an attacker. Endpoint security is especially important for databases because they are almost always mission-critical systems. 

In addition to traditional antivirus capabilities, modern endpoint security platforms typically offer sophisticated protection against malware and zero-day threats. An endpoint protection system can rapidly analyze security events, identify threats, block attacks in progress, and mitigate their impact. 

Endpoint protection solutions can help defend against SQL injection and other database threats by monitoring network and application traffic and identifying malicious patterns. In many cases, endpoint protection systems can block database traffic that includes a malicious SQL request, even if the underlying database is vulnerable to SQL injection.

Data Masking

Data masking is another common database security mechanism that can help prevent SQL injection. Data masking ensures that users only see data that is relevant to them, and masks or hides other sensitive data. Depending on the type of SQL injection attack, data masking can help reduce the damage caused by an attacker. Attackers typically manipulate SQL queries to receive additional data that cannot be accessed by the original user, but in an effective data masking implementation, the database will not return this data to a non-privileged user.

User Input Validation

You cannot use bind variables everywhere in an SQL query—for example, aspects like table or column names and sort order indicators (ASC/DESC) are not legal locations for variable binding. For these cases, you should redesign queries or validate inputs. The table and column names values should ideally come from the code, not from user-supplied parameters.

If you allow user-supplied parameter values to target table or column names, you should ensure they are mapped to specified, permitted table or column names. The main objective is to prevent the introduction of unvalidated user input into the query. However, this situation indicates a poor design, so you should consider a full rewrite when possible.

Parameterized Queries

Parameterized queries are the safest way to write queries to a database. You should ensure that all developers use prepared statements with bind variables. When using parameterized queries, you first define the overall SQL code before passing the parameters to a query. Compared to dynamic queries, they are easier to write and understand.

Coding with parameters lets databases distinguish between data and code, whatever user input they receive. Prepared statements prevent attackers from changing a query’s intent even when they manage to inject malicious SQL commands. 

Here are some recommendations based on the language you use: 

• .NET—SqlCommand(), OleDbCommand(), etc.
• Java EE—PreparedStatement()
• Hibernate—createQuery(), named parameters
• PHP—bindParam() with PDO
• SQLite—create statement objects with sqlite3_prepare()

In rare cases, using prepared statements may impact performance—in such cases, you should validate all data strongly or escape (encode) user input instead. 

Stored Procedures

Stored procedures work similarly to parameterized queries, although it is important to ensure you implement them safely to protect them from SQL injection. With this method, you build automatically parameterized SQL statements. It differs from using prepared statements because you define and store the procedures within the database. Each method effectively protects against SQL injection attacks, so you should use whichever method best suits your organization’s needs.

Conclusion

In this article, I explained the basics of SQL injection attacks and showed several ways you can protect against them:

• Endpoint protection—these solutions can be deployed on a SQL database to identify malicious activity and prevent attacks, even if the database is vulnerable.
• User input validation—ensure any inputs used in a SQL query are properly sanitized to ensure they do not contain malicious statements.
• Parameterized queries—this is the safest way to write data to a database, because it clearly distinguishes between data and code and prevents context switching.
• Stored procedures—when implemented correctly, stored procedures can also prevent SQL injection by defining and storing procedures within a database and not relying on user inputs.

I hope this will be useful as you improve the security posture of your databases and applications.