# Escape User Inputs in SQL Queries
# Description
This rule detects potential user inputs that are concatenated with Oracle SQL queries and wraps them in ESAPI.encoder().encodeForSql(codec, input) (opens new window).
In this way, the contents of the user input will only be considered as values and not as code, thus preventing the SQL Injection vulnerabilities.
A typical example of a malicious user input containing fragments that can change the intent of the SQL query is 1' or '1'='1.
When wrapped by encodeForSql(...), no part of the user input will be considered as code. For more details, see the examples below.
Requirements
Activation of this rule requires the following classpath entries to be present:
- org.owasp.esapi.codecs.OracleCodec
- org.owasp.esapi.codecs.Codec
- org.owasp.esapi.ESAPI
# Benefits
Prevents SQL injections.
# Tags
# Code Changes
# Using Statement execute
Pre
HttpServletRequest req = getRequest();
String query = "SELECT first_name FROM employee WHERE department_id ='" + req.getParameter("departmentId") + "' ORDER BY last_name";
Statement statement = connection.createStatement();
statement.execute(query);
ResultSet resultSet = statement.getResultSet();
Post
HttpServletRequest req = getRequest();
Codec<Character> oracleCodec = new OracleCodec();
String query = "SELECT first_name FROM employee WHERE department_id ='" + ESAPI.encoder().encodeForSQL(oracleCodec, req.getParameter("departmentId")) + "' ORDER BY last_name";
Statement statement = connection.createStatement();
statement.execute(query);
ResultSet resultSet = statement.getResultSet();
# Using Statement executeQuery
Pre
HttpServletRequest req = getRequest();
String query = "SELECT first_name FROM employee WHERE department_id ='" + req.getParameter("departmentId") + "' ORDER BY last_name";
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(query);
Post
HttpServletRequest req = getRequest();
Codec<Character> oracleCodec = new OracleCodec();
String query = "SELECT first_name FROM employee WHERE department_id ='" + ESAPI.encoder().encodeForSQL(oracleCodec, req.getParameter("departmentId")) + "' ORDER BY last_name";
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(query);
# Multiple Concatenation Lines
Pre
HttpServletRequest req = getRequest();
String query = "SELECT first_name FROM employee WHERE";
query += " id > '" + req.getParameter("id") + "'";
query += " AND department_id ='" + req.getParameter("departmentId") + "'";
query += " ORDER BY last_name";
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(query);
Post
HttpServletRequest req = getRequest();
Codec<Character> oracleCodec = new OracleCodec();
String query = "SELECT first_name FROM employee WHERE";
query += " id > '" + ESAPI.encoder().encodeForSQL(oracleCodec, req.getParameter("id")) + "'";
query += " AND department_id ='" + ESAPI.encoder().encodeForSQL(oracleCodec, req.getParameter("departmentId")) + "'";
query += " ORDER BY last_name";
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(query);
# Limitations
This technique of escaping user supplied input is database specific and supports Oracle DBMS.
🛠️ Auto-refactor Available
You can auto refactor this with jSparrow.
Drop this button to your Eclipse IDE workspace to install jSparrow for free:
Need help? Check out our installation guide.
# Properties
| Property | Value |
|---|---|
| Rule ID | EscapeUserInputsInSQLQueries |
| First seen in jSparrow version | 3.17.0 |
| Minimum Java version | 1.1 |
| Remediation cost | 5 min |
| Links |