Invariant Properties

What is SQL Injection?

SQL Injection is the ability of attackers to insert arbitrary SQL commands into your system.

Sample attack

Look at the following code:

ResultSet rs = stmt.execute(
   "select * from users where username='" + username +
   "' and password='" + password + "'");

What could go wrong? Let’s say we use the following values:

String username = "bob' or 1=1; --";
String password = "dont care";

When we call the earlier code the generated code is

select * from users where username='bob' or 1=1; --'
   and password='dont care'

This will list all users. Some web frameworks will list all users in the system. More carefully written applications will raise an alarm if more than one record is returned. This is easy to fix:

String username = "bob' or 1=1 order by userid limit 1; --";
String password = "dont care";

to produce

select * from users where username='bob' or 1=1
  order by userid limit 1; --' and password='dont care'

The ‘order by’ stanza ensures we see the first user in the system. That’s normally the administrator – something attackers do not forget.

Wrong Approach

Many inexperienced programmers attempt to get around this problem by explicitly sanitizing the user-provided input.

ResultSet rs = stmt.execute(
  "select * from users where username='" +
  username.replaceAll("'",   "''") +
  "' and password='" + password.replaceAll("'", "''") + "'");

This might have worked in the 1980s but the world uses more than ASCII today. Properly identifying quote characters is a non-trivial problem and should be left to others. The JDBC writers often have database-specific methods for this but they can get out of sync with the database and are, of course, database-specific.

Prepared Statements and Placeholders

The standard solution to this problem is to use prepared statements and placeholders. This replaces the code

ResultSet rs = stmt.execute(
  "select * from users where username='" +
  username + "' and password='" + password + "'");

with

PreparedStatment stmt = conn.prepareStatement(
  "select * from users where username=? and password=? limit 1");
stmt.setString(1, username);
stmt.setString(2, password);
ResultSet rs = stmt.execute()

Limitations

There are times when prepared statements are inappropriate. One common example is multi-insert statements. These can be significantly faster than multiple prepared statement calls.

An example of a multi-insert statement is

insert into squares(x, y)
   values (1, 1),
          (2, 4),
          (3, 9),
          (4, 16),
          (5, 25);

As a general rule this should not be used with user-provided data. If it is absolutely required use the database-specific method provided by your JDBC provider, not a roll your own solution.

Cost/Benefit Analysis

The cost/benefit analysis to using prepared statement placeholders is irrelevant – it’s one of those things that you simply have to do.