Re: Use Placeholders. For SECURITY and (sometimes) for PERFORMANCE

[Coruscate]

Just a TIMTOWTDI thought. I do not have time to benchmark now, but will get to it later if nobody does it before I get the chance. The "right" way example presented by jZed prepares the statement once then executes multiple times for each value. In such situations, I tend to do this another way. I form a statement that will do all changes in one blow, then run that through. My method of doing it the "right" way is as such:

my $sta = 'UPDATE foo SET bar=? WHERE ';
$sta .= (join ' OR ', ('baz=?') x @values);

my $sth = $dbh->prepare($sta);
$sth->execute(7, @values);

[runrig]

Making fewer database calls when possible is good. You can also do (if there are not too many @values):

my $sta = 'UPDATE foo SET bar = ? WHERE baz in (", join(',', "?" x @va
+lues), ")";

But I think jZed was just offering a generic example, and on these sorts of updates, I think you often want to set 'bar' equal to something different for each 'baz', so the multiple execution version is more appropriate in that case.

[etcshadow]

This can be a good trick... but it is not generally applicable... since it won't work, in general, for multiple inserts. I say "in general" because there are cases where you can do things like:

insert into table1 (val1,val2,val3)
select val1,val2,val3
from table2
where id in (?,?,?,?,...)

but... again, that will only be an available option some of the time.

[mpeppler]

That might work reasonably well for a fairly limited number of arguments. If your @values array is large (500+, say), then hitting the database 500 times may be faster than having a query with 500 ORs in the WHERE clause, because the optmizer is going to have a hell of a time trying to figure out the best access path, and because there may be a limit to the number of placeholders that a single statement can have (AFAIK for Sybase this limit is 256, btw).

Michael