6.0.0-git
2019-12-06

[#9153] BYTEA DB escaping for postgres driver
Summary BYTEA DB escaping for postgres driver
Queue Horde Framework Packages
Queue Version Git master
Type Bug
State Resolved
Priority 3. High
Owners chuck (at) horde (dot) org, jan (at) horde (dot) org, slusarz (at) horde (dot) org
Requester slusarz (at) horde (dot) org
Created 2010-07-28 (3418 days ago)
Due
Updated 2011-10-15 (2974 days ago)
Assigned 2011-03-13 (3190 days ago)
Resolved 2011-03-19 (3184 days ago)
Milestone 4
Patch No

History
2011-10-15 10:41:34 Jan Schneider Comment #28 Reply to this comment
More work has been done in the binary escaping of the Postgres driver. 
This should finally be fixed now with the latest version of Horde_Db.
2011-06-22 09:54:44 piper (at) hrz (dot) uni-marburg (dot) de Comment #27 Reply to this comment
On second thought I see, that my assumption is wrong: the function 
binaryToString is used when reading the (already broken) 
postgresql-data, therefore this conversion is completely correct.

The error occurs when storing the photo-data into postgresql, where 
the conversion is done by the function Horde_Db_Value_Binary 
(/usr/share/php/Horde/Db/Value/Binary.php). Maybe a special 
postgresql-handling has to be done there?

The photo-data which are stored in my postgresql-DB are all NULL

Andreas

[Show Quoted Text - 12 lines]
2011-06-22 09:17:57 piper (at) hrz (dot) uni-marburg (dot) de Comment #26 Reply to this comment
I observe the same problem:

if I try to synchronize Outlook(2007)-Contacts using Funambol
with Horde 4.0.6, turba 3.0.3 and postgresql-8.4. It works OK, as long 
as the Outlook-contacts do not contain photo-entries. If any of the 
contacts contains a photo, the described error occurs. This is also 
reproducable with iPhone and Funambol.

The problem seems to be the use of the function binaryToString 
(located in /usr/share/php/Horde/Db/Adapter/Postgresql/Column.php), 
which, as far as I understand the code, converts a bytea-array into an 
8bit-character-string.

I think in this case (storing of photo-data into postgresql) the 
conversion has to be reversed, from 8-bit-data to bytea-array, but 
such a function seems to be missing.

Many thanks in advance for a review of this matter,
Andreas
2011-05-14 00:06:13 torben (at) dannhauer (dot) info Comment #25 Reply to this comment

[Show Quoted Text - 17 lines]
Changing the Database encoding did not solve the problem. But I made 
some progress in understanding the bytea matter:

Horde/turba has to convert the bytea values from object_logo and 
object_photo column
it extracts from attributes.phph that it is type='image' and generates 
the 'blobFields' array with the coumn names that horde should convert 
after DB query correctly.
That 'blobFields' arry contains object_photo and object_logo as expected.

/usr/share/php/www/horde/turba/lib/Driver/Sql.php uses that blob Array 
in _parseRead() and decides according to that blobFlieds array if a 
convert by using 'binaryToString(..)' has to by done.

That "binaryToString" function seems to be defined in 
/usr/share/php/Horde/Db/Adapter/Postgresql/Column.php.

So, to recapitulate:
* Horde knows which columns are bytea and have to be converted
* Horde calls binaryToString() for the values which has to be 
converted but it doen't work
* It seems that a) the query itself does not work or a) binaryToString 
function does not convert the correctly queried data...

-> because it fails with the database following query at the last 
cited WHERE condition:

1SELECT object_id, owner_id, object_type, object_members, 
object_uid,#012#011  object_firstname, object_lastname, 
object_middlenames,#012#011  object_nameprefix, object_namesuffix, 
object_alias, object_bday,#012#011  object_photo, object_phototype, 
object_homestreet, object_homepob,#012#011  object_homecity, 
object_homeprovince, object_homepostalcode,#012#011   
object_homecountry, object_workstreet, object_workpob,#012#011   
object_workcity, object_workprovince, object_workpostalcode,#012#011   
object_workcountry, object_tz, object_email, object_homephone,#012#011 
  object_workphone, object_cellphone, object_fax, 
