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: function isOwner() { $toReturn = false; DB_DataObject::debugLevel(5); if (isset($this->id)) { $this_owner = new DataObjects_Owner; $this_owner->user = $this->id; $rowcount = $this_owner->find(); if ($rowCount == 1) { $toReturn = true; } } return $toReturn; } 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: dataobjects_owner: find: dataobjects_owner: CONNECT: Checking for database database_ in options dataobjects_owner: CONNECT: USING CACHED CONNECTION dataobjects_owner: QUERY: SELECT * FROM owners WHERE ( owners.user = 1 ) dataobjects_owner: Query Error: [db_error: message="DB Error: syntax error" code=-2 mode=return level=notice prefix="" info="SELECT * FROM owners WHERE ( owners.user = 1 ) [nativecode=ERROR: syntax error at or near "user" at character 39]"] dataobjects_owner: ERROR: db_error Object ( [error_message_prefix] => [mode] => 1 [level] => 1024 [code] => -2 [message] => DB Error: syntax error [userinfo] => SELECT * FROM owners WHERE ( owners.user = 1 ) [nativecode=ERROR: syntax error at or near "user" at character 39] [backtrace] => Array ( [0] => Array ( [file] => /usr/share/pear/DB.php [line] => 888 [function] => pear_error [class] => db_error [type] => -> ) [1] => Array ( [file] => /usr/share/pear/PEAR.php [line] => 536 [function] => db_error [class] => db_error [type] => -> ) [2] => Array ( [file] => /usr/share/pear/DB/common.php [line] => 1849 [function] => raiseerror [class] => pear [type] => :: ) [3] => Array ( [file] => /usr/share/pear/DB/pgsql.php [line] => 785 [function] => raiseerror [class] => db_pgsql [type] => -> ) [4] => Array ( [file] => /usr/share/pear/DB/pgsql.php [line] => 337 [function] => pgsqlraiseerror [class] => db_pgsql [type] => -> ) [5] => Array ( [file] => /usr/share/pear/DB/common.php [line] => 1162 [function] => simplequery [class] => db_pgsql [type] => -> ) [6] => Array ( [file] => /usr/share/pear/DB/DataObject.php [line] => 2324 [function] => query [class] => db_pgsql [type] => -> ) [7] => Array ( [file] => /usr/share/pear/DB/DataObject.php [line] => 443 [function] => _query [class] => dataobjects_owner [type] => -> ) [8] => Array ( [file] => /home/project/project/www/includes/dataobjects/user.php [line] => 59 [function] => find [class] => dataobjects_owner [type] => -> ) [9] => Array ( [file] => /home/project/project/www/includes/actions/login.php [line] => 92 [function] => isowner [class] => dataobjects_user [type] => -> ) [10] => Array ( [file] => /home/project/project/www/third_party/struts4php/action/actionbanana.php [line] => 304 [function] => perform [class] => loginaction [type] => -> ) [11] => Array ( [file] => /home/project/project/www/third_party/struts4php/action/actionbanana.php [line] => 185 [function] => processactionperform [class] => actionbanana [type] => -> ) [12] => Array ( [file] => /home/project/project/www/index.php [line] => 4 [function] => process [class] => actionbanana [type] => -> ) ) [callback] => ) dataobjects_owner: find: CHECK autofetchd dataobjects_owner: find: DONE

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

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: 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_STR ); } // now define the keys. function keys() { return array('id'); } } Trace returned: dataobjects_owner: find: dataobjects_owner: CONNECT: Checking for database database_ in options dataobjects_owner: CONNECT: USING CACHED CONNECTION dataobjects_owner: QUERY: SELECT * FROM owners WHERE ( owners.user = '1' ) dataobjects_owner: Query Error: [db_error: message="DB Error: syntax error" code=-2 mode=return level=notice prefix="" info="SELECT * FROM owners WHERE ( owners.user = '1' ) [nativecode=ERROR: syntax error at or near "user" at character 39]"] dataobjects_owner: ERROR: db_error Object ( [error_message_prefix] => [mode] => 1 [level] => 1024 [code] => -2 [message] => DB Error: syntax error [userinfo] => SELECT * FROM owners WHERE ( owners.user = '1' ) [nativecode=ERROR: syntax error at or near "user" at character 39] [backtrace] => Array ( [0] => Array ( [file] => /usr/share/pear/DB.php [line] => 888 [function] => pear_error [class] => db_error [type] => -> ) [1] => Array ( [file] => /usr/share/pear/PEAR.php [line] => 536 [function] => db_error [class] => db_error [type] => -> ) [2] => Array ( [file] => /usr/share/pear/DB/common.php [line] => 1849 [function] => raiseerror [class] => pear [type] => :: ) [3] => Array ( [file] => /usr/share/pear/DB/pgsql.php [line] => 785 [function] => raiseerror [class] => db_pgsql [type] => -> ) [4] => Array ( [file] => /usr/share/pear/DB/pgsql.php [line] => 337 [function] => pgsqlraiseerror [class] => db_pgsql [type] => -> ) [5] => Array ( [file] => /usr/share/pear/DB/common.php [line] => 1162 [function] => simplequery [class] => db_pgsql [type] => -> ) [6] => Array ( [file] => /usr/share/pear/DB/DataObject.php [line] => 2324 [function] => query [class] => db_pgsql [type] => -> ) [7] => Array ( [file] => /usr/share/pear/DB/DataObject.php [line] => 443 [function] => _query [class] => dataobjects_owner [type] => -> ) [8] => Array ( [file] => /home/project/project/www/includes/dataobjects/user.php [line] => 82 [function] => find [class] => dataobjects_owner [type] => -> ) [9] => Array ( [file] => /home/project/project/www/includes/actions/login.php [line] => 91 [function] => isowner [class] => dataobjects_user [type] => -> ) [10] => Array ( [file] => /home/project/project/www/third_party/struts4php/action/actionbanana.php [line] => 304 [function] => perform [class] => loginaction [type] => -> ) [11] => Array ( [file] => /home/project/project/www/third_party/struts4php/action/actionbanana.php [line] => 185 [function] => processactionperform [class] => actionbanana [type] => -> ) [12] => Array ( [file] => /home/project/project/www/index.php [line] => 4 [function] => process [class] => actionbanana [type] => -> ) ) [callback] => ) dataobjects_owner: find: CHECK autofetchd dataobjects_owner: find: DONE

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.

I'm guessing this is due to the extra brackets - and older versions of postgresql not liking them. any chance of testing that theory? SELECT * FROM owners WHERE ( owners.user = 1 ) SELECT * FROM owners WHERE owners.user = 1

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.

Have opened problem report with Red Hat: https://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=180407

Interesting, can you try quote_indentifiers = 1 effectively doing something like this: (can tremember the quote identifiers char on postgres) SELECT * FROM `owners` WHERE ( `owners`.`user` = 1 ) Regards

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.

looking at your original table definition, i noticed that user had been quoted = with double quotes. CREATE TABLE owners ( id bigserial NOT NULL, "user" bigint NOT NULL ); sorry, try quote_identifiers rather than quote_indentifiers I suspect the SQL should be written SELECT * FROM "owners" WHERE ( "owners"."user" = 1 )

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.

Thank you for taking the time to write to us, but this is not a bug. yeap - quote_identifiers