What is SQL Injection?

SQL injection is a major concern when developing a Web application. It occurs when the application accepts a malicious user input and then uses it as a part of SQL statement to query a backend database.

An attacker can inject SQL control characters and command keywords (e.g., single quote (‘), double quote (“), equal (=), comment (- -), etc.) to change the query structure. Using these control characters with common SQL commands (e.g., SELECT, FROM, DELETE, etc.) enables access or retrieval of data elements from a backend database server.

A successful attack requires a Web application to include malicious code from an attacker in a SQL statement. The malicious code usually comes from an untrusted source. In some cases, internal system databases can also be the source of malicious data. When malicious SQL statements execute against a backend database, an attacker can modify or access the database. This depends how the attacker crafts the malicious data.

This attack example illustrates code to accept a username and password from a user. The user-provided input crafts a SQL query to execute against a database. The database includes a table named “user,” with columns for name and password.

Consider a user authenticating to an application with “admin” as a username and “xDK9&GoP1” as a password. These are valid credentials. During application login, follow the SQL statement that executes against the database server:

SELECT name FROM user WHERE name=‘admin’ AND passwd=‘xDK9&GoP1’

This query executes against the database and authenticates the user due to valid credentials.

Now consider an attacker attempting to authenticate to an application using the “password’ OR ‘a’=’a” password value as the injection payload. During application login, following the SQL statement executes against the database server:

SELECT name FROM user WHERE name=‘admin’ AND passwd=‘password’ OR ‘a’=‘a’

Upon execution of this query, an attacker successfully authenticates to an application since ‘a’=‘a’ always returns true, resulting in authentication bypass.

With a successful attack, an attacker can gain:

1. Unauthorized access to an application. An attacker can successfully bypass an application’s authentication mechanism to have illegitimate access to it.
2. Information disclosure. An attack could lead to a complete data leakage from the database server.
3. Loss of data availability. An attacker can delete records from the database server.
4. Compromised data integrity. As SQL statements are also used to modify or add the record, an attacker can use SQL injection to modify or add data stored in a database. This would lead to compromised data integrity.

Prepared statements with parameterized queries. Use of prepared statements with parameterized queries is a strong control to mitigate an attack. Instead of writing dynamic queries—which fails to differentiate between application code and data—prepared statements force developers to use static SQL query and then pass in the external input as a parameter to query. This approach ensures the SQL interpreter always differentiates between code and data.

Let’s look at the re-implementation of the same authenticate() method discussed above. However, in this case using a parameterization feature with the PreparedStatement object:

Irrespective of the user input, runtime variables name and pass cannot affect the behavior of the query. Note that the use of only the PreparedStatement object isn’t a good defense. It must be used along with parameterization feature (“?”) for all runtime elements. Without the parameterization feature, string concatenation leads to SQL injection, even in the presence of the PreparedStatement object.

Stored procedures. Stored procedures are the SQL statements defined and stored in the database itself and then called from the application. Developers are usually only required to build SQL statements with parameters that are automatically parameterized. However, it’s possible for a developer to generate dynamic SQL queries inside stored procedures. Implement stored procedures safely by avoiding dynamic SQL generation inside.

Input validation. A common source of SQL injection is maliciously crafted external input. As such, it’s always a good practice to only accept approved input—an approach known as input validation. To protect against it, there are two variants of input validation: avoid list validation and preferlist validation.

Avoid listvalidation tests the external input against a set of known malicious inputs. An application compiles a list of all malicious inputs, and then verifies the external input against the compiled list. Therefore, it’s easy for an attacker to bypass avoid list validation since they can come up with multiple variants of malicious input that may not be part of the complied avoid list.

Preferlisting is a much better approach to mitigate risk. Preferlist validation tests an external input against a set of known, approved input. With prefer list input validation, the application knows exactly what’s desired and rejects other input values that fail a test against the known, approved input.

Principle of least privilege. This is a standard security control that helps minimize the potential damage of a successful attack. Application accounts shouldn’t assign DBA or admin type access onto the database server. Additionally, depending on access requirements, they should be restricted to least privileged access. For example, accounts that only require read access are only granted read access to the table they need to access. This ensures that if an application is compromised, an attacker won’t have the rights to the database through the compromised application.