ID: 48588
Comment by: sjoerd-php at linuxonly dot nl
Reported By: jake_lake at selinc dot com
Status: Open
Bug Type: PostgreSQL related
Operating System: Ubuntu 8.10
PHP Version: 5.2.9
New Comment:
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.
Previous Comments:
------------------------------------------------------------------------
[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 hholz...@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 hholz...@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 http://bugs.php.net/?id=48588&edit=1
