We don't bite newbies here... much | |
PerlMonks |
Re: DBI binding field names as parameters (quote_identifier)by tye (Sage) |
on Jun 03, 2014 at 17:32 UTC ( [id://1088471]=note: print w/replies, xml ) | Need Help?? |
One of the primary purposes of place-holders is to safely quote values so that it doesn't matter what data a user provides for a search or to be inserted. (The classic example is a search for "'; drop table users" with a DBD version that supports multiple queries separated by semicolons, which most don't.) Quoting matters. "select foo from bar" is very different from "select 'foo' from bar", quite intentionally. A secondary purpose of place-holders is to allow (for some DBD versions) one to pay only once the cost of parsing an SQL statement (and perhaps of figuring out a performant query plan) while executing similar statements but with different data. Most databases only allow constant values to be "left out" of the SQL being parsed. So, even if your DBD version had some support for a different kind of place-holder that took column names, it would likely boil down to being the same as if you had just done:
With the likely important distinction that your DBD would likely either validate that the value of $column only contained a valid identifier or would quote the identifier. How to quote an identifier varies between databases. At least DBD::Pg supports the quote_identifier() call so you'd want something more like:
Which might be similar to (but will be safer than and more portable than) "select [$column] from ..." or qq(select "$column" from ...). This, of course, prevents a single prepared statement from being used for more than one column. But is likely required with most databases. [And the performance benefit is very often not worth it in practice anyway. Every place I've ended up using DBD in Production we've had to disable the computing of query plans before the values have been provided because performance can be so terribly worsened by that "optimization".] - tye
In Section
Seekers of Perl Wisdom
|
|