object_pager,#012#011  object_title, object_role, object_company, 
object_logo,#012#011  object_logotype, object_category, object_notes, 
object_url,#012#011  object_freebusyurl, object_pgppublickey, 
object_smimepublickey FROM#012#011  turba_objects WHERE (owner_id = 
'torben@dannhauer.de' AND#012#011  ((object_nameprefix ILIKE '%Sseth%' 
OR object_firstname ILIKE#012#011  '%Sseth%' OR object_middlenames 
ILIKE '%Sseth%' OR object_lastname#012#011  ILIKE '%Sseth%' OR 
object_namesuffix ILIKE '%Sseth%') AND#012#011  object_lastname ILIKE 
'%%' AND object_firstname ILIKE '%Sseth%' AND#012#011   
object_middlenames ILIKE '%%' AND object_nameprefix ILIKE '%%' 
AND#012#011  object_namesuffix ILIKE '%%' AND object_homestreet ILIKE 
'%%' AND#012#011  object_homecity ILIKE '%%' AND object_homeprovince 
ILIKE '%%' AND#012#011  object_homepostalcode ILIKE '%%' AND 
object_workstreet ILIKE '%%' AND#012#011  object_workcity ILIKE '%%' 
AND object_workprovince ILIKE '%%' AND#012
May 14 01:04:44 jonathan HORDE: HORDE [turba] SQL   #012#011SELECT 
object_id, owner_id, object_type, object_members, object_uid,#012#011   
object_firstname, object_lastname, object_middlenames,#012#011   
object_nameprefix, object_namesuffix, object_alias, 
object_bday,#012#011  object_photo, object_phototype, 
object_homestreet, object_homepob,#012#011  object_homecity, 
object_homeprovince, object_homepostalcode,#012#011   
object_homecountry, object_workstreet, object_workpob,#012#011   
object_workcity, object_workprovince, object_workpostalcode,#012#011   
object_workcountry, object_tz, object_email, object_homephone,#012#011 
  object_workphone, object_cellphone, object_fax, 
object_pager,#012#011  object_title, object_role, object_company, 
object_logo,#012#011  object_logotype, object_category, object_notes, 
object_url,#012#011  object_freebusyurl, object_pgppublickey, 
object_smimepublickey FROM#012#011  turba_objects WHERE (owner_id = 
'torben@dannhauer.de' AND#012#011  ((object_nameprefix ILIKE '%Sseth%' 
OR object_firstname ILIKE#012#011  '%Sseth%' OR object_middlenames 
ILIKE '%Sseth%' OR object_lastname#012#011  ILIKE '%Sseth%' OR 
object_namesuffix ILIKE '%Sseth%') AND#012#011  object_lastname ILIKE 
'%%' AND object_firstname ILIKE '%Sseth%' AND#012#011   
object_middlenames ILIKE '%%' AND object_nameprefix ILIKE '%%' 
AND#012#011  object_namesuffix ILIKE '%%' AND object_homestreet ILIKE 
'%%' AND#012#011  object_homecity ILIKE '%%' AND object_homeprovince 
ILIKE '%%' AND#012#011  object_homepostalcode ILIKE '%%' AND 
object_workstreet ILIKE '%%' AND#012#011  object_workcity ILIKE '%%' 
AND object_workprovince ILIKE '%%' AND#012#011  object_workpostalcode 
ILIKE '%%' AND object_title ILIKE '%%' AND#012#011  object_company 
ILIKE '%%' AND object_notes ILIKE '%%' AND object_url#012#011  ILIKE 
'%%' AND object_homephone ILIKE '%%' AND object_workphone 
ILIKE#012#011  '%%' AND object_fax ILIKE '%%' AND object_pager ILIKE 
'%%' AND#012#011  object_cellphone ILIKE '%%' AND object_photo ILIKE 
'%%' AND

I assume the query it self is not properly escaped.

If I modify the query to [...] AND object_photo::text ILIKE '%%' AND [...]
  it works..


Has anyone an idea why the bytea datatype is not properly escaped?

