Re: Prepared Statements

To solve this problem we have stored procedures, know in PostgreSQL land as 
'functions'.


Dmitry Tkach wrote:>>
>> Forget performance for a moment and consider database security. Lets 
>> imagine that you have an address book table called address_book
>>
>> CREATE TABLE address_booK
>> (
>>     name        varchar(30),
>>     address     text
>> );
>>
>> and you want to select a row by name. You might write
>>
>> String query = "SELECT * from address_book WHERE name = "+strName
>>
>> where strName was typed in by the user. What would happen if the user 
>> typed:
>>
>> joe;delete from address_book
> 
> 
> 
> 
> Either the exact same thing as what you describe below with the 
> PreparedStatement, or a syntax eror if you forget to put the user's 
> input into quotes when constracting your sql :-)
> 
>>
>> 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
> 



-- 
Fernando Nasser
Red Hat - Toronto                       E-Mail:  fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9