Practical Security: Simple Practices for Defending Your Systems/

...

/

Extending the Defense Beyond Prepared Statements

Extending the Defense Beyond Prepared Statements

In this lesson, we will look at the Defense against SQL injections.

We'll cover the following...

Limitations of prepared statements #

Prepared statements are great because they’re nearly bulletproof. The downside is that not every part of a SQL statement can be parameterized. Table names, for instance, cannot be parameterized. There’s no way to write a prepared statement like this:

Press + to interact
public PreparedStatement journalEntrySearch(
      Connection con,
      String tableName,
      int personId,
      String wildcard) {
    String sql = "SELECT CreatedTimestamp, Body from ? WHERE PersonId = ? AND Body LIKE ?";
    PreparedStatement search = con.PrepareStatement(sql);
    search.setString(1, tableName);
    search.setInt(2, personId);
    search.setString(3, "%" + wildcard + "%");
    return search;
}

In our journal-keeping example, parameterizing the table name might sound a little silly. There are cases, however, where this level of flexibility would be useful. Suppose our journaling website takes off and we add support for blog posts, mass emails, and on-demand printing of birthday cards. We may find ourselves duplicating the search logic across tables for journal entries, blog posts, mass emails, and birthday cards. (Yes, there ...