best regards,

Torben
2011-05-11 13:42:40 torben (at) dannhauer (dot) info Comment #24 Reply to this comment
As you say your horde works on PGSQL 9.0 I looked a liitle deeper at 
my horde database.

I have two questions how you set up your database:

1) What is the encoding my database should use? currently it is 
SQL_ASCII ( ENCODING = 'SQL_ASCII' ). Should it be UTF-8 ( ENCODING = 
'UTF8' )?

2) Do I have to configure the bytea encoding in Postgres as described 
in http://drupal.org/node/926636#comment-3625380 ?
  ( ALTER DATABASE horde SET bytea_output = 'escape';)

Thank you for your help,

Torben

2011-05-11 06:48:02 torben (at) dannhauer (dot) info Comment #23 Reply to this comment
Hi Michael,

thanks for your fast reply!

I Horde throws the following error:


May 10 19:07:15 jonathan HORDE: HORDE [turba] SQL QUERY FAILED: 
SQLSTATE[42883]: Undefined function: 7 FEHLER:  Operator existiert 
nicht: bytea ~~* unknown#012ZEILE 1: ..._cellphone ILIKE '%012 
3777777%' AND object_photo ILIKE '%%'...#012                           
                                    ^#012TIP:  Kein Operator stimmt 
mit dem angegebenen Namen und den Argumenttypen überein. Sie müssen 
möglicherweise ausdrückliche Typumwandlungen hinzufügen.   
#012#011SELECT object_id, owner_id, object_type, object_members, 
object_uid,#012#011  object_firstname, object_lastname, 
object_middlenames,#012#011  object_nameprefix, object_namesuffix, 
object_alias, object_bday,#012#011  object_photo, object_phototype, 
object_homestreet, object_homepob,#012#011  object_homecity, 
object_homeprovince, object_homepostalcode,#012#011   
object_homecountry, object_workstreet, object_workpob,#012#011   
object_workcity, object_workprovince, object_workpostalcode,#012#011   
object_workcountry, object_tz, object_email, object_homephone,#012#011 
  object_workphone, object_cellphone, object_fax, 
object_pager,#012#011  object_title, object_role, object_company, 
object_logo,#012#011  object_logotype, object_category, object_notes, 
object_url,#012#011  object_freebusyurl, object_pgppublickey, 
object_smimepublickey FROM#012#011  turba_objects WHERE (owner_id = 
'torben@dannhauer.de' AND#012#011  (((object_nameprefix ILIKE 
'%Test2nn,%' OR object_nameprefix ILIKE#012#011  '%Test2v%' OR 
(object_nameprefix = '' OR object_nameprefix IS NULL))#012#011  AND 
(object_firstname ILIKE '%Test2nn,%' OR object_firstname ILIKE#012#011 
  '%Test2v%' OR (object_firstname = '' OR object_firstname IS NULL)) 
