# 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.

a drawn cute bird pointing at a graph that shows positive results

# Properties

Property Value
Rule ID EscapeUserInputsInSQLQueries
First seen in jSparrow version 3.17.0
Minimum Java version 1.1
Remediation cost 5 min
Links