On 08/03/2010 06:19 AM, Simon Brooke wrote:
I know that, from Java, the best method of protecting against SQL
injection attacks is to use prepared statements. I'm looking at a
situation in which the user may optionally pass a very large number of
parameters, so that the number of prepared statements I need would be
n^2 where n is the number of optional params. This becomes a bit
awkward ;-)
So I'm considering building up a query in a string buffer, with clauses
like the following:
String author = context.getValueAsString( Book.AUTHORFN );
if ( author != null ) {
/* do something to sanitise the input */ query.append( " and
author like '%" ).append( author ).append( "%'" );
}
where the passable parameters are integers I'm parsing the integer
value from the user's input using Integer.parseInt(String), and
appending the resultant integer (if any) to the buffer; so that should
be fairly robust. The problem is in string fields, especially as I
intend that users should normally be able to use substring matches.
I know that, whenever I encounter a single quote character (') in the
user's input, I need to emit two successive single quote characters
('').
But do all SQL injection attacks depend simply on this trick, or are
there other tricks I need to defend against? In the particular
instance, the database is Postgres 8, but I'd like to have a general
understanding and a general solution.
Why not just build up a PreparedStatement and avoid reinventing the
wheel? It's slightly more effort than building up a dynamic SQL query
the way you show, but not by much, and it buys you all the benefits of
PreparedStatement's run-time type safety and protection from SQL
injection.
I use a helper object similar to
/** package-private */
class PrepParam <T>
{
T value;
PrepParam( T val )
{
this.value = val;
}
void setParam( PreparedStatement ps, int pos ) {
ps.setObject( pos, value );
}
}
and appropriate specializations
class PrepParamLong extends PrepParam <Long>
{
void setParam( PreparedStatement ps, int pos ) {
ps.setLong( pos, value );
}
}
or
PrepParam <Timestamp> tsPrepper = new PrepParam <Timestamp> ()
{
void setParam( PreparedStatement ps, int pos ) {
ps.setYimestamp( pos, value );
}
};
There are variations possible - you could store the pos and ps as
instance variables, for example.
Then your buildup becomes:
List <PrepParam <?>> params = new ArrayList <PrepParam <?>> ();
StringBuilder query =
new StringBuilder( "SELECT * FROM foo WHERE TRUE" );
...
if ( author != null )
{
query.append( " AND author LIKE ?" ); params.add( new
PrepParamString( "%"+ author +"%" ));
}
...
PreparedStatement ps = cxn.prepareStatement( query.toString() ); int
ix = 0;
for ( PrepParam <?> pp : params )
{
pp.setParam( ps, ix++ );
}
assert ix == pp.size();
ps.executeQuery();
That "if ( author != null )" bit raises a signal in my brain that
polymorphism might be useful there, too.
the more I like it. Yes, I think that's a plan.