AND#012#011  (object_middlenames ILIKE '%Test2nn,%' OR 
object_middlenames ILIKE#012#011  '%Test2v%' OR (object_middlenames = 
'' OR object_middlenames IS NULL))#012#011  AND (object_lastname ILIKE 
'%Test2nn,%' OR object_lastname ILIKE#012#011  '%Test2v%' OR 
(object_lastname = '' OR object_lastname IS NULL)) AND#012#011   
(object_namesuffix ILIKE '%Test2nn,%' OR object

As consequence it fails to sync the contacts (kronolith sync with iPad 
works fine).

For me it looks like a problem with bytea but of course I do not know 
exactly.. Do have an idea what could cause the trouble?

Thank you very much,

Torben

2011-05-11 06:17:05 Michael Slusarz Comment #22 Reply to this comment
It does not work with Postgres 9.0 (But I assume there is no 
difference with postgres 8.4)
Yes it does.  I am using 9.0.4 right now without any problems.

2011-05-11 06:11:50 torben (at) dannhauer (dot) info Comment #21 Reply to this comment
It does not work with Postgres 9.0 (But I assume there is no 
difference with postgres 8.4)

Ip still can't escape the bytea and fail in turba to determine the message_uid
2011-03-19 20:27:39 Jan Schneider Comment #20
State ⇒ Resolved
Reply to this comment
Works fine with MySQL.
2011-03-19 20:17:50 Chuck Hagenbuch Comment #19 Reply to this comment
I don't have binary fields set up in Turba - can you try what I just 
committed?
2011-03-19 20:16:32 Git Commit Comment #18 Reply to this comment
Changes have been made in Git for this ticket:

Bug #9153: Use Horde_Db generic blob handling in Turba's SQL driver (untested)

  1 files changed, 3 insertions(+), 22 deletions(-)
http://git.horde.org/horde-git/-/commit/ad4212d90158d4195c3a69c6af9a47187de16ba1
2011-03-19 09:13:13 Jan Schneider Comment #17 Reply to this comment
No, like Michael said, Turba still needs to be updated.
2011-03-19 01:38:42 Chuck Hagenbuch Comment #16 Reply to this comment
Jan, seems like this is set? Test suite is fully passing...
2011-03-15 11:53:00 Jan Schneider Comment #15
Assigned to Chuck Hagenbuch
Assigned to Jan Schneider
Reply to this comment
Your identities were probably broken because the intermediate escaping 
solution was not correct, i.e. it escaped too much. I added a test 
case that shows that the stream already returns properly unescaped 
values, and additional unescaping would remove too many escape 
characters (the \' in the test string would turn into ').
2011-03-15 11:50:21 Git Commit Comment #14 Reply to this comment
Changes have been made in Git for this ticket:

Bug #9153: Add test case.

  1 files changed, 2 insertions(+), 2 deletions(-)
http://git.horde.org/horde-git/-/commit/93b73049c02daeb5205d304e82da8afb6b98bdfc
2011-03-15 11:50:16 Git Commit Comment #13 Reply to this comment
Changes have been made in Git for this ticket:

Bug #9153: Partially revert da89540692ff08d3468adb92f06f8f9b53b36179.

  1 files changed, 3 insertions(+), 3 deletions(-)
http://git.horde.org/horde-git/-/commit/0257cb249ac77ece7efc6765f26e8148038f6bc4
2011-03-15 02:30:15 Michael Slusarz Comment #12 Reply to this comment
How do you feel about the current implementation here, including the 
changes in Horde_Prefs?
I have no thoughts on the implementation - I will defer to your 
knowledge there.

But your changes did not entirely fix - things like my identities were 
broken.  My recent changes seem to have fixed the issues (at least 
with respect to escaped characters in my preferences).  But have no 
clue where to put tests for this previous fail case.

Additionally, Turba_Driver_Sql should probably be rewritten - it's got 
a bunch of BLOB code hardcoded, that I don't think is needed anymore.
2011-03-15 02:24:28 Git Commit Comment #11 Reply to this comment
Changes have been made in Git for this ticket:

Bug #9153: Fixes for postgresql binary data
1) Stream data may contain escaped characters, so need to scan for these
characters.
2) Fix regex - should be using (?:foo) instead of [foo].

  1 files changed, 5 insertions(+), 5 deletions(-)
http://git.horde.org/horde-git/-/commit/da89540692ff08d3468adb92f06f8f9b53b36179
2011-03-13 01:50:12 Chuck Hagenbuch Comment #10
Assigned to Michael Slusarz
Taken from Chuck Hagenbuch
State ⇒ Feedback
Reply to this comment
How do you feel about the current implementation here, including the 
changes in Horde_Prefs?
2011-03-13 01:49:04 Git Commit Comment #9 Reply to this comment
Changes have been made in Git for this ticket:

Bug #9153: Standardize on using Horde_Db_Column::binaryToString() for now
This isn't automatic-elegant, but it works. Also extend the binary tests to
other backends, and fix double-escaping of percents in SQLite binary data.

  8 files changed, 86 insertions(+), 17 deletions(-)
http://git.horde.org/horde-git/-/commit/9e5828c9e08a801d8cf4ac307da10d4ab129d436
2011-03-12 23:09:14 Git Commit Comment #8 Reply to this comment
Changes have been made in Git for this ticket:

