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@lexmark.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 @postgresql.org on 07/17/2003 
>>> 10:47:49
>>> AM
>>>
>>> Sent by:    pgsql-jdbc-owner@postgresql.org
>>>
>>>
>>> To:    Paul Thomas 
>>> cc:    "pgsql-jdbc @ postgresql . 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@postgresql.org
>>>
>>>
>>>
>>>
>>>
>>> ---------------------------(end of broadcast)---------------------------
>>> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>>>
>>
>>
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>