Writing secure code that does not allow for the injection of malicious code is a challenge for any developer. We asked the experts from Synopsys Software Integrity Group to share some best practices for how to avoid these attacks. The following is a guest post by that team.
Definition of SQL injection
A widespread problem for programmers is avoiding injection attacks: Writing secure code that does not allow for the injection of malicious code into an application. Injection attacks stem from a lack of strict separation between program instructions (code) and user-provided or external input. This allows an attacker to inject malicious code into a data snippet. The lack of separation means that an application can execute malicious code as crafted by the attacker.
Injection attacks are some of the most successful and widespread types of attack. SQL injection is also one of the most common types of injection attack. To carry it out, an attacker provides malicious SQL statements through the application. These control the backend database server. SQL injection is independent of the technology used for the underlying application. Thus, this attack technique is common.
How attackers exploit SQL
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 [for example, single quote (‘), double quote (“), equal (=), comment (- -)] to change the query structure. Using these control characters with common SQL commands (SELECT, FROM, DELETE) 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 on how the attacker crafts the malicious data.
Let’s explore a SQL attack example in more detail
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.
Public Boolean authenticate (String name, String pass) { Statement stmt = this.conn.createStatement(); String sql = “SELECT name FROM user WHERE name=’ ” + name + “ ’ AND passwd =’ ” + pass + “ ‘ ”; ResultSet results = stmt.executeQuery(sql); return results.first(); }
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 because ‘a’=‘a’ always returns true, resulting in authentication bypass.
With a successful attack, an attacker can gain:
- Unauthorized access to an application. An attacker can successfully bypass an application’s authentication mechanism to have illegitimate access to it.
- Information disclosure. An attack could lead to a complete data leakage from the database server.
- Loss of data availability. An attacker can delete records from the database server.
- 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.
How can organizations mitigate SQL Injection?
Prepared statements with parameterized queries
Using prepared statements with parameterized queries is a strong control to mitigate against attack. Instead of writing dynamic queries, which fail 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:
Public Boolean authenticate (String name, String pass) { PreparedStatement pstmt; String sql = “SELECT name FROM user WHERE name = ? AND passwd =? ”; pstmt = this.conn.prepareStatement(sql); pstmt.setString(0, name); pstmt.setString(1, pass); ResultSet results = pstmt.executeQuery(); return results.first(); }
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 prefer list validation.
Avoid list validation 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 compiled avoid list.
Prefer listing is a much better approach to mitigate risk. Prefer list 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. In addition, 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.
Additional questions or comments?
Share them on our developer forums!