How to protect against SQL injection when a WHERE clause is dynamically created from a search form?

Question

I know that the only really correct way to protect SQL queries from SQL injection in Java is to use PreparedStatements.

However, such a statement requires that the basic structure (selected attributes, joined tables, WHERE condition structure) does not change.

I have a JSP application containing a search form with about a dozen fields. But the user does not need to fill all of them - only the one that he needs. So my WHERE condition is different every time.

What should I do to still prevent SQL injection?
Avoid user-defined values? Write a wrapper class that creates a PreparedStatement each time? Or something else?

In a PostgreSQL 8.4 database, but I would prefer a general solution.

Thank you very much in advance.

Answer 1

Have you seen the JDBC NamedParameterJDBCTemplate ?

The NamedParameterJdbcTemplate class adds support for JDBC programming using named parameters (as opposed to programming JDBC statements using only the classic placeholder ('?') Arguments.

You can do things like:

String sql = "select count(0) from T_ACTOR where first_name = :first_name";
SqlParameterSource namedParameters = new MapSqlParameterSource("first_name", firstName);
return namedParameterJdbcTemplate.queryForInt(sql, namedParameters);

and build the query string dynamically, and then create your own in the SqlParameterSourcesame way.

Answer 2

, , , , , — , where , .

, , SQL-, PreparedStatement . in PreparedStatement (, where fieldName in (?, ?, ?), , ? ). , ( ).

, :

// You'd have just the one instance of this map somewhere:
Map<String,String> fieldNameToColumnName = new HashMap<String,String>();
// You'd actually load these from configuration somewhere rather than hard-coding them
fieldNameToColumnName.put("title", "TITLE");
fieldNameToColumnName.put("firstname", "FNAME");
fieldNameToColumnName.put("lastname", "LNAME");
// ...etc.

// Then in a class somewhere that used by the JSP, have the code that
// processes requests from users:
public AppropriateResultBean[] doSearch(Map<String,String> parameters)
throws SQLException, IllegalArgumentException
{
    StringBuilder           sql;
    String                  columnName;
    List<String>            paramValues;
    AppropriateResultBean[] rv;

    // Start the SQL statement; again you'd probably load the prefix SQL
    // from configuration somewhere rather than hard-coding it here.
    sql = new StringBuilder(2000);
    sql.append("select appropriate,fields from mytable where ");

    // Loop through the given parameters.
    // This loop assumes you don't need to preserve some sort of order
    // in the params, but is easily adjusted if you do.
    paramValues = new ArrayList<String>(parameters.size());
    for (Map.Entry<String,String> entry : parameters.entrySet())
    {
        // Only process fields that aren't blank.
        if (entry.getValue().length() > 0)
        {
            // Get the DB column name that corresponds to this form
            // field name.
            columnName = fieldNameToColumnName.get(entry.getKey());
                      // ^-- You'll probably need to prefix this with something, it not likely to be part of this instance
            if (columnName == null)
            {
                // Somehow, the user got an unknown field into the request
                // and that got past the code calling us (perhaps the code
                // calling us just used `request.getParameterMap` directly).
                // We don't allow unknown fields.
                throw new IllegalArgumentException(/* ... */);
            }
            if (paramValues.size() > 0)
            {
                sql.append("and ");
            }
            sql.append(columnName);
            sql.append(" = ? ");
            paramValues.add(entry.getValue());
        }
    }

    // I'll assume no parameters is an invalid case, but you can adjust the
    // below if that not correct.
    if (paramValues.size() == 0)
    {
        // My read of the problem being solved suggests this is not an
        // exceptional condition (users frequently forget to fill things
        // in), and so I'd use a flag value (null) for this case. But you
        // might go with an exception (you'd know best), either way.
        rv = null;
    }
    else
    {
        // Do the DB work (below)
        rv = this.buildBeansFor(sql.toString(), paramValues);
    }

    // Done
    return rv;
}

private AppropriateResultBean[] buildBeansFor(
    String sql,
    List<String> paramValues
)
throws SQLException
{
    PreparedStatement       ps      = null;
    Connection              con     = null;
    int                     index;
    AppropriateResultBean[] rv;

    assert sql != null && sql.length() > 0);
    assert paramValues != null && paramValues.size() > 0;

    try
    {
        // Get a connection
        con = /* ...however you get connections, whether it JNDI or some conn pool or ... */;

        // Prepare the statement
        ps = con.prepareStatement(sql);

        // Fill in the values
        index = 0;
        for (String value : paramValues)
        {
            ps.setString(++index, value);
        }

        // Execute the query
        rs = ps.executeQuery();

        /* ...loop through results, creating AppropriateResultBean instances
         * and filling in your array/list/whatever...
         */
        rv = /* ...convert the result to what we'll return */;

        // Close the DB resources (you probably have utility code for this)
        rs.close();
        rs = null;
        ps.close();
        ps = null;
        con.close(); // ...assuming pool overrides `close` and expects it to mean "release back to pool", most good pools do
        con = null;

        // Done
        return rv;
    }
    finally
    {
        /* If `rs`, `ps`, or `con` is !null, we're processing an exception.
         * Clean up the DB resources *without* allowing any exception to be
         * thrown, as we don't want to hide the original exception.
         */
    }
}

, , (, ), , SQL, , PreparedStatement.

Answer 3

, JSP .

, . JSP , ; ​​ .

Answer 4

, quote(), PHP PDO. .

, , .

Answer 5

, WHERE, , .

?

select id, title, author from book where title = :title

?

select id, title, author from book where author = :author

?

select id, title, author from book where title = :title and author = :author

. (, , PreparedStatements) . , , PreparedStatement Java, if . , .

, SELECT, , .

, PreparedStatement, , NamedParameterJdbcTemplate , re Spring.

:

select id, title, author
from book
where coalesce(:title, title) = title
and coalesce(:author, author) = author

NULL . coalesce() - , . , NULL :title, where coalesce(NULL, title) = title, where title = title, , , .

, , . , , .

( , IN (?, ?, ?), , , / .)