Re: PDO bindValue ORDER BY

Your workaround is probably what I would do myself. Note: mysql_real_escape_string() is technically expecting a "string value", although there is no harm using it, and it's not a bad idea to avoid possible SQL malicious codes. Alternately, you can also write a simple function using regular expression to make sure that $sort is one of your possible sort fields and nothing else. http://www.tudbc.org On 11/1/08, Matthew Peltzer <goochrules@gmail.com> wrote: > ok... this makes more sense now. I know in the past I tried to do > something similar with table names in the WHERE clause, and that > didn't work in the same manner. > > Is there a better way to do what I'm trying to do? that is, sorting > within the SQL statement based on a supplied column name without out > writing multiple SQL statements? > > for now my work around is to some thing like: > > $sort = mysql_real_escape_string($sort); > $sql = "SELECT * FROM table ORDER BY > $sort"; > > which makes me queasy because I spent a lot of time getting away from > inlining variables in SQL statements. > > > > On Fri, Oct 31, 2008 at 6:46 PM, Post TUDBC <post@tudbc.org> wrote: > > Technically, bound parameter is expecting a value, such as > > WHERE ID=:id > > However, ORDER BY is followed by a field name, such as > > ORRDER BY ID > > So I don't think it should work. > > > > If it does work, then it is a sign that the database driver is not > > really preparing the statement (as it should for performance reason), > > but it is just substituiting values to compose a SQL (just for your > > convenience). > > > > On 10/31/08, Matthew Peltzer <goochrules@gmail.com> wrote: > >> Are pdo bound parameters within an ORDER BY clause broken in php 5.2.5? > >> > >> I find that in php 5.2.6 this works as expected: > >> > >> <?php > >> $sql = 'SELECT * FROM table ORDER BY > >> :sort'; > >> $stmt = $pdo->prepare($sql); > >> $stmt->bindValue(':sort', $sort, PDO::PARAM_STR); > >> $stmt->execute(); > >> print_r($stmt->fetchAll(PDO::FETCH_ASSOC)); > >> ?> > >> > >> but under php5.2.5 the ORDER BY clause silently fails. Also, > >> parameters bound to SELECT or WHERE or LIMIT clauses function > >> correctly, but ORDE BY still has no effect. If I remove the > >> "$stmt->bindValue(':sort', $sort, PDO::PARAM_STR);" line or > >> the "ORDER > >> BY :sort" I get a "number of bound variables does not match number of > >> tokens" error. > >> > >> So it appears the parsing mechanism is funcitoning, but what ever is > >> responsible for binding to ORDER BY is not. > >> > >> I've looked in bug reports and the change logs, but did not find a > >> explicit reference to this issue. > >> > >> > >> -- > >> -- Matthew Peltzer > >> -- gooch@goochrules.net > >> > >> > >> -- > >> PHP Database Mailing List (http://www.php.net/) > >> To unsubscribe, visit: http://www.php.net/unsub.php > >> > >> > > > > > > > -- > > -- Matthew Peltzer > -- gooch@goochrules.net > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > >