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. It is critical to sanitize input because an application may not be prepared to handle the malformed input or the unsanitized input may conceal an attack vector.

Noncompliant Code Example

This noncompliant code example uses shows JDBC code to authenticate a user generated string xmlString. The string is designed to be parsed by an XML parser (see IDS05-J. Prevent XML Injection). The description node is a String, as defined by the XML schema. Consequently, it accepts all valid characters including CDATA tags. This is dangerous because an attacker may be able to inject an executable script into the XML representation as CDATA tags, when processed, are removed by the XML parser. This can result in a Cross Site Scripting (XSS) vulnerability if the text in the nodes is displayed back to the userto 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
xmlString = "<item>\n" +
            "<description><![CDATA[<]]>script<![CDATA[>]]>
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  alert('XSS')&lt;![CDATA[&lt;]]&gt;/script&lt;![CDATA[&gt;]]&gt;&lt;/description&gt;\n&quot; +
            &quot;&lt;price&gt;500.0&lt;/price&gt;\n&quot; +
 	    &quot;&lt;quantity&gt;1&lt;/quantity&gt;\n&quot; +
 	    &quot;&lt;/item&gt;&quot;;

Likewise, if the XML tree is constructed at the server side from some inputs obtained from the client, it is also possible to insert comments of the form <!-- \-\->}} and 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 {{<!-- description in the description field and --></description> <price>100.0</price><quantity>1 in the quantity field. The equivalent XML representation is shown below. Note that the user can override the price field and change it from 500.0 to 100.0.

xmlString = "<item>\n"+
  	    "<description><!-- description</description>\n" +
 	    "<price>500.0</price>\n" +
 	    "<quantity>--></description> <price>100.0</price>
             <quantity>1</quantity>\n" +
 	    "</item>";

Compliant Solution

This compliant solution creates a white list of possible string inputs. It allows only characters in the description node, eliminating the possibility of injection of < and > tags.

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 + "'";
      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()) {
        throw new SecurityException("User name or password incorrect");
      }

      // 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 name.

  public void doPrivilegedAction(
    String username, char[] password
  ) throws SQLException {
    Connection connection = getConnection();
    if (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

...

TODO

Related Vulnerabilities

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

References

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

Tool	Version	Checker	Description
The Checker Framework	Include Page The Checker Framework_V The Checker Framework_V	Tainting Checker	Trust and security errors (see Chapter 8)
CodeSonar	Include Page CodeSonar_V CodeSonar_V	JAVA.IO.INJ.SQL	SQL Injection (Java)
Coverity	7.5	SQLI FB.SQL_PREPARED_STATEMENT_GENERATED_ FB.SQL_NONCONSTANT_STRING_PASSED_TO_EXECUTE	Implemented
Findbugs	1.0	SQL_NONCONSTANT_STRING_PASSED_TO_EXECUTE	Implemented
Fortify	1.0	HTTP_Response_Splitting SQL_Injection__Persistence SQL_Injection	Implemented
Klocwork	Include Page Klocwork_V Klocwork_V	SV.DATA.DB SV.SQL SV.SQL.DBSOURCE	Implemented
Parasoft Jtest	Include Page Parasoft_V Parasoft_V	CERT.IDS00.TDSQL	Protect against SQL injection
SonarQube	Include Page SonarQube_V SonarQube_V	S2077 S3649	Executing SQL queries is security-sensitive SQL queries should not be vulnerable to injection attacks
SpotBugs	Include Page SpotBugs_V SpotBugs_V	SQL_NONCONSTANT_STRING_PASSED_TO_EXECUTE SQL_PREPARED_STATEMENT_GENERATED_FROM_NONCONSTANT_STRING	Implemented

Related Vulnerabilities

CVE-2008-2370 describes 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.

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 AddedIDS07-J. Understand how escape characters are interpreted when String literals are compiled      10. Input Validation and Data Sanitization (IDS)      IDS09-J. Account for supplementary and combining characters in globalized code