how to code to avoid SQL insertion attacks

[Lee Fesperman]

I can't point to a specific section of an ANSI specification. In fact,
if you know of a place where the ANSI SQL92 or SQL99 specs are publicly
available, that would be interesting. My understanding is that ANSI
holds the copyright and does not permit distribution.

It is very unfortunate that the specs are not available free. You can buy them, though,
from ANSI or an authorized reproducer. I bought my copy of SQL92 from Global Engineering
Documents.

I know it like the back of my hand and can say with confidence that that construct is
not included in SQL92.

I am instead making reference to Martin Gruber's "SQL Instant
Reference" and to observed behavior from PostgreSQL, which tends to be
at least approaching compliant with SQL92 (unlike some other free
databases... .

I know Martin very well (he dedicated his first book, "Understanding SQL", to me.)

I'd be disappointed if PostgreSQL supported it, because it is useless or at best
redundant ... IS [NOT] NULL was in SQL86. Perhaps it has something to do with
PostgreSQL's pre-1986 origins.

I'm curious, though. You seem to be implying that there is a difference
between "? = NULL" and "? = b" where b is NULL. Is there some reason
that the literal NULL is not allowed there? Was it disallowed
intentionally to prevent mistakes in SQL by people who don't understand
tri-value logic (and if so, doesn't it just invite subtler and more
difficult bugs?)

If ? = NULL were supported, then there should be no difference, and it would always
yield UNKOWN for compatibility with ? = b where b is NULL. That makes it completely
useless. So, I was guessing that its meaning could be like ? IS NULL.

I believe standard SQL disallows it to avoid misunderstandings, as related to 3VL. There
is the standard construct instead. As to subtle and deep bugs associated with 3VL and
NULLs, this is a really big topic. I participated in the "Great NULLs Debate" and have a
number of articles at firstsql.com. I come down on the side with Codd vs. Date, Pascal,
McGoveran, et al. I've debated it with all those guys (still do). However, I'd be the
first to say it's a very complex issue.

 

[steve]

I don't understand your concerns. In your code the actual sql is hardcoded in
the Java code. The only thing that can be injected into that Sql is the value
used for rep_index. I can't see a way to inject real SQL "code" in there,
because the PreparedStatement will not accept it.


None of those examples will work or return anything from the database. I
actually doubt if they would be exectued at all. AFAIK the ? paramters are
only
allowed for values not for columns (which you are doing).

My understanding is, that "SQL injection" means that the user can enter values
in a front end that will eventually end up being used as parameters in a SQL
statement.

Are you implying that your SQL strings would be entered by the user? Taking
your
initial example into account, I would only think the user would enter the
value
of the indexkey parameter.



Could you explain this attack in more detail? Should the indexkey contain a
single quote or a double quote? In either case this would either stop the SQL
from working (if rep_index was a numeric field) - depending on the DBMS used
or
simply return rows where the rep_index is equal to a single quote (or double
quote) depending on what your example was.


That is another topic and is - in my opinion - not related to SQL injection.
But
this depends largely on the architecture beeing used e.g. a web application
where the appliation server is doing the connection and not the user itself
could well use a thin JDBC connection (assuming application server and
database
server are well secured from the outside)


Thomas

no the discussion started with a guy asking about stopping sql insertion
attacks. the only info he gave was that he was using oracle.


the start of the replies were actually quite funny, then we go onto the fact
that using "bind variables" and prepared statements , would solve any
problems with sql insertion attackes.

this can be very easily disproved, with a small amount of work.

the above merely points out a number of ways to attack bind variables.
for the above sql it is complete crap, I was actually just having a bit of
fun, to sort the men out from the boys.

steve

 

[steve]

Well, then you should be pretty "f**&k" ashamed about giving a
piss poor reply yourself.


Does not work. Also, even if this example was valid, if code like
this is used then no insertion attack is needed. All the attacker
needs to do is to know the right column name. Insertion attack
means that you insert something that alters the logic in the SQL
statement.

For example if the SQL statement is created by catenating strings
and the statement is:

"DELETE FROM table WHERE col = '" + value + "'"

an insertion attack would be inserting the value

"0' OR col <> '0"

thus producing a statement:



Does not work.


Using PreparedStatement solves this. Characters that have a
special meaning in SQL are encoded before they are inserted
into a prepared statement. So this is not a working attack.


Incorrect. None of your examples work.


Practise what you preach.

it is obvious that the sql is not correct, you do not need to be so
verbose.

 

[steve]

Stored procedures have absolutely nothing to do with SQL injection
attacks, except for the incidental detail that in JDBC,
CallableStatement is a subtype of PreparedStatement and implements the
same parameterized query mechanism. If you try to call a stored
procedure without CallableStatement or PreparedStatement -- something
that's quite possible in most databases -- then you are just as
vulnerable to SQL injection as someone executing SQL select statements.
wrong

On the other hand, you could certainly stand to learn something about
JDBC and prepared statements. One of the fundamental concepts of
prepared statements is that you are responsible only for providing the
data, and the database driver guarantees that it will be passed properly
to the DBMS by including it in SQL. How that happens is implementation-
specific, but one of several possible techniques is to do quoting of
special characters, making SQL injection attacks null and void. Another
possibility is to actually send the data in separate binary streams
according to a vendor-specific wire protocol for statement parameters,
which is practically speaking even more effective at solving SQL
injection, since it leaves less room for driver bugs to open security
holes.

an sql insertion attack, in not always related to "special characters"

Running SQL code as unprivileged accounts is also an effective layered
security technique to provide extra protection against injection
attacks. However, it makes for a very poor primary solution; and your
muddying the waters with all that nonsense about stored procedures
creates just enough confusion for someone to open up a security hole by
accident. Bad job.

I'll leave you to read other replies on why your attacks against a
system using prepared statements will fail. Lee and Antii are exactly
right on this matter. One nit, though:


Technically, the two prepared statements above are actually valid.
(However, in ANSI SQL they will always return zero rows because
comparisons with NULL yield NULL rather than true or false.) However,
you can't plug in a column name there; only a literal value. This is,
of course, incredibly useless, so your queries are quite valid but still
unlikely to be correct.


You're right; you can't. That's because you're essentially using a
prepared statement there. The unprivileged user helps as well.
However, the lame attempt at obfuscation for security purposes convinces
me of two things:

lame ,but effective, and that is the whole point of the discussion.

the guy did not ask , what is the best way to write clear & effective code,
but was asking a hackers question.

to be able to hinder a hacker , you think like a hacker, whilst this may not
produce ., ideal code you have to offset it against the risk.
since he did not specify the risk.

1. You're never coming near a database schema I have to work on if I
have any say whatsoever on the matter.

K , give me an ip address of a server you maintain.

2. You are obviously not qualified to be making security decisions in
any kind of critical environment.

Nevertheless, notice that if you write (fixing your naming convention
issues):

String theQuery = "{call external_user.fgfsgfddfg.asa("
+ firstField + "," + secondField ... + ")}";

Then you would be using the same stored procedure but be quite
vulnerable to an SQL injection attack, leaving only your half-way
solution with unprivileged accounts to prevent a complete compromise of
the system.

you are obviously not aware of the purpose of an "sql injection attack"
but just to put you straight.
It is not to hack an SQL string.

it is to GAIN INFORMATION from the database that would not normally be
available.

your child like , attempt to prove a point, I.E that you can concat. a few
string.
Does not accomplish the main purpose of an "sql injection attack".

the above would not gain any information form the database, since you need to
know the inner workings of the routine.

and that was the whole point, you are not seeing the BIG picture.

Are you starting to understand yet? Your code was secure because you
used a parameterized query, NOT because you used a stored procedure. If
you had used a parameterized query without a stored procedure, it would
have been equally secure. If you had used a stored procedure without a
parameterized query, it would have been broken. Stored procedures don't
prevent you from writing secure code, but they are completely irrelevant
to whether you write secure code or not.

well with that sort of nonsense, i'm just glad you are not a D.B.A in
charge of any system I'm working on.
( notice i used capitals so you don't feel unimportant)

READ THE THREAD.

 

[steve]

My backend DBMS is Oracle.

I was actually, going to keep the thread running until April 1st, but that
would be unkind, too many heart attacks.

I wanted to see how far some people would go to argue with a complete tosser,
using "dubious" facts.


The facts are (what i consider to be true):

1. you can recover a password , from the oracle sql drivers.
(fire up a debugger), see what is actually held inside an oracle connection
object. ( why DO they store the password unencrypted, i would do a screen
snapshot, but i would have to setup a dummy account)

not sure about the latest 10 g, ( ocrs12/ojdbc14)but done :

classes111.zip
classes12.zip


2. the purpose of an injection attack is not to string useless sql together,
but to gain information from a database that would not normally be
available, by "injecting a modified sql string"
how you do that is irrelevant, the goal is to get information.

You can actually do it with a prepared statement, you just have to
invalidate, or "nullify the effect of the '?' passed parameter or
parameter's, i prefer more than 1 as it is easier to hack.


String employee="john"
String Query= "select * from employees where employee='"+employee+''";


vrs:
String Query ="select * from employees where ?= employee"

i suppose you could do:

String Query "select * from employees where ? not in (select * from dual)"

this works:

String Query="select * from export where ? not in ( select * from dual)"
......
whereas before the sql was
String Query="select * from export where ?= current_export_order"
....

if you can make the passed parameter irrelevant to the query, then you gain
access to the data in the table, inso far as the data being made available
to the user you are logged in as.
How you make the passed parameter invalid is a matter up to the hacker.

but be aware that using prepared statements and passes parameter's is not
guaranteed safe.

also note that passing in "null" is not the same as using 'NULL' or 'IS
NULL', but that seems to be changing on a daily basis.

I had to re-write a number of queries, because of 'null' or empty strings.

3. i did have a security tar against the oracle jdbc drivers, and they did
inform me of 2 other attacks.


As for the tosser's who said , " my security implementation is weak ", and "i
should test the code"
Good luck to you, live long and prosper.

I see over 200 attacks a week directly against our oracle database.
and over 1,000 system attacks.
That only depends on if the local Chinese Uni's are having a slow day,
and this is just because they have seen ports 1521 & 1526 open.

 

[Tor Iver Wilhelmsen]

I can't point to a specific section of an ANSI specification. In fact,
if you know of a place where the ANSI SQL92 or SQL99 specs are publicly
available, that would be interesting. My understanding is that ANSI
holds the copyright and does not permit distribution.

Also ISO. However, they are irrelevant, since implementors only
implement a subset and add their proprietary or "industry standard"
(meaning "used by more than one implementor like Transact SQL of
Sybase and Microsoft) extensions anyway.

 

[Tor Iver Wilhelmsen]

this can be very easily disproved, with a small amount of work.

Then disprove it; so far you have failed miserably, with nonsensical
code noone would write and dismissing counterpoints with just the word
"wrong".

the above merely points out a number of ways to attack bind
variables. for the above sql it is complete crap, I was actually
just having a bit of fun, to sort the men out from the boys.

You appear to be a troll with absolutely no knowledge of JDBC.

 

[Tor Iver Wilhelmsen]

wrong

Not wrong.

to be able to hinder a hacker , you think like a hacker, whilst this may not
produce ., ideal code you have to offset it against the risk.
since he did not specify the risk.

But a hacker does not write the SQL - the programmer does. That's the
point of PreparedStatement, to hinder the hacker in writing SQL.

it is to GAIN INFORMATION from the database that would not normally be
available.

And that is not possible since the hacker does not see the SQL.

the above would not gain any information form the database, since
you need to know the inner workings of the routine.

It's needlessly cryptic to the developer.

Again: In modern software systems, the code's SQL statements are NOT
VISIBLE to the user/hacker no matter how they are written.

and that was the whole point, you are not seeing the BIG picture.

We're thankfully not seeing the same picture you are.

READ THE THREAD.

Where everyone except you are making sense.

 

[Tor Iver Wilhelmsen]

1. you can recover a password , from the oracle sql drivers.

The database is kept behind a firewall, the app server connecting to
it will not have publically readable connect information. That is how
software is written.

You can actually do it with a prepared statement, you just have to
invalidate, or "nullify the effect of the '?' passed parameter or
parameter's, i prefer more than 1 as it is easier to hack.

You have failed to demonstrate how that is possible.

String Query ="select * from employees where ?= employee"

That is illegal syntax - why do you persist in thinking you can
parametrize table column names? Is that the straw-based foundation
your entire argument builds on? Parameters are - and can only be -
used for VALUES.

Also, the user of the application does not see that string.

if you can make the passed parameter irrelevant to the query, then
you gain access to the data in the table, inso far as the data being
made available to the user you are logged in as.

Here, you assume the hacker is already on the "inside", why doesn't he
just fire up SQL*Plus and do whatever he wants without resorting to
JDBC?

but be aware that using prepared statements and passes parameter's is not
guaranteed safe.

Yes it is, because you don't know how they work. In the wors case, bad data

I see over 200 attacks a week directly against our oracle database.

How do you detect that they use PreparedStatement?

 

[Tor Iver Wilhelmsen]

Yes it is, because you don't know how they work. In the wors case, bad data

.... leads to an SQLException in the code.

 

[Chris Smith]

an sql insertion attack, in not always related to "special characters"

Huh? Yes, it is... by definition. If you have some other attack in
mind that doesn't interject SQL code written by the attacker into an
otherwise harmless query, then it's not an SQL injection attack. SQL
injection has nothing to do with network snoopers, or debuggers (!) or
anything like that.

[security by obscurity is] lame ,but effective, and that is the whole
point of the discussion.

Okay, you're really a kook. I think it's sufficiently obvious to
everyone that you live in your own little world and shouldn't be trusted
with security advice. I'll leave you alone now.

--
www.designacourse.com
The Easiest Way To Train Anyone... Anywhere.

Chris Smith - Lead Software Developer/Technical Trainer
MindIQ Corporation

 

[Antti S. Brax]

I wanted to see how far some people would go to argue with a complete tosser,
using "dubious" facts.

Now that was stupid. If you were a bit smarter you could have
figured the answer yourself too. To me you are still just as
dumb as you "pretended" to be.

You see, some people are here to help other people. While helping
other people they sometimes learn new things themselves too. That
is why people do it.

This group is read by people who are new in programming. When
incorrect information is posted it harms them because they learn
incorrect information as facts. Later they come back and ask more
questions thus wasting everybody's time.

That is why the nice people here propably would argue it to the
end (or at least until they killfile you).

 

[nuklea]

For a start validate your input field that you will use in the prepared
statement. Do not prepare or excute sql statement solely from an input
field. If you need to do that you must grep for SQL keyword that will
do damage like delete, alter, or drop table in the input field. In some
cases you might just do the preparedStatement of the input and if this
method does not throws exception considered that input is a sql
injection code. Aside from this you proabbly need to limit the role of
the userid that your application use to access the db.

 

[Alan Krueger]

the start of the replies were actually quite funny, then we go onto the fact
that using "bind variables" and prepared statements , would solve any
problems with sql insertion attackes.

this can be very easily disproved, with a small amount of work.

What's stopping you, then?

 

[Owen Jacobson]

I'd be disappointed if PostgreSQL supported [? = NULL], because it is
useless or at best redundant ... IS [NOT] NULL was in SQL86. Perhaps it
has something to do with PostgreSQL's pre-1986 origins.

(Ressurecting a somewhat-dead thread.)

Note: he may not mean the same thing you do. PostgreSQL, at least as of
7.4.6, evaluates 'foo = NULL' to NULL, not TRUE, even if foo is NULL.
This is readily demonstrated from the psql command-line client:

template1=# select (null = null) is null;
?column?
----------
t
(1 row)

Cheers,
Owen

 

[Lee Fesperman]

I'd be disappointed if PostgreSQL supported [? = NULL], because it is
useless or at best redundant ... IS [NOT] NULL was in SQL86. Perhaps it
has something to do with PostgreSQL's pre-1986 origins.

(Ressurecting a somewhat-dead thread.)

Note: he may not mean the same thing you do. PostgreSQL, at least as of
7.4.6, evaluates 'foo = NULL' to NULL, not TRUE, even if foo is NULL.
This is readily demonstrated from the psql command-line client:

template1=# select (null = null) is null;
?column?

As I said, "useless or at best redundant".

Also, read the thread again. I said (foo = NULL) should evaluate to UNKOWN, not NULL.
Thus ((foo = NULL) IS NULL) is really meaningless. The correct (?) construct would be
((foo = NULL) IS UNKNOWN) which is also meaningless (maybe lesser so). Does PostreSQL
really work that way, or are you confused?

Anyway, let's try it all again --- ((foo = bar) IS UNKNOWN) is reasonable and evaluates
to TRUE if foo and/or bar are NULL. All rational and compliant with SQL92 (if you wanna
call SQL rational).

It's been fun playing with you guys ;^) I'm game for keeping it up.

 

[Lee Fesperman]

I'd be disappointed if PostgreSQL supported [? = NULL], because it is
useless or at best redundant ... IS [NOT] NULL was in SQL86. Perhaps it
has something to do with PostgreSQL's pre-1986 origins.

(Ressurecting a somewhat-dead thread.)

Note: he may not mean the same thing you do. PostgreSQL, at least as of
7.4.6, evaluates 'foo = NULL' to NULL, not TRUE, even if foo is NULL.
This is readily demonstrated from the psql command-line client:

template1=# select (null = null) is null;
?column?

BTW, I really would like an answer. Does PostgreSQL really work this way?

I've always been a little dubious about the product. This would confirm that they are
occasionally brain-dead in their implementation.

 

[Owen Jacobson]

I'd be disappointed if PostgreSQL supported [? = NULL], because it is
useless or at best redundant ... IS [NOT] NULL was in SQL86. Perhaps
it has something to do with PostgreSQL's pre-1986 origins.

(Ressurecting a somewhat-dead thread.)

Note: he may not mean the same thing you do. PostgreSQL, at least as of
7.4.6, evaluates 'foo = NULL' to NULL, not TRUE, even if foo is NULL.
This is readily demonstrated from the psql command-line client:

template1=# select (null = null) is null;
?column?

As I said, "useless or at best redundant".

Certainly. However, unless you want to deal with four-value logic (T, F,
NULL, and UNKNOWN), conflating NULL and UNKNOWN works just fine. This is,
in fact, what PostgreSQL does:

"Notice that IS UNKNOWN and IS NOT UNKNOWN are effectively the same as
IS NULL and IS NOT NULL, respectively, except that the input expression
must be of Boolean type."
<http://www.postgresql.org/docs/8.0/interactive/functions-comparison.html>

Now, I'm not sure at all whether you can bind column names into an
expression, which is the other part of the original question.

Also, read the thread again. I said (foo = NULL) should evaluate to
UNKOWN, not NULL. Thus ((foo = NULL) IS NULL) is really meaningless. The
correct (?) construct would be ((foo = NULL) IS UNKNOWN) which is also
meaningless (maybe lesser so). Does PostreSQL really work that way, or are
you confused?

I'm not; I read the manual. PostgreSQL uses strictly three-value
boolean logic; any comparison or operation on NULL returns NULL rather
than TRUE or FALSE (excluding IS NULL and IS NOT NULL).

Anyway, let's try it all again --- ((foo = bar) IS UNKNOWN) is reasonable
and evaluates to TRUE if foo and/or bar are NULL. All rational and
compliant with SQL92 (if you wanna call SQL rational).

Cite? I'd be interested in seeing the justification for using a fourth
logical state for this.

I find this sort of thing much easier to resolve if I keep in mind that
SQL is not and is not intended to be a perfect relational system. It's
"Good Enough", and does express relational concepts very well as far as it
goes, but there are cases like this that are frequently implemented in a
less "pure" way for ease of use.

 

[Lee Fesperman]

Certainly. However, unless you want to deal with four-value logic (T, F,
NULL, and UNKNOWN), conflating NULL and UNKNOWN works just fine. ...

Of course, four-value logic is a complex subject. There are varied definitions of the
4th value. That's certainly not Codd's in RM 2. However, ...

... This is, in fact, what PostgreSQL does:

"Notice that IS UNKNOWN and IS NOT UNKNOWN are effectively the same as
IS NULL and IS NOT NULL, respectively, except that the input expression
must be of Boolean type."
<http://www.postgresql.org/docs/8.0/interactive/functions-comparison.html>

IOW, it's really 3VL with two ways to say IS UNKNOWN. They seem to be combining regular
expressions (arithmetic, string, ...) and logical expressions (predicate). That's their
perogative but contrary to RM and SQL.

It doesn't really change my assertion. ((foo = NULL) IS `whatever`) is still meaningless
or redundant.

Now, I'm not sure at all whether you can bind column names into an
expression, which is the other part of the original question.

Fine, we can skip that issue. We're on to other concepts anyway.

I'm not; I read the manual. PostgreSQL uses strictly three-value
boolean logic; any comparison or operation on NULL returns NULL rather
than TRUE or FALSE (excluding IS NULL and IS NOT NULL).

Ok, I see now, based on your earlier citation from the PostgreSQL docs.

Cite? I'd be interested in seeing the justification for using a fourth
logical state for this.

It isn't a fourth logical state since the standard doesn't define NULL as a logical
state. Citation:

<<<
SQL92 Standard: 8.1 <predicate>

Specify a condition that can be evaluated to give a truth value of TRUE, FALSE or
UNKNOWN.
Unfortunately, I can't give a link because the standard is not online. I can give a link
to our tutorial -- http://www.firstsql.com/tutor2.htm#where, which is faithful to the
SQL92 Standard (I hope).

I find this sort of thing much easier to resolve if I keep in mind that
SQL is not and is not intended to be a perfect relational system. It's
"Good Enough", and does express relational concepts very well as far as it
goes, but there are cases like this that are frequently implemented in a
less "pure" way for ease of use.

Certainly I agree on the latter counts. I'm not so sure about SQL's intentions but no
matter.

Anyway, thanks for your clarification ... and clear thinking.

 

[Owen Jacobson]

<<<
SQL92 Standard: 8.1 <predicate>

Specify a condition that can be evaluated to give a truth value of TRUE,
FALSE or UNKNOWN.

Whereas both PostgreSQL and MySQL (*shudder*) evaluate predicates to TRUE,
FALSE, or NULL and have no truth value referred to as UNKNOWN. If we
suppose that NULL IS UNKNOWN is true and that UNKNOWN IS NULL is true
(this seems to be the sticking point) then the SQL definition and both of
those DBMS servers are compatible.

Interesting quotes:

PostgreSQL:
"SQL uses a three-valued Boolean logic where the null value represents
"unknown"."
<http://www.postgresql.org/docs/8.0/interactive/functions.html>

MySQL:
"The NULL value means ``no data.''"
<http://dev.mysql.com/doc/mysql/en/null-values.html>

I'll have a look at Oracle and possibly MSSQL at some point. This is
turning out to be more interesting than expected.

Personally, I find PostgreSQL's definition of NULL far more useful,
conceptually; a NULL in a tuple simply means that the corresponding piece
of information is missing or unknown (the value is not NULL, the value is
*not there*), and a NULL result from a comparison means (a) that at least
one value was NULL and (b) that the result of the comparison is likewise
unknown. Perhaps UNKNOWN is a better label for the concept, but NULL is
succinct and very widely understood at this point.

Unfortunately, I can't give a link because the standard is not online. I
can give a link to our tutorial --
http://www.firstsql.com/tutor2.htm#where, which is faithful to the SQL92
Standard (I hope).

Thanks for that; I'll have a look when I have a moment.