SQL Injection with JDBC

[Lothar Kimmeringer]

A prepared statement is by definition not dynamic SQL.

No, but the result of dynamic SQL can be executed by
a Prepared Statement

(except in the very special cases where you build up
dynamically a where with parameters)

There is nothing special about it. In general you create
the statement using one or more StringBuffers (one for the
where-clause, one for the list of selected tables, other
for more stuff like grouping etc.) and a list of parameters.

At the end you create a PreparedStatement, set the sql-statement
as you built it up with your StringBuffer(s) and set the
parameters you find in the list.

Nothing special and nothing unusual in the "real world". The
interesting part is finding a way this process always ends up
with a statement that performs best on the target database.

How would you do it (I think I asked it already)?


Regards, Lothar
--
Lothar Kimmeringer E-Mail: (e-mail address removed)
PGP-encrypted mails preferred (Key-ID: 0x8BC3CD81)

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

 

[Arne Vajhøj]

No, but the result of dynamic SQL can be executed by
a Prepared Statement


There is nothing special about it. In general you create
the statement using one or more StringBuffers (one for the
where-clause, one for the list of selected tables, other
for more stuff like grouping etc.) and a list of parameters.

At the end you create a PreparedStatement, set the sql-statement
as you built it up with your StringBuffer(s) and set the
parameters you find in the list.

No. That is not a very common scenario.

Most database frameworks cache PreparedStatements with
fixed SQL instead of building the SQL dynamicly.

Arne

 

[Lothar Kimmeringer]

Try:

http://www.google.com/search?hl=en&q=define:dynamic+sql&btnG=Google+Search&aq=f&oq=

That doesn't explain how Google implements the extended search.
But just as side-note:

| SQL statements constructed at runtime and passed to the
| database management system for execution.

How do you think will the generated statement be executed
when talking about Java? A Prepared Statement maybe?


Regards, Lothar
--
Lothar Kimmeringer E-Mail: (e-mail address removed)
PGP-encrypted mails preferred (Key-ID: 0x8BC3CD81)

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

 

[Lothar Kimmeringer]

No. That is not a very common scenario.

Just because it doesn't happen in your world very often, doesn't
mean that it doesn't happen anywhere or that it's uncommon. Again,
how do you implement a search as it is provided at e.g.
http://www.google.com/advanced_search?hl=en

Most database frameworks cache PreparedStatements with
fixed SQL instead of building the SQL dynamicly.

When talking about a database framework in Java, the most common
is JDBC which is just a bunch of interfaces and a Factory-class,
so what is happening there is completely up to the implementor
of the JDBC-driver of a database.


Regards, Lothar
--
Lothar Kimmeringer E-Mail: (e-mail address removed)
PGP-encrypted mails preferred (Key-ID: 0x8BC3CD81)

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

 

[Arne Vajhøj]

That doesn't explain how Google implements the extended search.

Google is neither coded in Java or using a relational database.

But let us assume that they were.

Then there are 3 possibilities:
- dynamic SQL
- multiple fixed SQL
- single SQL using the coalesce trick

The last two are always secure against SQL injection.

The first requires either careful input validation or the
combination of PreparedStatement with dynamic SQL.

The second would perform much better with most databases.

| SQL statements constructed at runtime and passed to the
| database management system for execution.

How do you think will the generated statement be executed
when talking about Java? A Prepared Statement maybe?

Most likely not. And that is when the problem arise.

Arne

 

[Arne Vajhøj]

Just because it doesn't happen in your world very often, doesn't
mean that it doesn't happen anywhere or that it's uncommon.

No.

But you can either take my word for it or do some research
on it.

> Again,
how do you implement a search as it is provided at e.g.
http://www.google.com/advanced_search?hl=en

Answered in another reply.

When talking about a database framework in Java, the most common
is JDBC which is just a bunch of interfaces and a Factory-class,
so what is happening there is completely up to the implementor
of the JDBC-driver of a database.

If you read database framework in what I wrote as being at the
JDBC level, then I have a communication problem.

