[Purdue-pm] DBI Usage Question
jacoby at purdue.edu
Fri Feb 22 09:01:49 PST 2008
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.
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
More information about the Purdue-pm