Page History

SQL injection vulnerabilities arise in applications where elements of a SQL query originate from an untrusted source. Without precautions, the untrusted data may maliciously alter the query, resulting in information leaks or data modification. The primary means of preventing SQL injection are sanitization and validation, which are typically implemented as parameterized queries and stored procedures.

Suppose a system authenticates users by issuing the following query to a SQL database. If the query returns any results, authentication succeeds; otherwise, authentication fails.

SELECT * FROM db_user WHERE username='<USERNAME>' AND 
                            password='<PASSWORD>'

Suppose an attacker can substitute arbitrary strings for <USERNAME> and <PASSWORD>. In that case, the authentication mechanism can be bypassed by supplying the following <USERNAME> with an arbitrary password:

validuser' OR '1'='1

The authentication routine dynamically constructs the following query:

SELECT * FROM db_user WHERE username='validuser' OR '1'='1' AND password='<PASSWORD>'

If validuser is a valid user name, this SELECT statement yields the validuser record in the table. The password is never checked because username='validuser' is true; consequently, the items after the OR are not tested. As long as the components after the OR generate a syntactically correct SQL expression, the attacker is granted the access of validuser.

Similarly, an attacker could supply the following string for <PASSWORD> with an arbitrary username:

' OR '1'='1

producing the following query:

SELECT * FROM db_user WHERE username='<USERNAME>' AND password='' OR '1'='1'

'1'='1' always evaluates to true, causing the query to yield every row in the database. In this scenario, the attacker would be authenticated without needing a valid username or passwordInput sanitization refers to the elimination of unwanted characters from the input by means of removal, replacement, encoding or escaping the characters. Input must be sanitized, both because an application may be unprepared to handle the malformed input, and also because unsanitized input may conceal an attack vector.

Noncompliant Code Example

This noncompliant code example uses a user generated string xmlString, which will be parsed by an XML parser; see guideline IDS08-J. Prevent XML Injection. The description node is a String, as defined by the XML schema. Consequently, it accepts all valid characters including CDATA tagsshows JDBC code to authenticate a user to a system. The password is passed as a char array, the database connection is created, and then the passwords are hashed.

Unfortunately, this code example permits a SQL injection attack by incorporating the unsanitized input argument username into the SQL command, allowing an attacker to inject validuser' OR '1'='1. The password argument cannot be used to attack this program because it is passed to the hashPassword() function, which also sanitizes the input.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

class Login {
  public Connection getConnection() throws SQLException {
    DriverManager.registerDriver(new
            com.microsoft.sqlserver.jdbc.SQLServerDriver());
    String dbConnection = 
      PropertyManager.getProperty("db.connection");
    // Can hold some value like
xmlString = "<item>\n" +
    // "jdbc:microsoft:sqlserver://<HOST>:1433,<UID>,<PWD>"
    return DriverManager.getConnection(dbConnection);
  }

   "<description><![CDATA[<]]>script<![CDATA[>]]>
String hashPassword(char[] password) {
    // Create hash of password
  }

  public void doPrivilegedAction(String username, char[] password)
                                  alert('XSS')<![CDATA[<]]>/script<![CDATA[>]]></description>\n" throws SQLException {
    Connection connection = getConnection();
    if (connection == null) {
      // Handle error
    }
    try {
      String pwd = hashPassword(password);

      String sqlString = "SELECT * FROM db_user WHERE username = '" 
                         + username +
            "<price>500.0</price>\n" +
 	    "<quantity>1</quantity>\n" +
 	    "</item>";

This is insecure because an attacker may be able to inject an executable script into the XML representation, disguised using CDATA tags. CDATA tags, when processed, are removed by the XML parser, yielding the executable script. This can result in a Cross Site Scripting (XSS) vulnerability if the text in the nodes is displayed back to the user.

Similarly, if the XML tree is constructed at the server side from client inputs, comments of the form

<!-- -->

may be maliciously inserted in an attempt to override the server side inputs. For instance, if the user can enter input into the description and quantity fields, it may be possible to override the price field set by the server. This can be achieved by entering the string "<!-- description" in the description field and the string "--></description> <price>100.0</price><quantity>1" in the quantity field (without the '"' characters in each case). The equivalent XML representation is:

             "' AND password = '" + pwd + "'";
      Statement stmt = connection.createStatement();
      ResultSet rs = stmt.executeQuery(sqlString);

      if (!rs.next()) {
        throw new SecurityException(
          "User name or password incorrect"
        );
      }

      // Authenticated; proceed
    } finally {
      try {
        connection.close();
      } catch (SQLException x) {
        // Forward to handler
      }
    }
  }
}

Noncompliant Code Example (PreparedStatement)

The JDBC library provides an API for building SQL commands that sanitize untrusted data. The java.sql.PreparedStatement class properly escapes input strings, preventing SQL injection when used correctly. This code example modifies the doPrivilegedAction() method to use a PreparedStatement instead of java.sql.Statement. However, the prepared statement still permits a SQL injection attack by incorporating the unsanitized input argument username into the prepared statement.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

