java.sql.PreparedStatement

Sanitizing user input is a means to secure an application. The JDBC™ standard however provides a mechanism being superior regarding the purpose of protecting applications against SQL injection attacks. We shed some light on our current mechanism sending SQL statements to a database server:

Figure 889. SQL statements in Java™ applications get parsed at the database server Slide presentation
SQL statements in Java™ applications get parsed at the database server

Figure 890. Two questions Slide presentation

  1. What happens when executing thousands of SQL statements having identical structure?

  2. Is this architecture adequate with respect to security concerns?


Figure 891. Addressing performance Slide presentation 
INSERT INTO Person VALUES ('Jim', 'jim@q.org')
INSERT INTO Person VALUES ('Eve', 'eve@y.org')
INSERT INTO Person VALUES ('Pete', 'p@rr.com')
...

Wasting time parsing SQL over and over again!


Figure 892. Addressing performance mitigation Slide presentation 
INSERT INTO Person VALUES
  ('Jim', 'jim@q.org'),
  ('Eve', 'eve@y.org'),
  ('Pete', 'p@rr.com') ... ;

Dealing with large record counts even this option may become questionable.


Figure 893. Restating the SQL injection problem Slide presentation

The database server's interpreter may interpret an attacker's malicious code among with intended SQL.

  • User input is being interpreted by the database server's interpreter.

  • User input filtering my be incomplete / tedious.


Figure 894. Solution: Use java.sql.PreparedStatement Slide presentation

  • User input being excluded from parsing.

  • Allows for reuse per record.


Figure 895. PreparedStatement principle. Slide presentation
PreparedStatement principle.

Prepared statements are an example for parameterized SQL statements which do exist in various programming languages. When using java.sql.PreparedStatement instances we actually have three distinct phases:

Figure 896. Three phases using parameterized queries Slide presentation

  1. PreparedStatement instance creation: Parsing SQL statement possibly containing place holders.

  2. Set values of all placeholder values: SQL values are not being parsed.

  3. Execute the statement.

Steps 2. and 3. may be repeated without re-parsing the underlying SQL statement thereby saving database server resources.


Our introductory toy application Figure 859, “JDBC™ backed data insert ” may be rewritten using PreparedStatement objects:

Figure 897. PreparedStatement example Slide presentation 
final Connection conn = DriverManager.getConnection (...

final PreparedStatement pStmt = conn.prepareStatement(
  "INSERT INTO Person VALUES(?, ?)");❶

pStmt.setString(1, "Jim");❷
pStmt.setString(2, "jim@foo.org");❸

final int updateCount = pStmt.executeUpdate();❹

System.out.println("Successfully inserted " + updateCount + " dataset(s)");

❶

An instance of java.sql.PreparedStatement is being created. Notice the two question marks representing two place holders for string values to be inserted in the next step.

❷ ❸

Fill in the two placeholder values being defined at ❶.

Caution

Since half the world of programming folks will index a list of n elements starting from 0 to n-1, JDBC™ apparently counts from 1 to n. Working with JDBC™ would have been too easy otherwise!

❹

Execute the beast! Notice the empty parameter list. No SQL is required since we already prepared it in ❶.

Figure 898. Injection attempt example Slide presentation 
Jim', 'jim@c.com');DROP TABLE Person;INSERT INTO Person VALUES('Joe

Attacker's injection text simply becomes part of the database server's content.

Problem solved!


Figure 899. Limitation: No dynamic table support! Slide presentation 
  • SELECT birthday from Persons
  • PreparedSatatement statement =
  connection.prepareStatement("SELECT ? ❶ from ?" ❷);
statement.setString(1, "birthday") ❸;
statement.setString(2, "Persons") ❹;
ResultSet rs = statement.executeQuery() ❺;

In a nutshell: Only attribute value literals may be parameterized.


❶

Providing an attributes name as parameter.

❷

Providing the table name to be queried as parameter.

❸

Setting the desired attributes name intending:

SELECT birthday FROM ...

❹

Setting the table name to be queried intending:

SELECT birthday FROM Persons

❺

Fails: Only attribute value literals are allowed.

exercise No. 10

Prepared Statements to keep the barbarians at the gate

Q:

Use PreparedStatement objects to sanitize your flawed Interactive inserts, connection properties, error handling and unit tests implementation being susceptible to SQL injection attacks.

When you are done repeat your injection attempt from Attack from the dark side . You may require larger string lengths in your SQL schema for accommodating the injection string.

A:

See:
Prev  Up  Next
Sanitizing user input  Home  Read Access