Re^3: Variable interpolation in a file to be read in

[afoken]

in reply to Re^2: Variable interpolation in a file to be read in
in thread Variable interpolation in a file to be read in

quote() is evil. Well, not exactly, but using quote() manually causes more trouble than it is worth. Always use placeholders and you never again have to think about quoting values for SQL statements. Plus, DBI, DBD or database can cache prepared statements for better application performance.

Alexander

[runrig]

Always use placeholders...

Beware of absolutes. Placeholders are not always the way to go.

[afoken]

Re^4: DBI, place holders and CGI forms (quote++)

Problem 1: "limit ?" with DBD::mysql. Seems to be a bug introduced in some version of DBD::mysql. Maybe the placeholder support for limit was an accidental feature removed by a "bugfix", but as this "bugfix" breaks existing code, it is a bug, not a bugfix. Maybe it is related to changed placeholder handling code in 4.014 / 4.015, and maybe it could be fixed using the mysql_bind_comment_placeholders attribute. Or it was broken in 3.0008_1, when the LIMIT parsing was changed. I don't know, I don't like MySQL, and so I don't use it.

Problem 2: Datetime literals with DBD::ODBC. Seems to be caused by using an incompatible datetime format in the application, according to the reply by mje, the maintainer of DBD::ODBC.

Where the advice to use DBI bind parameters can go wrong (long)

Problem 3: "col like ?" instead of "col like 'prefix%'" forces Oracle's query optimizer into a full table scan, because it can no longer see the special case that only col values starting with prefix are relevant and thus an index on col can be used. When I learned to use Oracle, I was told to avoid like when substr can be used. I have no Oracle instance available, but I think the query optimizer should be able to use the index with "substr(col,1,length(:p1))=:p1".

Can a DBI Placeholder accept multiple values?

Problem 4: "col in (list)". Yeah. SQL sucks here. This is not a DBI problem. You have to construct a different SQL statement for each different list length. And in parallel, you can/have to construct a list of parameters to be passed to $sth->bind_param() or $sth->execute(). Or, you use a wrapper to do so: igelkott's reply proposes to use the special "??" placeholder of DBIx::Simple.

I still see no reason to use quote() in all but the first case. And even this "limit" problem would better be fixed by reconfiguring or reparing DBD::mysql, or by downgrading it to the latest "known good" version.

Alexander

[runrig]

I've run into ODBC drivers that did not cope very well with placeholders and executing statements multiple times, substr() doesn't fix everything when you do sometimes want wildcards at the beginning of a string, and if I might have wildly varying numbers of "?" in "IN (?,?,?)" type clauses, I usually just go with quote().