[Purdue-pm] DBI Usage Question

[Dave Jacoby]

Andy Lester wrote:
> On Feb 22, 2008, at 10:41 AM, Rick Westerman wrote:

>>    The use of '?' marks (placeholders) in DBI is two fold -- First for
>> speed since  the prepare statement only has to be done once instead of
>> each time for each new set of variables.  Second it automatically quotes
>> the parameters so that you do not need to do so.

> And number three, it protects against SQL injection attacks.
> xoxo,
> Andy

Starting from first principles, assume this is your query.

   SELECT * FROM table WHERE id = '$id' ;

If you set $id to be "foo' ; SELECT * FROM * ; '" , you get

   SELECT * FROM table WHERE id = 'foo' ; SELECT * FROM * ; '' ;

This gives me every table. But if quote $id, you get

   SELECT * FROM table WHERE id = 'foo\' ; SELECT * FROM * ; \'' ;

That being a very unlikely value for the id field, the query returns 
nothing, and the injection attack is avoided.

That may be done automagically in the Schwartz syntax. I do like the
explicit, but I'm flexible. But it seems that Rick's point 2 and Andy's 
point 3 are the same.

There are other reasons why you'd want to quote, like someone wanting to 
store this current paragraph with the possessives into a database, but 
avoiding SQL injection is a big and important one.

-- 
David Jacoby    jacoby at purdue.edu
   Purdue Genomics Facility
   S049, WSLR building
   Phone: hah!