Bug #6694

->find() for simple tables generates bad SQL

Details

Submitted	2006-02-04 23:33 UTC
From	steve at cuppett dot com
Status	Bogus
Package	DB_DataObject
PHP Version	4.3.9
OS	Red Hat Enterprise Linux 4
Roadmaps	(Not assigned)

Comments

[2006-02-04 23:33 UTC] steve at cuppett dot com

Description:
------------
In PostgreSQL 7.4.x (postgresql-7.4.8-1.RHEL4.1) and PHP 4.3.9 (php-4.3.9-3.9) with DB_DataObjects 1.8.2, an incorrect (and disturbing) query is being generated.

This happens with the find operation. I get the following query:

SELECT * FROM owners WHERE ( owners.user = 1 )

which generates an error in PostgreSQL. I should get a query similar to the following:

SELECT * FROM owners WHERE user = 1

Test script:
---------------
Database table:

CREATE TABLE owners (
id bigserial NOT NULL,
"user" bigint NOT NULL
);

Definition to DB_DataObject:

class DataObjects_Owner extends DB_DataObject {

var $__table = 'owners';
var $id;
var $user;

/* Static get */
function staticGet($k,$v=NULL) { return DB_DataObject::staticGet('DataObjects_Owner',$k,$v); }

// now define your table structure.
// key is column name, value is type
function table() {
return array(
'id' => DB_DATAOBJECT_INT,
'user' => DB_DATAOBJECT_INT
);
}

// now define the keys.
function keys() {
return array('id');
}
}

Exploit code:

Expected result:
----------------
I expect a rational query to be generated that isn't an error. That deviation of the actual query generated is included above.

Actual result:
--------------
The incorrect PostgreSQL query:

SELECT * FROM owners WHERE ( owners.user = 1 )

is generated.

Here is the error from debugLevel 5:

[2006-02-04 23:45 UTC] steve at cuppett dot com

Was aware that I was using ->find() elsewhere in my project successfully. It appears that with string elements, quotes can validate the query. Perhaps quotes are needed in the numeric cases as well? Successful query:

SELECT * FROM users WHERE ( users.username = 'steve' )

Trace output from successful string usage:

dataobjects_user: find:
dataobjects_user: CONNECT: Checking for database database_ in options
dataobjects_user: CONNECT: NEW CONNECTION
dataobjects_user: CONNECT: pgsql://project:t3ng4t3@localhost/project 082729cbfe8a2469f504bff2e5f80d4e
dataobjects_user: CONNECT: a:1:{s:32:"082729cbfe8a2469f504bff2e5f80d4e";O:8:"db_pgsql":8:{s:10:"autocommit";b:1;s:8:"dbsyntax";s:5:"pgsql";s:3:"dsn";a:9:{s:7:"phptype";s:5:"pgsql";s:8:"dbsyntax";s:5:"pgsql";s:8:"username";s:7:"project";s:8:"password";s:7:"t3ng4t3";s:8:"protocol";s:3:"tcp";s:8:"hostspec";s:9:"localhost";s:4:"port";b:0;s:6:"socket";b:0;s:8:"database";s:7:"project";}s:8:"features";a:7:{s:5:"limit";s:5:"alter";s:8:"new_link";s:5:"4.3.0";s:7:"numrows";b:1;s:8:"pconnect";b:1;s:7:"prepare";b:0;s:3:"ssl";b:1;s:12:"transactions";b:1;}s:9:"fetchmode";i:1;s:22:"fetchmode_object_class";s:8:"stdClass";s:7:"options";a:8:{s:16:"result_buffering";i:500;s:10:"persistent";b:0;s:3:"ssl";b:0;s:5:"debug";i:0;s:14:"seqname_format";s:6:"%s_seq";s:8:"autofree";b:0;s:11:"portability";i:0;s:8:"optimize";s:11:"performance";}s:13:"was_connected";b:1;}}
dataobjects_user: QUERY: SELECT * FROM users WHERE ( users.username = 'steve' )
dataobjects_user: query: QUERY DONE IN 0.016561985015869 seconds
dataobjects_user: RESULT: O:9:"db_result":11:{s:8:"autofree";b:0;s:3:"dbh";O:8:"db_pgsql":8:{s:10:"autocommit";b:1;s:8:"dbsyntax";s:5:"pgsql";s:3:"dsn";a:9:{s:7:"phptype";s:5:"pgsql";s:8:"dbsyntax";s:5:"pgsql";s:8:"username";s:7:"project";s:8:"password";s:7:"t3ng4t3";s:8:"protocol";s:3:"tcp";s:8:"hostspec";s:9:"localhost";s:4:"port";b:0;s:6:"socket";b:0;s:8:"database";s:7:"project";}s:8:"features";a:7:{s:5:"limit";s:5:"alter";s:8:"new_link";s:5:"4.3.0";s:7:"numrows";b:1;s:8:"pconnect";b:1;s:7:"prepare";b:0;s:3:"ssl";b:1;s:12:"transactions";b:1;}s:9:"fetchmode";i:1;s:22:"fetchmode_object_class";s:8:"stdClass";s:7:"options";a:8:{s:16:"result_buffering";i:500;s:10:"persistent";b:0;s:3:"ssl";b:0;s:5:"debug";i:0;s:14:"seqname_format";s:6:"%s_seq";s:8:"autofree";b:0;s:11:"portability";i:0;s:8:"optimize";s:11:"performance";}s:13:"was_connected";b:1;}s:9:"fetchmode";i:1;s:22:"fetchmode_object_class";s:8:"stdClass";s:11:"limit_count";N;s:10:"limit_from";N;s:10:"parameters";a:0:{}s:5:"query";s:62:"SELECT * FROM users WHERE ( users.username = 'steve' ) ";s:6:"result";i:0;s:11:"row_counter";N;s:9:"statement";N;}
dataobjects_user: find: CHECK autofetchd
dataobjects_user: find: DONE

