Re: Prepared Statements

Dmitry,

That is a bug. Thanks for pointing it out. Anyone care to submit a patch?

--Barry

Dmitry Tkach wrote:
> 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
>>>
>>
>>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>