Req #48588 [Opn->Wfx]: pg_query_params doesn't accept ORDER BY parameter

Edit report at https://bugs.php.net/bug.php?id=48588&edit=1 ID: 48588 Updated by: yohgaki@php.net Reported by: jake_lake at selinc dot com Summary: pg_query_params doesn't accept ORDER BY parameter -Status: Open +Status: Wont fix Type: Feature/Change Request Package: PostgreSQL related Operating System: Ubuntu 8.10 PHP Version: 5.2.9 Block user comment: N Private report: N New Comment: This is not a pgsql modules behavior, but postgresql. Please ask PostgreSQL project, if you would like change this behavior. Previous Comments: ------------------------------------------------------------------------ [2009-06-24 17:50:38] 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. ------------------------------------------------------------------------ [2009-06-17 17:00:43] 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 ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=48588&edit=1