class Login {
  public Connection getConnection() throws SQLException {
    DriverManager.registerDriver(new
            com.microsoft.sqlserver.jdbc.SQLServerDriver());
    String dbConnection = 
      PropertyManager.getProperty("db.connection");
    // Can hold some value like
    // "jdbc:microsoft:sqlserver://<HOST>:1433,<UID>,<PWD>"
    return DriverManager.getConnection(dbConnection);
  }

  String hashPassword(char[] password) {
    // Create hash of password
  }

  public void doPrivilegedAction(
    String username, char[] password
  ) throws SQLException {
    Connection connection = getConnection();
    if (connection == null) {
      // Handle error
    }
    try {
      String pwd = hashPassword(password);
      String sqlString = "select * from db_user where username=" + 
        username + " and password =" + pwd;      
      PreparedStatement stmt = connection.prepareStatement(sqlString);

      ResultSet rs = stmt.executeQuery();
      if (!rs.next()) {

xmlString = "<item>\n"+
  	    "<description><!-- description</description>\n" +
 	    "<price>500.0</price>\n" +
 	    "<quantity>--></description> <price>100.0</price>
        throw new SecurityException("User name or password <quantity>1</quantity>\n" +
 	incorrect");
      }

      "</item>";

Note that the user can thus override the price field, changing it from 500.0 to an arbitrary value such as 100.0 (in this case).

Compliant Solution

// Authenticated; proceed
    } finally {
      try {
        connection.close();
      } catch (SQLException x) {
        // Forward to handler
      }
    }
  }
}

Compliant Solution (PreparedStatement)

This compliant solution uses a parametric query with a ? character as a placeholder for the argument. This code also validates the length of the username argument, preventing an attacker from submitting an arbitrarily long user nameThis compliant solution creates a white list of possible string inputs. It allows only alphabetic characters in the description node, consequently eliminating the possibility of injection of < and > tags.

  public void doPrivilegedAction(
    String username, char[] password
  ) throws SQLException {
    Connection connection = getConnection();
    if(!xmlString.matches("[\\w]*")) { // String does not match white-listed characters
  throw new IllegalArgumentException();
} 
// Use the xmlString (connection == null) {
      // Handle error
    }
    try {
      String pwd = hashPassword(password);

      // Validate username length
      if (username.length() > 8) {
        // Handle error
      }

      String sqlString = 
        "select * from db_user where username=? and password=?";
      PreparedStatement stmt = connection.prepareStatement(sqlString);
      stmt.setString(1, username);
      stmt.setString(2, pwd);
      ResultSet rs = stmt.executeQuery();
      if (!rs.next()) {
        throw new SecurityException("User name or password incorrect");
      }

      // Authenticated; proceed
    } finally {
      try {
        connection.close();
      } catch (SQLException x) {
        // Forward to handler
      }
    }
  	
}

Use the set*() methods of the PreparedStatement class to enforce strong type checking. This technique mitigates the SQL injection vulnerability because the input is properly escaped by automatic entrapment within double quotes. Note that prepared statements must be used even with queries that insert data into the database.

Risk Assessment

Failure to sanitize user input before processing or storing it can lead to injection of arbitrary executable contentresult in injection attacks.

Automated Detection

Related Vulnerabilities

CVE-2008-2370 describes a a vulnerability in Apache Tomcat 4.1.0 through 4.1.37, 5.5.0 through 5.5.26, and 6.0.0 through 6.0.16. When a RequestDispatcher is used, Tomcat performs path normalization before removing the query string from the URI, which allows remote attackers to conduct directory traversal attacks and read arbitrary files via a .. (dot dot) in a request parameter.

Search for other vulnerabilities resulting from the violation of this guideline on the CERT website.

Bibliography

\[[OWASP 2008|AA. Bibliography#OWASP 08]\] [Testing for XML Injection (OWASP-DV-008)|http://www.owasp.org/index.php/Testing_for_XML_Injection_%28OWASP-DV-008%29]

\[[OWASP 2005|AA. Bibliography#OWASP 05]\] 
\[[OWASP 2007|AA. Bibliography#OWASP 07]\]

Related Guidelines

Android Implementation Details

This rule uses Microsoft SQL Server as an example to show a database connection. However, on Android, DatabaseHelper from SQLite is used for a database connection. Because Android apps may receive untrusted data via network connections, the rule is applicable.

Bibliography

[OWASP 2005]	A Guide to Building Secure Web Applications and Web Services
[OWASP 2007]	OWASP Top 10 for Java EE
[Seacord 2015]	Image Added IDS00-J. Prevent SQL Injection LiveLesson
[W3C 2008]	Section 4.4.3, "Included If Validating"

...

Image Added Image Added Image AddedFilter data that passes through a trust boundary      13. Input Validation and Data Sanitization (IDS)      IDS02-J. Validate strings after performing normalization