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 | 07/28/2010 (5433 days ago) |
Due | |
Updated | 10/15/2011 (4989 days ago) |
Assigned | 03/13/2011 (5205 days ago) |
Resolved | 03/19/2011 (5199 days ago) |
Github Issue Link | |
Github Pull Request | |
Milestone | 4 |
Patch | No |
This should finally be fixed now with the latest version of Horde_Db.
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
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
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
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
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
difference with postgres 8.4)
difference with postgres 8.4)
Ip still can't escape the bytea and fail in turba to determine the message_uid
State ⇒ Resolved
committed?
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
Assigned to Jan Schneider
Assigned to Chuck Hagenbuch
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 ').
Bug #9153: Add test case.1 files changed, 2 insertions(+), 2 deletions(-)
http://git.horde.org/horde-git/-/commit/93b73049c02daeb5205d304e82da8afb6b98bdfc
Bug #9153: Partially revert da89540692ff08d3468adb92f06f8f9b53b36179.1 files changed, 3 insertions(+), 3 deletions(-)
http://git.horde.org/horde-git/-/commit/0257cb249ac77ece7efc6765f26e8148038f6bc4
changes in Horde_Prefs?
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.
Bug #9153: Fixes for postgresql binary data1) 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
Assigned to Michael Slusarz
Taken from Chuck Hagenbuch
State ⇒ Feedback
changes in Horde_Prefs?
Bug #9153: Standardize on using Horde_Db_Column::binaryToString() for nowThis 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
Bug #9153: Change how quoting of binary data is done, so that Postgresstores 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
Failing test for
Bug: #9153http://git.horde.org/horde-git/-/commit/11ba399cd5b75e46f4cbf5c37235a0a076d19b72
Taken from
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.
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).
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.
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.place I should be able to work on it pretty easily.
Priority ⇒ 3. High
Priority ⇒ 2. Medium
State ⇒ Assigned
Patch ⇒ No
Milestone ⇒ 4
Assigned to Chuck Hagenbuch
Summary ⇒ BYTEA DB escaping for postgres driver
Type ⇒ Bug
Queue ⇒ Horde Framework Packages
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.