php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Request #48588 pg_query_params doesn't accept ORDER BY parameter
Submitted: 2009-06-17 17:00 UTC Modified: 2012-03-29 10:30 UTC
Votes:2
Avg. Score:5.0 ± 0.0
Reproduced:2 of 2 (100.0%)
Same Version:1 (50.0%)
Same OS:1 (50.0%)
From: jake_lake at selinc dot com Assigned:
Status: Wont fix Package: PostgreSQL related
PHP Version: 5.2.9 OS: Ubuntu 8.10
Private report: No CVE-ID: None
Have you experienced this issue?
Rate the importance of this bug to you:

 [2009-06-17 17:00 UTC] jake_lake at selinc dot com
Description:
------------
In attempting to use the pg_query_params function, it came to my attention that trying to use an ORDER BY with a parameter fails.  After searching high and low I finally found someone else with the same issue.  It was reported in Bug # 45101 and I believe falsely written-off as bogus.

In the bug report Alan writes, " I've looked at the pg_trace() output and it appears to be doing the right thing. All I can assume is that the parameter is being converted to a TRUE for an ORDER BY, and so the database happily accepts 'ORDER BY 1'."  

This makes sense as then the query should run fine using the 1 as the column number and selecting the first column number from the table to order on.

However, the given response by hholzgra@php.net does not make any sense.  If the expression were to truly be evaluated using a constant string, PGSQL would return an error as strings cannot be in the ORDER BY clause, only column headers and integers representing the column # wanted to order on.

Therefore, it seems as Alan was more on the right track assuming that for some reason the input value is being converted to TRUE or 1.  This must surely be faulty behaviour as it essentially is ignoring any parameter assigned to ORDER BY and throwing out that part of the clause all together.  

If, however, this is the designed behaviour of this function, it should at least be documented so that others do not get caught up debugging for hours over this silly thing!


Reproduce code:
---------------
#!/opt/php/bin/php
<?php
/*
create table php_bug (id integer, name varchar(255));
insert into php_bug values (1, 'one');
insert into php_bug values (2, 'two');
insert into php_bug values (3, 'three');
insert into php_bug values (4, 'four');
insert into php_bug values (5, 'five');
 */

$conn = pg_connect('host=localhost dbname=test port=5432 user=web');

$sql = 'SELECT * FROM php_bug WHERE name LIKE $1 ORDER BY $2';
$params = array('%o%', 'doesnt_exist_and_should_be_an_sql_error');

if (! pg_connection_busy($conn)) pg_send_query_params($conn, $sql,
$params);

$res = pg_get_result($conn);

while($row = pg_fetch_assoc($res))
        echo "{$row['id']} - {$row['name']}\n";

?>

Expected result:
----------------
If passing as constant string like hholzgra@php.net claims:
ERROR:  non-integer constant in ORDER BY

If passing as column header that doesn't exist:
ERROR:  column "doesnt_exist_and_should_be_an_sql_error" does not exist
LINE 11:               ORDER BY doesnt_exist_and_should_be_an_sql_error;
                               ^



Actual result:
--------------
1 - one
2 - two
4 - four

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2009-06-24 17:50 UTC] sjoerd-php at linuxonly dot nl
Thank you for your bug report.

It would be nice if your example worked, but there are some problems about the implementation.

In your example query, both the value for $1 and for $2 are properly escaped. The strings passed to pg_send_query_params() are quoted and pasted in the query. This results in the following query:
SELECT * FROM php_bug WHERE name LIKE '%o%' ORDER BY 'doesnt_exist_and_should_be_an_sql_error'

Now, in order for the behavior to change as you want, the second parameter, $2, should not be escaped. In general, any parameter which is part of an ORDER BY clause should not be escaped. However, this means that pg_send_query_params() needs to parse the query, just to insert the variables. This is error-prone, slow, inconsistent and it may still not satisfy everybody.

So while I acknowledge that it would be nice if it worked like you say, it is hard for PHP to know whether your parameter is a string expression or a table name.
 [2011-01-01 23:24 UTC] jani@php.net
-Summary: pg_query_params doesn't accepted ORDER BY parameter +Summary: pg_query_params doesn't accept ORDER BY parameter -Package: Feature/Change Request +Package: PostgreSQL related
 [2012-03-29 10:30 UTC] yohgaki@php.net
-Status: Open +Status: Wont fix
 [2012-03-29 10:30 UTC] yohgaki@php.net
This is not a pgsql modules behavior, but postgresql.
Please ask PostgreSQL project, if you would like change this behavior.
 
PHP Copyright © 2001-2020 The PHP Group
All rights reserved.
Last updated: Fri Nov 27 11:01:24 2020 UTC