Re: Understanding injection attacks

From:

Anselmo Canfora <nobody@nowhere.net>

Newsgroups:

comp.databases.postgresql,comp.lang.java.databases

Date:

Wed, 04 Aug 2010 15:14:26 +0200

Message-ID:

<4c5967b5$0$30898$5fc30a8@news.tiscali.it>

Generally speaking, you can avoid attacks surrounding all parameters by
single quotes (in PG it works for all types, not only strings) and by
filtering all single quotes in input parameters. You also need to pay
attention to escape sequences that may be converted into single quotes
by your implementation language, for example the following expressions
will all print a single quote in Perl:

         print "\x27\n";
         print "\047\n";
         print "\x{27}\n";

so in this case (Perl) if you filter single quotes and backslashes you
should be ok. If you surround all inputs by single quotes and nothing
can break the string whatever it contains is harmless. Of course check
also the string length :)

Il 03/08/2010 12.19, Simon Brooke ha scritto:

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.