Re: Understanding injection attacks
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.
--
Lew