[Tickets #10380] SQL error when importing vCard with PHOTO attribute

[bugs at horde.org]

DO NOT REPLY TO THIS MESSAGE. THIS EMAIL ADDRESS IS NOT MONITORED.

Ticket URL: http://bugs.horde.org/ticket/10380
------------------------------------------------------------------------------
  Ticket             | 10380
  Created By         | thpo+horde at dotrc.de
  Summary            | SQL error when importing vCard with PHOTO attribute
  Queue              | Turba
  Version            | 3.0.4
  Type               | Bug
  State              | Unconfirmed
  Priority           | 1. Low
  Milestone          |
  Patch              |
  Owners             |
------------------------------------------------------------------------------


thpo+horde at dotrc.de (2011-07-26 06:30) wrote:

When trying to import a vCard (either via syncml or using  
/turba/data.php) I get the following error:

2011-07-26T08:03:28+02:00 ERR: HORDE [turba] SQL QUERY FAILED:  
SQLSTATE[22021]: Character not in repertoire: 7 ERROR:  invalid byte  
sequence for encoding "UTF8": 0xb7
TIP:  This error can also happen if the byte sequence does not match  
the encoding expected by the server, which is controlled by  
"client_encoding".
         SELECT object_id, owner_id, object_type, object_members, object_uid,
           object_firstname, object_lastname, object_middlenames,
           object_nameprefix, object_namesuffix, object_alias, object_bday,
           object_photo, object_phototype, object_homestreet, object_homepob,
           object_homecity, object_homeprovince, object_homepostalcode,
           object_homecountry, object_workstreet, object_workpob,
           object_workcity, object_workprovince, object_workpostalcode,
           object_workcountry, object_tz, object_email, object_homephone,
           object_workphone, object_cellphone, object_fax, object_pager,
           object_title, object_role, object_company, object_logo,
           object_logotype, object_category, object_notes, object_url,
           object_freebusyurl, object_pgppublickey, object_smimepublickey FROM
           turba_objects WHERE (owner_id = 'thpo' AND  
(((object_nameprefix ILIKE
           '%Test%' OR object_nameprefix ILIKE '%User%' OR (object_nameprefix =
           '' OR object_nameprefix IS NULL)) AND (object_firstname  
ILIKE '%Test%'
           OR object_firstname ILIKE '%User%' OR (object_firstname = '' OR
           object_firstname IS NULL)) AND (object_middlenames ILIKE '%Test%' OR
           object_middlenames ILIKE '%User%' OR (object_middlenames = '' OR
           object_middlenames IS NULL)) AND (object_lastname ILIKE '%Test%' OR
           object_lastname ILIKE '%User%' OR (object_lastname = '' OR
           object_lastname IS NULL)) AND (object_namesuffix ILIKE '%Test%' OR
           object_namesuffix ILIKE '%User%' OR (object_namesuffix = '' OR
           object_namesuffix IS NULL))) AND object_lastname ILIKE '%User%' AND
           object_firstname ILIKE '%Test%' AND object_photo ILIKE  
'%i<B7>^]%' AND
           object_phototype ILIKE '%png%')) [pid 7729 on line 808 of  
"/usr/share/php/Horde/Db/Adapter/Base.php"]


I have tried to play around with some character encoding settings in  
Horde and PostgreSQL, but it did not solve the problem.

My current settings are:
  - in horde/config/conf.php:$conf['sql']['charset'] = 'utf-8';
  - in the database both client_encoding and server_encoding are set to UTF8

As a workaround for this bug, I have currently added the following  
line to my turba/config/backends.local.php:
  unset($cfgSources['localsql']['map']['photo']);

Please note, that this bug is closely related to Bug #9725. But in my  
opinion these are two seperate issues in the same code region.