Understanding injection attacks

#1

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.

--

;; Semper in faecibus sumus, sole profundam variat

#2

On 08/03/2010 06:19 AM, Simon Brooke wrote:

Quote:

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

#3

On Tue, 03 Aug 2010 09:02:00 -0400, Lew wrote:

Quote:

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.

When I first looked at your answer I thought 'that's crap, he hasn't
answered the questions at all...' but the more I look at your solution,
the more I like it. Yes, I think that's a plan.

However, I'd still out of academic interest like to know the answer to
the question I asked, which is, are SQL injection attacks possible which
do not depend on unescaped single quote characters?

--

;; Semper in faecibus sumus, sole profundam variat

#4

Simon Brooke wrote:

Quote:

On Tue, 03 Aug 2010 09:02:00 -0400, Lew wrote:

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.

When I first looked at your answer I thought 'that's crap, he hasn't
answered the questions at all...' but the more I look at your solution,
the more I like it. Yes, I think that's a plan.

However, I'd still out of academic interest like to know the answer to
the question I asked, which is, are SQL injection attacks possible which
do not depend on unescaped single quote characters?

Yes. Google is your friend.

#5

Simon Brooke wrote:

Quote:

When I first looked at your answer I thought 'that's crap, he hasn't
answered the questions at all...' but the more I look at your solution,
the more I like it. Yes, I think that's a plan.

You'll get over that reaction as you get used to my posts.

Quote:

However, I'd still out of academic interest like to know the answer to
the question I asked, which is, are SQL injection attacks possible which
do not depend on unescaped single quote characters?

Is it possible to make cabinets without using a saw? Maybe, but why would you
want to? Does that make a saw the only tool you need? Certainly not.

Apostrophes are one tool in the injection attacker's kit. So is the
"double-dash" sequence "--". You almost certainly would not use single-quote
characters if injecting into a non-string value. You almost certainly would
not use single-quotes without other tricks alongside. For example, an
attacker pretty much certainly would include additional SQL to hack at the DB,
e.g., convert

"SELECT auth FROM people WHERE handle = '" + name +"'"
is hacked by a value of 'name' equal to
"'; INSERT INTO people (handle, auth) VALUES ('foo', 'xkcd.com/327'); --"

SQL injection attacks are usually incremental - the attacker uses the error
messages from each step to inform the next step.

<http://xkcd.com/327/>

--
Lew

#6

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:

Quote:

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.

#7

For completeness, in dynamic languages like Perl you need also to filter
out interpolation characters, like $, @, % etc etc...
In Perl you should do something like:

$tainted_string=~ s/['\$@%]//g;

no worry for languages with no string interpolation like Java

Il 04/08/2010 15.14, Anselmo Canfora ha scritto:

Quote:

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.

#8

Anselmo Canfora <nob... (AT) nowhere (DOT) net> wrote:

Quote:

For completeness, in dynamic languages like Perl you need also to filter
out interpolation characters, like $, @, % etc etc...

Please do not top-post, and please do trim posts for readability.

A: Trim posts and insert your responses at the appropriate points in
the conversational flow.
Q: What should I do instead?
A: It makes the conversational flow harder to follow.
Q: Why is it bad?
A: Positioning your response to quoted material prior to (or "on top
of") the quoted material.
Q: What is top-posting?

--
Lew

#9

Simon Brooke wrote:

Quote:

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 ;-)

Nothing you can't solve with a StringBuffer and an ArrayList with the
parameters. BTDT (daily). PreparedStatements have another big advantage.
Many JDBC-drivers need to parse a statement before sending it to the
DB. Also the DB needs to go over it with the Query Optimizer to build
the optimal execution path. Many DBMS keep that result in a cache
to reuse it if the statement comes along another time. So a Prepared
Statement with the same query but different values can be reused while
a normal statement can't.

Quote:

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( "%'" );
}

query.append(" and author like ?")
parameters.add(new MyParameterInfo(MyParameterInfo.TYPE_STRING,
"%" + author + "%");

You still have injection issues here, the user can enter % and _
for himself but that only affects the results not the statement
itself.

Quote:

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.

The gerneral solution is PreparedStatement, building your own
solution for this is like programming your own cryptography
library because you don't like the logo on BouncyCastle's website.

Regards, Lothar
--
Lothar Kimmeringer E-Mail: spamfang (AT) kimmeringer (DOT) de
PGP-encrypted mails preferred (Key-ID: 0x8BC3CD81)

Always remember: The answer is forty-two, there can only be wrong
questions!

#10

Simon Brooke wrote:

Quote:

However, I'd still out of academic interest like to know the answer to
the question I asked, which is, are SQL injection attacks possible which
do not depend on unescaped single quote characters?

It depends on the implementation of the JDBC-driver and of the
database in question. So you can't have a definitive answer, since
both things can change over time. 10 years ago I told everybody
that viruses can't infect your PC by just reading an email since
it's just plain old text that is shown. Then there came Outlook
Express and HTML-mails...

To answer your question: Ask in a different group covering security
topics where it's more likely to meet with people with this specific
kind of interest. Or start researching by using your favorite
search engine. "How do SQL injections" should be a good start.

BTW: SQL-injections even work with NoSQL ;-)
http://www.kalzumeus.com/2010/09/22/...aspora-launch/

Regards, Lothar
--
Lothar Kimmeringer E-Mail: spamfang (AT) kimmeringer (DOT) de
PGP-encrypted mails preferred (Key-ID: 0x8BC3CD81)

Always remember: The answer is forty-two, there can only be wrong
questions!