Skip to content

SQL Injection Prevention Cheat Sheet

Introduction

A SQL injection attack occurs when untrusted data (usually strings) becomes part of a query's SQL syntax, modifying the logic of the SQL request.

The malicious data can come from various sources such as:

  • an HTTP request (for example, from a malicious user);
  • an LDAP directory;
  • an incoming email;
  • a third party REST API service;
  • the database itself.

Impact: A successful SQL injection attack can, depending on database features and privileges, read data from the database, modify database data (insert/update/delete), execute administration operations on the database (such as shutdown the DBMS), recover the content of a given file existing on the DBMS file system or write files into the file system, and, in some cases, execute operating system commands.

Resolution: define the SQL structure separately from its data values, then bind those values through the database API:

  • use parameterized queries for dynamic data values;
  • use fixed application-owned choices for dynamic table names, column names, sort order (ASC, DESC), etc.

These controls apply regardless of whether data comes from an HTTP request, another service, or previously stored data.

A related class of vulnerability affects object-relational mapping (ORM) frameworks, such as JPA/Hibernate, which have their own query language (JPQL/HQL). Applications using these frameworks might be vulnerable to injections of these query languages:

  • JPQL injections (JPA);
  • HQL injections (Hibernate).

For other injection types, see the Injection Prevention Cheat Sheet.

Examples of injections

Basic SQL injection

A simple SQL injection flaw in Java is shown below. The "customerName" parameter is simply concatenated to build the query and an attacker can enter SQL code into that query and the application would take the attacker's code and execute it on the database.

// UNSAFE!
String query = "SELECT account_balance FROM user_data WHERE user_name = '"
    + request.getParameter("customerName") + "'";
Statement statement = connection.createStatement();
ResultSet results = statement.executeQuery(query);

As explained below, the recommended safe implementation would use parameterized queries, using ? as placeholders for the values:

String query = "SELECT account_balance FROM user_data WHERE user_name = ?";
PreparedStatement statement = connection.prepareStatement(query);
statement.setString(1, customerName);
ResultSet results = statement.executeQuery();

An input such as tom' or '1'='1 is treated as the literal username to match, not as a condition added to the query. Parameter binding prevents this value from changing SQL syntax; it does not decide whether the caller may access the matching account.

SQL injection with Procedure Calls

SQL injections can happen as well in procedure calls:

// UNSAFE
CallableStatement statement = connection.prepareCall("{call sp_updateAccountBalance('" + customerName + "' , " + amount + ")}");
statement.execute();

As below, the recommended implementation uses parameterized queries:

CallableStatement statement = connection.prepareCall("{call sp_updateAccountBalance(?,?)}");
statement.setString(1, customerName);
statement.setBigDecimal(2, amount);
statement.execute();

While this prevents injection when calling the procedure, the procedure itself might still be vulnerable to SQL injection, as explained in SQL Injection inside stored Procedures and Functions.

SQL Injection in ORM Query Languages

Some ORM frameworks such as Hibernate use dedicated query languages, such as Hibernate Query Language (HQL), which may suffer from the same injection problems (HQL Injection):

// UNSAFE: HQL injection!
Query unsafeHQLQuery = session.createQuery("from Inventory where productID='"+userSuppliedParameter+"'");

Parameterized queries are the recommended solution here as well:

// Here is a safe version of the same query using named parameters
Query safeHQLQuery = session.createQuery("from Inventory where productID=:productid");
safeHQLQuery.setParameter("productid", userSuppliedParameter);

SQL Injection inside stored Procedures and Functions

Binding procedure arguments protects the call, not dynamic SQL constructed inside the procedure.

Use static SQL with parameters, or separately bind data values in internal dynamic queries instead of concatenating them into SQL text. Microsoft's secure dynamic SQL guidance calls for reviewing procedures that construct SQL and parameterizing their commands.

For example, PostgreSQL documents EXECUTE ... USING for binding values and format('%I', ...) for quoting identifiers. Select identifiers from fixed application-owned mappings. Identifier quoting protects SQL syntax, but does not restrict which tables the application may select.

Primary Defenses

The preferred patterns are:

  • parameterized queries when including dynamic data in SQL requests;
  • for structural parts that cannot be parameterized (table names, column names, sort direction), map user choices to a strict allow-list of fixed, application-owned values.

Relying on manually escaping dynamic inputs is error-prone and is strongly discouraged.

Parameterized Queries

Define the SQL structure separately from its data values and bind those values through the database API, as shown in the Basic SQL injection example. Concatenating untrusted values into the query before preparing it defeats this protection.

See the Query Parameterization Cheat Sheet for additional examples of parameterized queries in several languages, including Java, .NET, Ruby, PHP, Cold Fusion, Perl, and Rust. Other examples can be found at Bobby Tables.

