Security Code Review 101 — Parameterized Statements

Neutralizing hazardous input to prevent Injection attacks.

This article is part of a series. Read the previous article here: https://medium.com/@paul_io/security-code-review-101-input-validation-f309b1be96c7

Image showing a syringe, a symbol for the Injection attack.

About Injection

In the previous article we reviewed Input Validation. While Input Validation is an effective deterrent to a large number of attacks, including Injection, not all input can be filtered.

For example imagine someone’s name is O’Brien. The single quote in O’Brien happens to also be part of SQL command syntax. For example a website may perform a database record search like this:

Image showing invalid SQL query.

Notice that the single quote in the name O’Brien is causing a syntax error. The SQL command processor considers the string ends with O and the rest, BRIEN%, is just an unrecognized command.

In order to work around this problem one must escape the single quote with a another single quote like in the image below.

Image showing the previous SQL query escaped.

However when this query is executed by a program, things look different. The name would be read from a variable and the database query would be constructed dynamically.

Let’s take a look at the following code snippet.

Code statement showing a SQL statement constructed dynamically.

The variable lastName contains input coming from the user. It is concatenated to a constant SQL query string and the resulting command is passed to the database server.

There is no input validation and no input escaping whatsoever. This means that a user entering O’Brien would cause an SQL syntax error, which is a bug. However a malicious user would take advantage of this behaviour. What would happen if the user entered something like the string below?

‘;DROP TABLE users;

The SQL query being passed to the database would end up being two different commands. One selects all users in the database, while the other deletes the users table.

SELECT * FROM users WHERE last_name = ‘%’; DROP TABLE users;

If these concepts are new to you, now you can finally enjoy this old hacker joke about little Bobby Tables.

https://xkcd.com/327/

Preventing Injection

As the old comic suggests, sanitizing the user input could have prevented the issue. “Sanitizing” could have been done with Input Validation or escaping of the single quote. However not all input can be validated and not all SQL Injection is done with single quotes.

In the example below Injection takes advantage of an un-sanitized ORDER BY parameter:

SELECT * FROM users ORDER BY name; DROP TABLE users;

In this case Input Validation could be employed because column names should be alphanumeric however this leads to a more complex defence strategy where the application must employ Input Validation for values going into the ORDER BY section and Escaping for values going into the WHERE clause.

There is however a simpler way. Do not use concatenation at all. This approach also holds true for other Injection scenarios like Command Injection.

Blackboard displaying the text: “Concatenation causes injection: ‘COMMAND+INPUT = INJECTION’”

This is a scenario where we can employ Occam’s Razor to identify the simplest most universal defence to Injection attacks. The solution here is using Parameterized Statements.

Parameterized Statements interact with the command processor to separate the variables from the SQL query, thus effectively neutralizing characters that may influence the query.

Visual representation of a parameterized statement.

In Java this can be achieved by using a Prepared Statement as per the example below. The question mark in the query string at line 3 is a placeholder for the parameter value.

Example showing the use of PreparedStatements to prevent SQL Injection

When dealing with OS Commands, is best to avoid them completely and write the equivalent functionality in your programming language of choice. For example if you must read a file in Java use APIs such as File and BufferedReader rather than the Linux cat command. However in certain cases there’s no choice and the code must “Shell Out”. For those situations the equivalent of a Prepared Statement is passing command line arguments as a separate array, thus avoiding concatenation.

Example showing a OS Command arguments passed as function parameters.

Object-Relational Mapping (ORM)

There’s an even better way to abstract SQL statements from application code. ORM frameworks allow developers to work with objects rather than SQL queries. Instead of working with the users table developers would work with a users collection and execute a method on that collection to return the corresponding record.

Example of Java code using ORM to interact with the database.

Under the covers the framework would perform a transformation similar to the diagram below.

Transformation performed by a ORM framework.

Caution

There are cases where Injection will still occur in spite of Parameterized Statements being used. For example when the injection occurs in a database stored procedure or the OS shell script being executed

In order to reduce the likelihood of such scenarios occurring, Input Validation should still be used as much as possible.

To sum it all up

When reviewing a code change that involves a command processor look for the following:

• Input Validation for known alphanumeric values
• Employing Parameterized Statements
• Using a ORM framework where applicable

The next article in the series will cover Memory Safe Functions and Safe Memory Management practices.

Click the following link to access the next article in the series: https://medium.com/@paul_io/security-code-review-101-memory-adf0543926ee

The Complete Security Code Review 101 Series

• Input Validation — https://medium.com/@paul_io/security-code-review-101-input-validation-f309b1be96c7
• Parameterized Statements — https://medium.com/@paul_io/security-code-review-101-parameterized-statements-df95c264364a
• Memory Safe Functions and Safe Memory Management practices — https://medium.com/@paul_io/security-code-review-101-memory-adf0543926ee
• Protecting Data — https://medium.com/@paul_io/security-code-review-101-protecting-data-part-1-23e810277f7d and https://medium.com/@paul_io/security-code-review-101-protecting-personal-data-f2e891a6ff13
• Protecting Against Cross-Site Scripting — https://medium.com/@paul_io/security-code-review-101-protecting-against-cross-site-scripting-387fb188aa2e
• Indirect Object Reference — https://medium.com/@paul_io/security-code-review-101-indirect-object-reference-91f3ad0451c5