DBD::mysql bind variables in ORDER BY clause

dot.tom has asked for the wisdom of the Perl Monks concerning the following question:

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.

PREAMBLE

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 (identifier quoting in ORDER BY clause)

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?

Comment on DBD::mysql bind variables in ORDER BY clause

Replies are listed 'Best First'.
Re: DBD::mysql bind variables in ORDER BY clause by ikegami (Patriarch) on Jun 29, 2009 at 20:44 UTC
(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). That's a lot of needless speculation. It assumes that DBD emulates placeholders (needlessly, because the driver doesn't support them or because the database doesn't support them). Ideally, the change is that 5.x started supporting placeholders. I don't know which one's true, but it doesn't matter. "select from clients order by ?" and passing "last_name" won't work anymore* You are segregating the query and the parameters incorrectly. Elements of both `getQuery()` and `getQueryVars()` form the query. You need to reorganise your data. Using your notation, the minimal change would be: `$dbh->prepare( build_query( $dbh, getQueryInfo() ) ); $sth->execute( getQueryVars() );` [download] or `$dbh->prepare( build_query( $dbh, getBaseQuery(), getOrder() ) ); $sth->execute( getQueryVars() );` [download] It has been suggested that $dbh->quote_identifier() will do the trick, and this is undoubtedly so if everything were hard-coded. That makes no sense. If the query was hard-coded, you wouldn't need `quote_identifier`. The following builds an ORDER BY clause with nothing hard-coded: `if (@order) { $query .= ' ORDER BY ' . join ', ', map { $dbh->quote_identifier($_->[0]) . " $_->[1]" } @order; }` [download] `@order` is expected to resemble the following: `my @order = ( [ Brand => 'ASC' ], [ Model => 'ASC' ], [ Year => 'DESC' ], );` [download]	[reply] [d/l] [select]
Re: DBD::mysql bind variables in ORDER BY clause by DStaal (Chaplain) on Jun 29, 2009 at 19:56 UTC
I think we need to see more code. Somewhere, somehow, you are generating the string to pass to `prepare()`, and it is probably possible to rewrite how you are doing that. What does `getQuery()` look like? (From the above, it would have to return the string.)	[reply] [d/l] [select]
Re: DBD::mysql bind variables in ORDER BY clause by dot.tom (Initiate) on Jun 29, 2009 at 21:29 UTC
Thanks for your helpful comments so far. Before I throw up more code, which may or may not help clarify the issue, further experimentation has led me to this (not so profound) discovery. This: `my $sth = $dbh->prepare("select * from clients order by ? limit 20;"); $sth->execute($dbh->quote_identifier("last_name"));` [download] doesn't actually help after all. Digging deeper, it appears that I have probably been (mis)using DBI in highly dubious ways: With most drivers, placeholders can't be used for any element of a statement that would prevent the database server from validating the statement and creating a query execution plan for it. which, if I understand correctly, includes using ? as a placeholder for a column name. So I guess I'm screwed.	[reply] [d/l]
Re: DBD::mysql bind variables in ORDER BY clause by psini (Deacon) on Jun 29, 2009 at 20:16 UTC
I agree that using field names as query parameters is not a good idea. I don't know why it worked before, because `SELECT x FROM y ORDER BY 'z'` is not valid SQL. Perhaps MySQL 4 was a bit relaxed on its syntax. I can see only two possible strategies to solve the proble: (a) if you can change the values returned by getQuery() you can replace the placeholders "?" in the ORDER BY clauses with something else (perhaps "??") or (b) you have to write a parser that recognizes the ORDER BY clause and. In both cases you must preprocess the query before passing it to prepare(), then execute() can replace the placeholders with real values Rule One: "Do not act incautiously when confronting a little bald wrinkly smiling man."	[reply] [d/l]