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 867. SQL statements in Java applications get parsed at the database server Slide presentation Create comment in forum
SQL statements in Java™ applications get parsed at the database server

Figure 868. Two questions Slide presentation Create comment in forum
  1. What happens when executing structural identical SQL statements (differing only by attribute values) repeatedly?

    E.g. inserting thousands of records of identical structure.

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


Figure 869. Addressing performance Slide presentation Create comment in forum
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 870. Addressing performance mitigation Slide presentation Create comment in forum
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 871. Addressing security Slide presentation Create comment in forum

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


Figure 872. Solution: Use java.sql.PreparedStatement Slide presentation Create comment in forum
  • Parsing happens only once.

  • Reuse per record.

  • Avoids parsing contained «payload» / user input.


Figure 873. PreparedStatement principle. Slide presentation Create comment in forum
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 874. Three phases using parameterized queries Slide presentation Create comment in forum
  1. PreparedStatement instance creation: Parsing SQL statement possibly containing place holders.

  2. Set values of all placeholder values: No SQL parsing happens.

  3. Execute the statement.

Steps 2. and 3. may be repeated without requiring re-parsing SQL statements thus saving database server resources.


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

Figure 875. PreparedStatement example Slide presentation Create comment in forum
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 876. Injection attempt example Slide presentation Create comment in forum
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 877. No dynamic table support Slide presentation Create comment in forum
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. 32

Prepared Statements to keep the barbarians at the gate Create comment in forum

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: