# Escape User Inputs in SQL Queries

# Properties

Property Value
Rule ID EscapeUserInputsInSQLQueries
First seen in jSparrow version 3.17.0
Minimum Java version 1.1
Remediation cost 5 min
Links Prepared Statements (with Parameterized Queries)

# Description

This rule detects potential user inputs that are concatenated with Oracle SQL queries and wraps them in ESAPI.encoder().encodeForSql(codec, input). 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.

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

Automatic Application of This Rule

The automatic application of this rule is supported in the following jSparrow version:

# Tags

1
default
You & jSparrow
default

Hey there! May I help you? 😊