Re: Prepared Statements

On 17/07/2003 15:47 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 :-)


Guilty as charged. still it was gone midnight... Still, I hope the general
meaning is still clear.

>>
>> 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.

I've certainly seen this referred to as SQL injection. We'll just have to
agree to differ on this one :-)

> 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.

You just need to be aware that there is some extra validation/parsing work
to do when using Statement.

regards

--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants         |
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+