PDO bindValue ORDER BY

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:

$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

Re: PDO bindValue ORDER BY

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 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:
>
> > $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
>
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: PDO bindValue ORDER BY

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 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 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:
>>
>> >> $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

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 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 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 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:
> >>
> >> > >> $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
>
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: PDO bindValue ORDER BY

Matthew Peltzer 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.

Validate your data.

You're just making a bad query here.

I somehow get "orderby" to be "field_that_does_not_exist".

You end up with:

select * from table where blah order by `field_that_does_not_exist`;

which will generate a mysql_error and give no results at all.

--
Postgresql & php tutorials
http://www.designmagick.com/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: PDO bindValue ORDER BY

Chris wrote:
> Matthew Peltzer 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.
>
> Validate your data.
>
> You're just making a bad query here.
>
> I somehow get "orderby" to be "field_that_does_not_exist".
>
> You end up with:
>
> select * from table where blah order by `field_that_does_not_exist`;
>
> which will generate a mysql_error and give no results at all.

Also mysql_real_escape_string needs a connection to do the escaping
properly, so now you're doubling up.

1 connection for pdo
1 connection for mysql_real_escape_string to use

You could get away with mysql_escape_string however it doesn't take
charset's into account so you may have some issues with those.

--
Postgresql & php tutorials
http://www.designmagick.com/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php