SQL Injection with JDBC

[sameergn]

Hi,

We are trying to inject SQL code into an oracle 10g query. (Apache +
Tomcat Setup)

Statement stmt = conn.createStatement();
String query = "select * from some_table where some_column = '" +
some_value + "'";
rs = stmt.executeQuery(query);

where some_value is user input.

if some_value is set to "xxx';delete from test_sql_injection;--", the
query becomes

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

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

Same error is thrown when the value is set to "xxx' or 1 = 1;--'"
which results in following query.

select * from some_table where some_column = 'xxx' or 1 = 1;--'

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

Thanks,
Sameer

 

[John B. Matthews]

(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>

 

[sameergn]

(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>

Thanks John.
Apart from PL/SQL block, do you think there is a way to inject SQL
code in the query as shown in the example. We have lots of such
queries in the code and we are evaluating if we need to convert them
to use prepared statements.

 

[jolz]

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

query?

some_value = "' OR '' = '" and user may gain acces to information that
shouldn't ever see

 

[John B. Matthews]

(e-mail address removed) wrote:

[...]

String query = "select * from some_table where some_column = '"
+ some_value + "'";

Thanks John. Apart from PL/SQL block, do you think there is a way to
inject SQL code in the query as shown in the example.

Well, some_value = "xxx' union select <conformal_list_o_goodies> from

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.

 

[sameergn]

<[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

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.

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.

 

[Lew]

I would say "all" is a good approximation.

In addition to the improved security, PreparedStatement gives you a
kind of run-time type safety. You can set dates to Date values,
timestamps to Timestamp values, numbers to numeric values, etc., and
the method call to set each parameter takes only the appropriate type
for the column.

And PreparedStatement can even give a degree of performance
improvement under the right circumstances.

 

[John B. Matthews]

<[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

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.

I understand there is resistance to a wholesale rewrite, but I don't
know any other way to foreclose that avenue of attack. It doesn't matter
how many doors are locked; if one door is unlocked, the building is open.

 

[Arne Vajhøj]

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.[/QUOTE]

Dynamic SQL should be practically non existing in any
real world app.

Arne

 

[steve]

(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>

Thanks John.
Apart from PL/SQL block, do you think there is a way to inject SQL
code in the query as shown in the example. We have lots of such
queries in the code and we are evaluating if we need to convert them
to use prepared statements.

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
2. It is WAY more secure.
3. you pass in values, not SQL.
4. you can re-use the functions etc, either in java, pl/sql, or any other
language you care to mention, so when you stop using java you have WAY less
work to do.
5. The Oracle optimiser can pin in the frequently needed routines and
precompile for speed.


A working example:

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

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) {
result = false;

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

another working example.....

public static boolean TagSupplier(String supplierkey, int flagpos,
int state) {
boolean result = false;

// set it to bad result
OracleCallableStatement cstmt = null;
ResultSet rset = null;
String The_qry = "{call
misc.mod_lock_flags(?,?,?)}";

// "misc.mod_lock_flags"
try {
cstmt = (OracleCallableStatement) dbconn.prepareCall(The_qry);

cstmt.setString(1, supplierkey);

//
cstmt.setInt(2, flagpos);
cstmt.setInt(3, state);
cstmt.execute();

result = true;

// must be good no error
} catch (Exception e) {
Error_stuff.handleError(e, Error_stuff.EXEPTION_ERROR, -1);
} finally {
closeports(cstmt, rset);
}

return result;
}


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, it makes
injection very difficult, since you are using bind variables.

Steve

 

[Lothar Kimmeringer]

I would say "all" is a good approximation.

Dynamic SQL should be practically non existing in any
real world app.[/QUOTE]

First of all we are not talking about dynamic SQL here but
SQL injection that is described e.g. at
http://en.wikipedia.org/wiki/SQL_Injection

If you mean by real "I never used that in the past", you might
be right. But every website offering you extended search-
capabilities proves you wrong for the real world as "we" under-
stand it.

Or how would you implement e.g. the Google Search as you can
see it at http://www.google.de/advanced_search?hl=en without
the use of dynamic SQL?


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]

sorry that is complete nonsense.....

I don't think so.

if you do not have 'dynamic sql' , how will you return quiries that are not
identical.

for example , if you need to query on a key, then the sql must be dynamic,
albiet with bind variables, if not then you would always return the "same "
query

No. Prepared statements were invented to handle this without
dynamic SQL.

Arne

 

[Arne Vajhøj]

First of all we are not talking about dynamic SQL here but
SQL injection that is described e.g. at
http://en.wikipedia.org/wiki/SQL_Injection

If you mean by real "I never used that in the past", you might
be right.

Not only then.

But every website offering you extended search-
capabilities proves you wrong for the real world as "we" under-
stand it.

Or how would you implement e.g. the Google Search as you can
see it at http://www.google.de/advanced_search?hl=en without
the use of dynamic SQL?

I would expect those sites to use prepared statement not
dynamic SQL.

(prepared staemenet in Java and PHP, parameters in MS technology)

Arne

 

[Lew]

Not only then.


I would expect those sites to use prepared statement not
dynamic SQL.

(prepared staemenet in Java and PHP, parameters in MS technology)

It seems that more than one person is misinterpreting what Arne is referring
to with the term "dynamic SQL". Dynamic SQL refers to run-time substitution
of the characters that make up a SQL query:

Dynamic SQL statements are stored as strings of characters
that are entered when the program runs.

<http://searchoracle.techtarget.com/sDefinition/0,,sid41_gci927933,00.html>

This is not the same as parametrized SQL, a.k.a. prepared statements, whereby
question-mark (or similar) parameters obtain type-safe values at run time.

 

[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.

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.

2. It is WAY more secure.

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

3. you pass in values, not SQL.

How is that an advantage?

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.

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

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

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

5. The Oracle optimiser can pin in the frequently needed routines and
precompile for speed. ....
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, it makes
injection very difficult, since you are using bind variables.

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.

 

[Lothar Kimmeringer]

I would expect those sites to use prepared statement not
dynamic SQL.

I would expect dynamic SQL being executed as Prepared Statement,
when being implemented in Java.


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. Prepared statements were invented to handle this without
dynamic SQL.

No, Prepared Statements cope with the parametrization, not the
statement as such. Or do you have other methods than setSql
in a PreparedStatement I'm not aware of?

How do you realize a search as it can be seen at Google that
I linked in my other post?


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]

I would expect dynamic SQL being executed as Prepared Statement,
when being implemented in Java.

A prepared statement is by definition not dynamic SQL.

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

Arne

 

[Arne Vajhøj]

No, Prepared Statements cope with the parametrization, not the
statement as such. Or do you have other methods than setSql
in a PreparedStatement I'm not aware of?

PreparedStatement handle that type of query without dynamic SQL.

How do you realize a search as it can be seen at Google that
I linked in my other post?

With a PreparedStatement.

Arne

 

[Arne Vajhøj]

Or how would you implement e.g. the Google Search as you can
see it at http://www.google.de/advanced_search?hl=en without
the use of dynamic SQL?

Try:

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

Arne