Re: Prepared Statements

Barry Lind wrote:

> If using a PreparedStatement the driver correctly escapes all values
> to avoid SQL injection attacks.

No, it doesn't :-)
For example:

PreparedStatement s = c.prepareStatement ("select * from user where id =
?");
s.setObject (1, "null;drop database mydatabase", Types.INTEGER);
System.out.println (s.toString ());

select * from user where id=null;drop database mydb

:-)

Dima

> While this can also be done when using a regular Statement object, it
> is then the resposibility of the programmer to a) remember they need
> to escape, b) know specificially how postgresql needs things escaped,
> and c) to actually escape all user input. Invariably this will be
> forgotten some of the time and therefore I would always recommend
> using PreparedStatements when you don't have control over the values
> that are being used in the SQL statements.
>
> thanks,
> --Barry
>
>
> wsheldah(at)lexmark(dot)com wrote:
>
>> I have to disagree; SQL injection can happen just from input
>> parameters, as
>> described. The only thing left out was the quotes. If you construct the
>> query as:
>> String query = "SELECT * from address_book WHERE name = '" + userInput +
>> "'";
>>
>> Then the user needs to change his input to: "joe'; delete from
>> address_book; '"
>>
>> I don't know about the JDBC driver, but perl's DBI driver would
>> handle the
>> above IF it were a parameterized query by escaping all quotes in the
>> user's
>> input. So if instead of constructing it by hand, you had the "WHERE name
>> = ?" form and the user passed in the above, postgresql would see:
>>
>> SELECT * from address_book WHERE name = 'joe''; delete from
>> address_book;
>> ''
>>
>> (I'm assuming postgresql escapes quotes by doubling them, I don't recall
>> for sure.)
>> Hopefully the JDBC driver will do this as well. If not, then all user
>> input
>> needs to be scanned for quotes, semicolons, etc., so they can be
>> properly
>> escaped to avoid SQL injection attacks. Incidentally, such attacks
>> might be
>> a second select query instead of deleting records, so as to get info
>> on all
>> users in the database instead of just themselves for instance. In
>> that case
>> it would be much less obvious that an attack had occurred.
>>
>> Wes Sheldahl
>>
>>
>>
>> Dmitry Tkach <dmitry(at)openratings(dot)com>@postgresql.org on 07/17/2003
>> 10:47:49
>> AM
>>
>> Sent by: pgsql-jdbc-owner(at)postgresql(dot)org
>>
>>
>> To: Paul Thomas <paul(at)tmsl(dot)demon(dot)co(dot)uk>
>> cc: "pgsql-jdbc @ postgresql . org" <pgsql-jdbc(at)postgresql(dot)org>
>> Subject: Re: [JDBC] Prepared Statements
>>
>>
>>
>>
>>> This is a security hole known as SQL injection.
>>
>>
>>
>> No, it isn't :-)
>> The "hole" you are referring to is letting the users type in entire
>> queries, not just input parameters.
>> As long as you have control over how your sql is constructed, you not
>> any less (nor any more) safe with plain Statements than you would be
>> with PreparedStatements. The do the same exact thing.
>>
>> Dima
>>
>>
>>> If you are using a normal Statement then your users can probably
>>> delete whole tables from the database but with a PreparedStatement you
>>> would write
>>>
>>> String query = "SELECT * from address_book WHERE name = ?"
>>>
>>> and the command actually passed over to the database would be
>>>
>>> SELECT * from address_book WHERE name = 'joe;delete from address_book'
>>>
>>> I'm sure you can see the difference. Maybe PreparedStatements will
>>> have a performance gain in some future release but at the moment they
>>> have a vital role to play in database security.
>>>
>>
>>
>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 1: subscribe and unsubscribe commands go to
>> majordomo(at)postgresql(dot)org
>>
>>
>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>>
>
>