Hi Monks, thanks for the prelim comments in CB, but this one seems to be more complex, or at least I'm making it more complex.
What we have is a MySQL query parser of sorts - essentially I'm abstracting the data layer of my application by storing all my queries externally to the business logic. These external queries leverage DBD::mysql's native ability to dynamically bind values to pre-made statements using the '?' feature. So I might have a simple query like "select * from clients where row_id=?" and I bind a variable eg: $user_input{row_id} to this query.
One important thing to note is that the variable names themselves are also stored externally - so nowhere do I have a line of code that says:
$sth->prepare("select * from clients where row_id=?";
$sth->execute($user_input{row_id});
but rather:
$sth->prepare(getQuery());
$sth->execute(getQueryVars());
The issue has only come to light since we upgraded our MySQL servers to 5.x from 4.1. This method of dynamically generating queries has been operating well for nigh on 3 years. Now however, we're discovering that whenever the bind variables are being used in the context of the ORDER BY clause, the query breaks.
So: "select * from clients order by ?" and passing "last_name" won't work anymore, because (I believe) DBD::mysql adds single quotes to all its bind variables and (I believe) MySQL 5.x is now much more strict about correctly quoting identifiers (like column names).
It has been suggested that $dbh->quote_identifier() will do the trick, and this is undoubtedly so if everything were hard-coded. I'm not sure, however, that this will work, as everything is interpreted and it's not easy for my parser to tell whether the variable is used in the context of a string somewhere or whether it needs to be quoted as an identifier for an ORDER BY clause.
Any thoughts?
In reply to DBD::mysql bind variables in ORDER BY clause by dot.tom
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |