# 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.
Use a Java Refactoring Tool
No license required
You can review this refactoring on your code without a license by installing jSparrow to your Eclipse IDE. Install the plug-in from Eclipse IDE: Eclipse Marketplace.
System-wide Refactoring
Do you want to automate this refactoring (and many more) to your system-wide code? The automatic application of this system-wide refactoring can be unlocked by acquiring your jSparrow license.
# Properties
| Property | Value |
|---|---|
| Rule ID | EscapeUserInputsInSQLQueries |
| First seen in jSparrow version | 3.17.0 |
| Minimum Java version | 1.1 |
| Remediation cost | 5 min |
| Links |