Thread: Invalid SQL still executes valid sub transactions in Prepared Statement

Invalid SQL still executes valid sub transactions in Prepared Statement

From
"Tom Hargrave"
Date:
Details: 

If a piece of SQL is executed in a JDBC prepared statement that
includes a 
semicolon and a valid piece of SQL, then the embedded valid piece of
SQL 
still executes even though the overall statement is invalid. 

Example: 

select c1 from t1 order by;drop t2; c1

This causes security issues if the SQL is constructed from a web page
that 
inputs strings that are used to construct a statement, since a hacker
can 
embed SQL within a single field that executes regardless of the overall

statement being invalid. 

See article:

http://www.computerweekly.com/articles/article.asp?liArticleID=127470&liFlavourID=1


**************************************************************************************************
CONFIDENTIAL AND PRIVILEGED INFORMATION

IMPORTANT: This message is intended for the addressee only and is privileged and 
confidential.  If you are not the addressee, then please DO NOT read, copy or 
distribute it, but reply to the sender that you received it in error and delete it.  Thank 
you.

Fisher Scientific U.K., Limited.

Registered Office:
Bishop Meadow Road,
Loughborough LE11 5RG
England

Registered in England No: 2883961





Re: Invalid SQL still executes valid sub transactions in Prepared Statement

From
Paul Thomas
Date:
On 16/01/2004 14:04 Tom Hargrave wrote:
> Details:
>
> If a piece of SQL is executed in a JDBC prepared statement that
> includes a
> semicolon and a valid piece of SQL, then the embedded valid piece of
> SQL
> still executes even though the overall statement is invalid.
>
> Example:
>
> select c1 from t1 order by;drop t2; c1
>
> This causes security issues if the SQL is constructed from a web page
> that
> inputs strings that are used to construct a statement, since a hacker
> can
> embed SQL within a single field that executes regardless of the overall
>
> statement being invalid.

Use java.sql.PreparedStatement instead of java.sql.Statement. The driver
will safely escape the user-entered string so that SQL Injection cannot
take place. Look through the archives for list (IRC last summer-ish). ISTR
we had some discussion on SQL Injection and some patches to the driver
were submitted.

>
> See article:
>
> http://www.computerweekly.com/articles/article.asp?liArticleID=127470&liFlavourID=1

There are undoubtably better resources on the net regarding this subject
and how to avoid it as well as best-practice web application design.


--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants         |
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+