...
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.
| Code Block | ||
|---|---|---|
| ||
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:
| Code Block | ||
|---|---|---|
| ||
validuser' OR '1'='1 |
The authentication routine dynamically constructs the following query:
| Code Block | ||
|---|---|---|
| ||
SELECT * FROM db_user WHERE username='validuser' OR '1'='1' AND password='<PASSWORD>' |
...
Similarly, an attacker could supply the following string for <PASSWORD> with an arbitrary username:
| Code Block | ||
|---|---|---|
| ||
' OR '1'='1 |
producing the following query:
| Code Block | ||
|---|---|---|
| ||
SELECT * FROM db_user WHERE username='<USERNAME>' AND password='' OR '1'='1' |
...
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.
| Code Block | ||||
|---|---|---|---|---|
| ||||
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 + "'";
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
}
}
}
}
|
...
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.
| Code Block | ||||
|---|---|---|---|---|
| ||||
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
}
}
}
}
|
...
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.
| Code Block | ||||
|---|---|---|---|---|
| ||||
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
}
}
}
|
...
Failure to sanitize user input before processing or storing it can result in injection attacks.
Rule | Severity | Likelihood | Remediation Cost | Priority | Level |
|---|---|---|---|---|---|
IDS00-J | High |
Likely | Medium |
P18 | L1 |
Automated Detection
| Tool | Version | Checker | Description |
|---|
FB.SECURITY.SQL_NONCONSTANT_STRING_PASSED_TO_EXECUTE
Nonconstant string passed to execute method on an SQL statement
| The Checker Framework |
| Tainting Checker | Trust and security errors (see Chapter 8) | ||||||
| CodeSonar |
| JAVA.IO.INJ.SQL | SQL Injection (Java) | ||||||
| Coverity | 7.5 | SQLI | Implemented | ||||||
| Findbugs | 1.0 | SQL_NONCONSTANT_STRING_PASSED_TO_EXECUTE | Implemented | ||||||
| Fortify | 1.0 | HTTP_Response_Splitting | Implemented | ||||||
| Klocwork |
| SV.DATA.DB |
SV.SQL
SQL |
SQL.DBSOURCE |
| Implemented |
| Parasoft Jtest |
| CERT.IDS00.TDSQL | Protect against SQL injection | |||||||
| SonarQube |
|
|
| |||||||||
| SpotBugs |
| SQL_NONCONSTANT_STRING_PASSED_TO_EXECUTE | 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
| SEI CERT Perl Coding Standard | IDS33-PL. Sanitize untrusted data passed across a trust boundary |
Injection [RST] | |
CWE-116, Improper Encoding or Escaping of Output |
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
A Guide to Building Secure Web Applications and Web Services | |
| [Seacord 2015] | |
[W3C 2008] | Section 4.4.3, "Included If Validating" |
...