Arne

 

[Lew]

No.

But you can either take my word for it or do some research
on it.


Answered in another reply.


If you read database framework in what I wrote as being at the
JDBC level, then I have a communication problem.

Arne's experience jives with mine. None of the examples I've read, neither in
tutorials nor from actual systems, nor real-world code in dozens of Java
projects on which I've worked, build up PreparedStatements with dynamic SQL.
They all relied on fixed SQL statements with parameters for the run-time part.
At most, they may have included clauses dynamically (e.g., "AND t.foo=?"),
but in my own experience that type of code is very messy to maintain, and one
winds up selecting entire query strings based on user input, not building the
strings up piecemeal.

I also concur with the definition of "database framework" not meaning JDBC as
such, but the code that rests atop the JDBC layer, i.e., the project-specific
middleware.

It seems that you two are talking at cross purposes using different
definitions. Perhaps by avoiding terms with controversial definitions we can
achieve a common understanding.

The thing that Arne (and I) say "should not be used in real-world projects" is
non-parametrized, non-prepared statements where the SQL string is built up
entirely in text then executed as such. This is subject to things like
SQL-injection attacks and wrongly-typed values. The thing that we recommend
is the use of PreparedStatement to embed type-safe parameters into SQL
statements that are not subject to such flaws. PreparedStatement is perfectly
capable of handling the kind of "googley" queries mentioned without the
dangers of non-parametrized queries.

I venture to say that the referenced Google link is very unlikely to have
dynamic SQL (as the term is defined upthread and used by Arne), but depends
entirely on parametrized queries with fixed (i.e., hard-coded) strings for the
non-parameter parts of the query. Given the level of expertise at Google (for
example, Joshua Bloch) that guides their development practices, it would be
shocking if they did not use PreparedStatements with parameters for the user
inputs.

 

[Arved Sandstrom]

On Thu, 25 Dec 2008 15:34:42 -0500, Lew wrote:

[ SNIP ]

There is nothing wrong with using PreparedStatements in Java code, and
there are a number of arguments against using stored procedures for
business logic.
Data logic belongs in the database, but not business logic. Building
queries in Java is more suitable when there are a number of apps using
the same database but with different query needs.