Allow-List Input Validation

Parameter markers represent data values, not table names, column names, or SQL keywords such as ASC and DESC; see the PDO parameter-marker limitations. Keep these structural choices fixed in code where possible. When users need to select among them, map their selection to a small set of application-owned values allowed for that query and reject anything else. In this example, tableName can only hold one of two fixed values, so it can be appended to the query:

String tableName;
switch(PARAM) {
  case "room":
    tableName = "roomTable";
    break;
  case "floor":
    tableName = "floorTable";
    break;
  default:
    throw new InputValidationException("unexpected table name");
}

Map a requested sort direction the same way, to the literal ASC or DESC, rather than appending the supplied text.

Manually escaping (STRONGLY DISCOURAGED)

Do not manually escape untrusted values to construct SQL queries. Use parameterized queries instead.

Manually escaping the values into the SQL string is error-prone and attempts to do something which is normally handled by the database driver through parameter bindings. It can fail and can allow SQL injections for several reasons:

  • SQL escaping rules are not always straightforward;
  • different SQL dialects have different rules;
  • some databases have configuration which might affect the escaping behavior (such as MySQL NO_BACKSLASH_ESCAPES).

Replace escaping-based query construction with parameter binding.

Hardening

We also recommend adopting all of these additional defenses to provide defense in depth to limit damage in case of successful SQL injection attacks.

Least Privilege

To minimize the potential damage of a successful SQL injection attack, you should minimize the privileges assigned to every database account in your environment. Start from the ground up to determine what access rights your application accounts require, rather than trying to figure out what access rights you need to take away.

Make sure that accounts that only need read access are only granted read access to the tables they need access to. DO NOT ASSIGN DBA OR ADMIN TYPE ACCESS TO YOUR APPLICATION ACCOUNTS. We understand that this is easy, and everything just "works" when you do it this way, but it is very dangerous.

Minimizing Application and OS Privileges

SQL injection is not the only threat to your database data. Attackers can simply change the parameter values from one of the legal values they are presented with, to a value that is unauthorized for them, but the application itself might be authorized to access. As such, minimizing the privileges granted to your application will reduce the likelihood of such unauthorized access attempts, even when an attacker is not trying to use SQL injection as part of their exploit.

While you are at it, you should minimize the privileges of the operating system account that the DBMS runs under. Don't run your DBMS as root or system! Most DBMSs run out of the box with a very powerful system account. For example, MySQL runs as system on Windows by default! Change the DBMS's OS account to something more appropriate, with restricted privileges.

Details Of Least Privilege When Developing

If an account only needs access to portions of a table, consider creating a view that limits access to that portion of the data and assigning the account access to the view instead of the underlying table. Rarely, if ever, grant create or delete access to database accounts.

If you adopt a policy where you use stored procedures everywhere, and don't allow application accounts to directly execute their own queries, then restrict those accounts to only be able to execute the stored procedures they need. Don't grant them any rights directly to the tables in the database.

Least Admin Privileges For Multiple DBs

The designers of web applications should avoid using the same owner/admin account in the web applications to connect to the database. Different DB users should be used for different web applications.

In general, each separate web application that requires access to the database should have a designated database user account that the application will use to connect to the DB. That way, the designer of the application can have good granularity in the access control, thus reducing the privileges as much as possible. Each DB user will then have select access to only what it needs, and write-access as needed.

As an example, a login page requires read access to the username and password fields of a table, but no write access of any form (no insert, update, or delete). However, the sign-up page certainly requires insert privilege to that table; this restriction can only be enforced if these web apps use different DB users to connect to the database.

Enhancing Least Privilege with SQL Views

You can use SQL views to further increase the granularity of access by limiting the read access to specific fields of a table or joins of tables. It could have additional benefits.

For example, a product catalog can use a view that exposes product names and retail prices while omitting supplier costs. Grant the application's database account only SELECT access to that view, without access to the underlying table or other objects that expose the omitted fields. Verify your database's view privilege rules; for example, PostgreSQL normally checks underlying table access using the view owner's privileges.

This limits the data exposed through a compromised application account. Queries against views still require parameterization to prevent SQL injection.

Input Validation

Input validation complements parameter binding; it does not replace it. Continue binding all data values, even when they pass type, length, or format checks. Validation of a value does not make it safe to concatenate into SQL, and a validation approach that is safe for one SQL dialect may be unsafe for another. See the Input Validation Cheat Sheet.

Testing and detection

Review query construction in application code and stored procedures. Check that untrusted values are bound rather than concatenated into SQL, and that dynamic identifiers and sort directions come from the query's fixed mapping. Preparing the outer call is not evidence that a procedure's internal SQL is safe.

Use the OWASP Web Security Testing Guide's SQL injection tests for manual and automated testing, including blind and out-of-band techniques.

References