[2006-02-04 23:54 UTC] steve at cuppett dot com

Attempted to modify owner class definition to trick PEAR into thinking that my BIGINT was a string so that it would use quotes. That doesn't appear to work. Modified class:

// now define your table structure.
// key is column name, value is type
function table() {
return array(
'id' => DB_DATAOBJECT_INT,
'user' => DB_DATAOBJECT_STR
);
}

// now define the keys.
function keys() {
return array('id');
}
}

Trace returned:

[2006-02-05 02:42 UTC] steve at cuppett dot com

Have upgraded the same system running RHEL 4 Update 2 to PostgreSQL 8.1.2 via SRPM. This problem seems resolved at that level; however, going off distro to do that seems incorrect to me.

Is there anything else that can be done at the query generation level to work around this problem on standard PostgreSQL installations from Red Hat? (PG 7.4.8)

Unsure whether to open a bug against PostgreSQL or Red Hat Enterprise support now.

[2006-02-07 21:53 UTC] steve at cuppett dot com

No. This theory is wrong, should have mentioned that. Older version of DB_DataObject didn't have brackets (). When I hit problem, I updated PEAR DB and DB_DataObject and started seeing the bracket. Both had same problem.

Can create the table in PostgreSQL on RHEL4 and recreate without PEAR DB_DataObject by entering commands both with and without quotes into the command line.

[2006-02-07 22:12 UTC] steve at cuppett dot com

Have opened problem report with Red Hat:

https://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=180407

[2006-02-08 01:39 UTC] steve at cuppett dot com

Doesn't seem to have done anything to Postgresql in terms of the generation. I tried both ` and ' in the query suggested and PostgreSQL didn't seem to appreciate it. Maybe something is missing in my config?

$options = array(
'database' => 'xxxxxxxxxxxx',
'require_prefix' => 'include/dataobjects/',
'class_prefix' => 'DataObjects_',
'quote_indentifiers' => '1'
);

With that config, nothing changed in the query generated.

[2006-02-08 13:50 UTC] steve at cuppett dot com

I have a feeling your recommendation will work. Here is the response from the Red Hat bug database that was closed:

------- Additional Comments From tgl@redhat.com 2006-02-08 01:38 EST -------
USER is a reserved word according to both the SQL standard and the Postgres grammar. You can use it as
a column name only if you always surround it in double quotes. The fact that the 8.1 grammar lets you
use it as the second name in a qualified identifier without quotes is a bit interesting, but it does not make
7.4's behavior a bug ... and in fact you'd be pretty foolish to assume that releases after 8.1 will still let you
do that. (The example without the qualified name isn't doing what you think at all ... it's invoking the
CURRENT_USER function.)

Bottom line: choose a different name for your column, or get used to using double quotes.