Bug #9153: Change how quoting of binary data is done, so that Postgres 
stores binary data correctly
Data should be flagged by wrapping it in a Horde_Db_Value_Binary object. This
doesn't solve the problem that postgres blobs are streams when coming out of
PDO.

  11 files changed, 126 insertions(+), 57 deletions(-)
http://git.horde.org/horde-git/-/commit/965d096694da47b40d483439fbfd08f1483ac9ca
2011-02-27 06:02:00 Git Commit Comment #7 Reply to this comment
Changes have been made in Git for this ticket:

Failing test for Bug: #9153

http://git.horde.org/horde-git/-/commit/11ba399cd5b75e46f4cbf5c37235a0a076d19b72
2011-02-27 04:12:36 Chuck Hagenbuch Comment #6
Taken from Horde DevelopersHorde Developers
Reply to this comment
I hadn't read this closely enough and thought of the same solution as 
Jan this afternoon. In fact a similar approach is already implemented 
in Rdo with Horde_Rdo_Query_Literal.

Unless there are objections I'll implement that.
2011-01-26 12:59:40 Jan Schneider Comment #5 Reply to this comment
The problem is with replacing parameter placeholders in 
Horde_Db_Adapter_Base::_replaceParameters(). This is the place where 
we call quote() without a column parameter.
When using prepared statements we don't know the column types, and 
parsing the prepared statement to query the database for column types 
is out of question.

I see two potential solutions for this:
1) Analyze the column value and encode if necessary, e.g. if seeing a 
control character. The problems is that such a character may or may 
not appear in the values of such a column.
2) Add a new wrapper class to Horde_Db that represent a value and can 
be used for proper quoting, casting, escaping and encoding, e.g. 
$value = new Horde_Db_Value($value, 'binary') or even $value = new 
Horde_Db_Value_Binary($value).
2011-01-25 09:41:57 Michael Slusarz Comment #4 Reply to this comment
Not good - the big issue preventing implementation is that quote() 
does not pass the current column to the Postgres driver - thus, the 
postgres driver never does the necessary quoting for binary data.

Not sure if I am following the code completely, but quote() seems to 
be written (at least as called from most locations) with the 
assumption that quoting is independent of the column type.  This is 
wrong.  Not sure how this needs to be fixed properly.
2010-10-08 15:31:58 Michael Slusarz Comment #3 Reply to this comment
Don't have a test case at the moment, but see, e.g., the code in 
Horde_Prefs_Sql.

Line 84:
             switch ($this->_db->adapterName()) {
             case 'PDO_PostgreSQL':
                 // TODO: Should be done in DB driver
                 if (is_resource($row['pref_value'])) {
                     $val = stream_get_contents($row['pref_value']);
                     fclose($row['pref_value']);
                     $row['pref_value'] = $val;
                 }
                 $row['pref_value'] = pg_unescape_bytea($row['pref_value']);
                 break;
             }

Line 146:
                 switch ($this->_db->adapterName()) {
                 case 'PDO_PostgreSQL':
                     // TODO: Should be done in DB driver
                     $value = pg_escape_bytea($value);
                     break;
                 }

This is necessary because of Bug #8130.
2010-10-08 13:41:04 Chuck Hagenbuch Comment #2 Reply to this comment
Any chance you can add a failing test case for this? With that in 
place I should be able to work on it pretty easily.
2010-10-07 15:57:36 Michael Slusarz Assigned to Horde DevelopersHorde Developers
Priority ⇒ 3. High
 
2010-07-28 02:29:05 Michael Slusarz Comment #1
Type ⇒ Bug
State ⇒ Assigned
Priority ⇒ 2. Medium
Summary ⇒ BYTEA DB escaping for postgres driver
Queue ⇒ Horde Framework Packages
Assigned to Chuck Hagenbuch
Milestone ⇒ 4
Patch ⇒ No
Reply to this comment
There needs to be a way to escape (whether manually or automatically) 
BYTEA data for Postgres DBs in Horde_Db.

See, e.g, framework/Prefs/lib/Horde/Prefs/Sql.php and the 
pg_escape_bytea() and pg_unescape_bytea() calls.

Saved Queries