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