Search⌘ K
AI Features
Home
Courses
Practical Security: Simple Practices for Defending Your Systems

Extending the Defense Beyond Prepared Statements

Explore advanced SQL defense techniques that go beyond prepared statements to secure your applications. Understand why prepared statements have limitations with elements like table names and discover how to safely handle user inputs by introducing indirection. This lesson guides you through practical methods to prevent SQL injection by mapping user selections to server-side data, making attacks easier to detect and defend against.

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:

Java
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 ...