I agree with you, and this guy (http://www.codeproject.com/KB/
architecture/
DudeWheresMyBusinessLogic.aspx#_topicpageref_WhatisBusinessLogic) agrees
with both of us.

AHS

 

[Arne Vajhøj]

I venture to say that the referenced Google link is very unlikely to
have dynamic SQL (as the term is defined upthread and used by Arne), but
depends entirely on parametrized queries with fixed (i.e., hard-coded)
strings for the non-parameter parts of the query. Given the level of
expertise at Google (for example, Joshua Bloch) that guides their
development practices, it would be shocking if they did not use
PreparedStatements with parameters for the user inputs.

If they used a relational database as data storage and used
Java as programming language, then I am also sure they would.

Arne

 

[Arne Vajhøj]

the best way is NOT do do this from java [sic], but instead put the
SQL IN THE DATABASE, where it belongs.

That is not a universally-accepted principle.

I completely agree.

4. you can re-use the functions etc, either in java [sic], pl/sql, or
any other language you care to mention, so when you stop using java
[sic] you have WAY less work to do.

A substantial system written in Java is very unlikely to be rewritten in
a different language.

It could happen.

But there is really not much point in the argument.

If you write anything in language X and you want to change language,
then the code need to be rewritten.

It does not matter if it is Java or SQL or anything else.

So it can not be used as an argument for picking or not
picking a language.

(SQL is a standard, but stored procedures are not portable)

Arne

 

[Arne Vajhøj]

the best way is NOT do do this from java, but instead put the SQL IN THE
DATABASE, where it belongs.
then call out of java to a function , that returns the data.

The advantage is :

1.that you can update the code in the database, without having to roll out
new applications

You update the code in the database instead of the code in the app.

That means higher test costs.

I don't see that as an advantage.

2. It is WAY more secure.

That depends on the database.

3. you pass in values, not SQL.

The questions of dynamic SQL versus prepared statement and
SQL versus stored procedures are independent of each other.

You can make dynamic SQL open for SQL injectiosn with SP's as
well.

4. you can re-use the functions etc, either in java, pl/sql, or any other
language you care to mention,

It can sometimes be an advantage.

so when you stop using java you have WAY less
work to do.

And when you switch database you have WAY more work to do.

That is not an advantage.

5. The Oracle optimiser can pin in the frequently needed routines and
precompile for speed.

Oracle actually do a very nice work with PreparedStatement as well !

public static boolean AddResultReport(String supplierkey, String
indexkey,
String reptype, String releasename) {
boolean result = false; // set it to bad result

// this adds a header record into the database so we can release/fail
goods
// it DOES NOT COMMIT, so we can roll back later.
OracleCallableStatement cstmt = null;
ResultSet rset = null;
String The_qry =
"{call qa.add_result_report(??,?,?)}";

try {
cstmt = (OracleCallableStatement) dbconn.prepareCall(The_qry);

Upcast to an Oracle specific class.

Argh.

cstmt.setString(1, supplierkey);

//
cstmt.setString(2, indexkey);
cstmt.setString(3, reptype);
cstmt.setString(4, releasename);
cstmt.execute();

result = true;

// must be good no error
} catch (Exception e) {

Catch of Exception.

Hmm.

result = false;

Error_stuff.handleError(e, Error_stuff.EXEPTION_ERROR, -1);
} finally {
closeports(cstmt, rset);
}

This is WAY MORE SECURE!!!!, since you do not have to allow the user the
ability to execute raw SQL, but rather execute certain restricted packages.
Then you set a users rights to the packages which are black boxes,

True. But you are completely dependent on Oracle then.

> it makes
> injection very difficult, since you are using bind variables.

As described above that is completely unrelated.

Arne

 

[~Glynne]

 (e-mail address removed) wrote:

[...]

select * from some_table where some_column = 'xxx';delete from
test_sql_injection;--'

Which results in  "java.sql.SQLException: ORA-00911: invalid
character"

I think it's complaining about the ';'. I recall it's easy to overlook
if one is accustomed to PL/SQL & SQL*Plus.

[...]

So how is it possible to demonstrate SQL injection attack using above
query?

You could try wrapping your test in a PL/SQL block, which permits
semicolons. I found this article helpful:

<http://www.net-security.org/dl/articles/IntegrigyIntrotoSQLInjectionAtta
cks.pdf>

Now I'll probably get blasted for this suggestion but if you're
looking
for a simple/mechanical way to refactor a large codebase you could
try
the following.

Replace quotes in the user strings with a harmless character,
e.g. a backtick or double quote. Then slap quotes around all values,
even the numeric ones. A simple utility method might look like:

public static String requote( String str )
{
char q="'", b="`";
return q + str.replace(q[0],b[0]) + q;
}


For example
sql1 = "subexpression1 " + name + " subexpression2";
sql2 = "subexpression3 " + age + " subexpression4";

become
sql1 = "subexpression1 " + requote(name) + " subexpression2";
sql2 = "subexpression3 " + requote(age) + " subexpression4";



Quoting of numeric values isn't strictly kosher, but is supported by
most popular databases, e.g. Oracle and MySQL. With SQL Server you
can enclose all values in square brackets instead of quotes.

~Glynne

 

[Arne Vajhøj]

Now I'll probably get blasted for this suggestion but if you're
looking
for a simple/mechanical way to refactor a large codebase you could
try
the following.

Replace quotes in the user strings with a harmless character,
e.g. a backtick or double quote. Then slap quotes around all values,
even the numeric ones. A simple utility method might look like:

public static String requote( String str )
{
char q="'", b="`";
return q + str.replace(q[0],b[0]) + q;
}


For example
sql1 = "subexpression1 " + name + " subexpression2";
sql2 = "subexpression3 " + age + " subexpression4";

become
sql1 = "subexpression1 " + requote(name) + " subexpression2";
sql2 = "subexpression3 " + requote(age) + " subexpression4";

Quoting of numeric values isn't strictly kosher, but is supported by
most popular databases, e.g. Oracle and MySQL. With SQL Server you
can enclose all values in square brackets instead of quotes.

Using something that is database specific in JDBC is not good (if it can
be avoided).

Making different user input identical in the database could also
create some confusion.

And some database will need other characters to be changes as well.
\ in MySQL is one example.

So rewrite to prepared statement still looks better to me.

Arne

 

[sameergn]

<6832b781-4a90-4a1d-a547-837c25c7b...@q30g2000prq.googlegroups.com>,
(e-mail address removed) wrote:
<[email protected]>,
(e-mail address removed) wrote:
[...]
String query = "select * from some_table where some_column = '"
+ some_value + "'";
Thanks John. Apart from PL/SQLblock, do you think there is a way
to injectSQLcode in the query as shown in the example.
Well, some_value = "xxx' union select <conformal_list_o_goodies> from
secret where <a_goody> !='" gives me the willies. Cf. jolz' nearby
proposal.
We have lots of such queries in the code and we are evaluating if we
need to convert them to use prepared statements.
I would say "all" is a good approximation.

Removing semicolon from my original example (select * from some_table
where some_column = 'xxx' or 1 = 1;--') made the query return all
data, as expected, so I guess semicolon is not required at all, the
comment '--' characters does the job of keeping the extra single quote
away.

Yes, but "'1' = '1" would do as well, using the final single quote. The
point is that attackers are motivated, creative and persistent.

Now for the "delete from" stuff, I guess a semicolon is a must,
so I am assuming it is not possible to attack this way, since
this is not a PL/SQL block and just a plain select statement.

I believe this is a false conclusion. Other databases and other drivers
may ignore the semicolon. Moreover, just because we don't see an obvious
way to inject a DELETE doesn't mean it's not possible. If data can be
used as code, injection is possible and a PreparedStatement with bound
variables is warranted.

My conclusion was solely related to the JDBC Statement example that I
quoted in my original post.
I did not mean to generalize that "DELETE from" attack is impossible.
We will go with a prepared statement route anyway, but before that,
just wanted to know how vulnerable we currently are. In our case
specifically, I hope that we are just open to "1 = 1" kind of attack,
which would reveal unauthorized data to attacker and not vulnerable to
a "delete from table" kind of injection.

Thanks to all who provided their valuable input.

 

[John B. Matthews]

[...]

Now for the "delete from" stuff, I guess a semicolon is a must,
so I am assuming it is not possible to attack this way, since
this is not a PL/SQL block and just a plain select statement.

I believe this is a false conclusion. Other databases and other
drivers may ignore the semicolon. Moreover, just because we don't
see an obvious way to inject a DELETE doesn't mean it's not
possible. If data can be used as code, injection is possible and a
PreparedStatement with bound variables is warranted.

My conclusion was solely related to the JDBC Statement example that I
quoted in my original post. I did not mean to generalize that "DELETE
from" attack is impossible. We will go with a prepared statement
route anyway, but before that, just wanted to know how vulnerable we
currently are. In our case specifically, I hope that we are just open
to "1 = 1" kind of attack, which would reveal unauthorized data to
attacker and not vulnerable to a "delete from table" kind of
injection.

Not meaning to be obdurate, I note that arbitrary "delete from" can
cause a crippling denial of service, but backups and transaction logs
can mitigate the damage. In contrast, unauthorized data can be sold,
distributed, and used in further exploits. The last is tantamount to
"security through obscurity" and not so easy to detect or mitigate. Too
often, the burden falls on the victim.

Consider whether you would be comfortable using the system. If this is a
deployed system with known vulnerabilities, you may have an ethical
obligation to bring it to the attention of your company's principals.

[...]

 

[steve]

the best way is NOT do do this from java [sic], but instead put the SQL IN
THE
DATABASE, where it belongs.

That is not a universally-accepted principle.

Neither is taking a holiday on a Sunday.
If the business logic is not in the database then data integrity cannot be
protected, but instead is an the mercy of every noob with a spreadsheet
application and an ODBC driver + pw.

then call out of java [sic] to a function , that returns the data.

The advantage is :

1.that you can update the code in the database, without having to roll out
new applications

Valid for data routines, not true for business logic.

Sorry that is not correct.

Certain aspects of it may be somewhat more secure, but there is plenty of
security in the Java-coded way, too, if done correctly.


How is that an advantage?

if you don't know you should not be replying, but for you , "Sql injection"
which wash the whole point of the discussion., by passing in SQL the data
stream can be intercepted and the SQL statement can be modified.
By passing in values or references the SQL is still completely under the
control of the database.

4. you can re-use the functions etc, either in java [sic], pl/sql, or any
other
language you care to mention, so when you stop using java [sic] you have
WAY less
work to do.

Disadvantage: you have centralized all your business logic in the database,
and must grow that single point of access as new logic accretes in the system.

not a disadvantage, it brings central control to the data repository.
Anyone who thinks they can control the integrity of the database via a
2nd or 3rd tier application is dreaming, when you consider a database can be
accessed by any number of tools NOT related to your beloved application.

Putting the logic in middleware supports scalability and flexibility of
adding
or modifying features.

so does the database, but with added control over the flexibility, once you
take "processing bandwidth" for critical functionality and palm it off on
clients, you open up a path for subversion of the system.

A substantial system written in Java is very unlikely to be rewritten in a
different language.

Sorry that is complete nonsense, it is already happening, please refer to the
web for a significant number of companies that offer this as a service.
A computer language is generally NOT for the life of any business.

If it were, translation of SQL statements would be very little work in
proportion, since the SQL would not change.

not correct, we are talking about dynamic SQL, and potential injection.

Shouting and excessive use of exclamation points do not substantiate an
argument.

There is nothing wrong with using PreparedStatements in Java code, and there
are a number of arguments against using stored procedures for business logic.

Data logic belongs in the database, but not business logic. Building
queries in Java is more suitable when there are a number of apps using the
same database but with different query needs.

did I say there was anything wrong with prepared statements?, they have their
place , indeed does not the example code use a prepared statement?

You indeed attempt to make some points about " substantiate an argument"
But you do not actually do that, instead you just repeat hearsay and spew
verbage without either example code or references to your points.
Just because some other people stick business logic in the other tiers,
does not make it database law.

Please substantiate your comments with example code , using sql and a
prepared statement, in line with the posters question ,and we will see if
your code can be subverted,

 

[Lew]

the best way is NOT do do this from java [sic], but instead put the SQL IN
THE
DATABASE, where it belongs.

That is not a universally-accepted principle.

Neither is taking a holiday on a Sunday.
If the business logic is not in the database then data integrity cannot be
protected, but instead is an the mercy of every noob with a spreadsheet
application and an ODBC driver + pw.

Huh?

You seem to be confusing business logic with data integrity logic.

then call out of java [sic] to a function , that returns the data.

The advantage is :

1.that you can update the code in the database, without having to roll out
new applications

Valid for data routines, not true for business logic.

Sorry that is not correct.

Sorry, that is correct.

if you don't know you should not be replying, but for you , "Sql injection"

Nice snideness, but off point. I see that I left out an important part of the
question, which I *should* have phrased, "How is that an advantage of putting
business logic in the database instead of the middleware?"

which wash the whole point of the discussion., by passing in SQL the data
stream can be intercepted and the SQL statement can be modified.
By passing in values or references the SQL is still completely under the
control of the database.

Which has nothing to do with putting business logic in stored procedures. You
pass in values, not SQL, when the business logic is in the middleware also.
Thus, how is it an advantage to putting the business logic in the database
instead of the middleware?

4. you can re-use the functions etc, either in java [sic], pl/sql, or any
other
language you care to mention, so when you stop using java [sic] you have
WAY less
work to do.

Disadvantage: you have centralized all your business logic in the database,
and must grow that single point of access as new logic accretes in the

system.
not a disadvantage, it brings central control to the data repository.
Anyone who thinks they can control the integrity of the database via a
2nd or 3rd tier application is dreaming, when you consider a database can be
accessed by any number of tools NOT related to your beloved application.

Putting the logic in middleware supports scalability and flexibility of
adding
or modifying features.

so does the database, but with added control over the flexibility, once you

Less control over the flexibility, and less scalability of the business logic.

With business logic in the middleware, you can write separate routines on
separate hosts to spread the load, which is more flexible and scalable than
putting in the single point of the database.

take "processing bandwidth" for critical functionality and palm it off on
clients, you open up a path for subversion of the system.

Not if you do it right.

Sorry that is complete nonsense, it is already happening, please refer to the
web for a significant number of companies that offer this as a service.
A computer language is generally NOT for the life of any business.


not correct, we are talking about dynamic SQL, and potential injection.

No, I am not. Therefore "we" are not.

did I say there was anything wrong with prepared statements?, they have their
place , indeed does not the example code use a prepared statement?

You indeed attempt to make some points about " substantiate an argument"
But you do not actually do that, instead you just repeat hearsay and spew
verbage without either example code or references to your points.
Just because some other people stick business logic in the other tiers,
does not make it database law.

Others have cited the sources, I merely summarize them. I added some
substantiating arguments in this post.

Please substantiate your comments with example code , using sql and a
prepared statement, in line with the posters question ,and we will see if
your code can be subverted,

Arved already provided a link several days ago,
<http://www.codeproject.com/KB/architecture/DudeWheresMyBusinessLogic.aspx>
that gives a sufficiently cogent case that I feel no need to reiterate it.

How about you substantiate how one subverts a prepared statement?

Remember that I am only talking about business logic going in the middleware,
not data integrity logic.

 

[Arne Vajhøj]

the best way is NOT do do this from java [sic], but instead put the SQL IN
THE
DATABASE, where it belongs.

That is not a universally-accepted principle.

Neither is taking a holiday on a Sunday.

True.

But the fact that other claims than yours are wrong does not
make your claim true.

If the business logic is not in the database then data integrity cannot be
protected,

Since business logic and data integrity are two different things, then
that does not make much sense.

if you don't know you should not be replying, but for you , "Sql injection"
which wash the whole point of the discussion., by passing in SQL the data
stream can be intercepted and the SQL statement can be modified.

Since PreparedStatement solve that as well, then SQL injection is not
a valid argument for using SP's.

4. you can re-use the functions etc, either in java [sic], pl/sql, or any
other
language you care to mention, so when you stop using java [sic] you have
WAY less
work to do.

Disadvantage: you have centralized all your business logic in the database,
and must grow that single point of access as new logic accretes in they system.

not a disadvantage, it brings central control to the data repository.
Anyone who thinks they can control the integrity of the database via a
2nd or 3rd tier application is dreaming, when you consider a database can be
accessed by any number of tools NOT related to your beloved application.

data integrity != business logic

And just as logic in app does not cover other apps then logic
in a databse does not cover other databases.

You are not solving a problem you are just moving a problem.

not correct, we are talking about dynamic SQL, and potential injection.

No. Lew is most certainly talking about PreparedStatement.

did I say there was anything wrong with prepared statements?, they have their
place , indeed does not the example code use a prepared statement?

Actually a CallableStatement which is a subinterface of
PreparedStatement.

You indeed attempt to make some points about " substantiate an argument"
But you do not actually do that, instead you just repeat hearsay and spew
verbage without either example code or references to your points.

Code examples are not particular relevant in a discussion about where
business logic belongs.

The only thing your code examples showed was that you have a very bad
Java coding style.

Please substantiate your comments with example code , using sql and a
prepared statement, in line with the posters question ,and we will see if
your code can be subverted,

Everyone knows that PreparedStatement is not vulnerable to SQL
injection, so there is no point.

Arne