Viewing Incoming/7447
Full headers

Major security issue: yes  no

Notes:

Notification:

Date: Tue, 20 Nov 2012 13:11:02 +0000
From: metzdorf@geograt.de
To: openldap-its@OpenLDAP.org
Subject: backsql and german umlaute

Full_Name: Herbert Metzdorf
Version: 2.4.30
OS: Windows 7 / 2008
URL: ftp://ftp.openldap.org/incoming/
Submission from: (NULL) (87.138.76.52)


I am using thunderbird to query the openldap with the sql backend. Queries with
german umlaut's give no result because of wrong lower/uppercase conversion. Look
at the following log snippets:

50ab7cdb ==> limits_get: conn=1008 op=1 self="[anonymous]"
this="ou=adressen,dc=geograt,dc=de"
50ab7cdb ==>backsql_search(): base="ou=adressen,dc=geograt,dc=de",
filter="(|(?mail=*search.o.u.*)(cn=*search.o.u.*)(givenName=*search.o.u.*)(sn=*search.o.u.*))",
scope=2,50ab7cdb  deref=0, attrsonly=0, attributes to load: custom list
50ab7cdb ==>backsql_get_db_conn()
...
50ab7cdb <==backsql_srch_query() returns SELECT DISTINCT
ldap_entries.id,ldap_persons.sysid,'inetOrgPerson' AS
objectClass,ldap_entries.dn AS dn FROM ldap_entries,ldap_persons WHERE
ldap_persons.sysid=ldap_entries.keyval AND ldap_entries.oc_map_id=? AND
UPPER(ldap_entries.dn) LIKE CONCAT('%',UPPER(?)) AND (1=0 OR (UPPER(vorname||'
'||nachname) LIKE '%SEARCH.O.U.%') OR (UPPER(vorname) LIKE '%SEARCH.O.U.%') OR
(UPPER(nachname) LIKE '%SEARCH.O.U.%'))

The search expression in converted to uppercase but this fails for the
umlaut's.
Is there a parameter to configure this behavior, or do you know a workaround?

Thanks for your help
Herbert

Followup 1

Date: Tue, 20 Nov 2012 14:23:58 +0100
From: Herbert Metzdorf <metzdorf@geograt.de>
To: <openldap-its@OpenLDAP.org>
Subject: Re: (ITS#7447) backsql and german umlaute

The umlaut's are gone while posting.
So i will try to describe:

The search expression is "search<lower umlaut a>o<lower umlaut
o>u...".
This is converted to "SEARCH<lower umlaut a>O<lower umlaut o>U" in
the 
SELECT statement.
The expected is "SEARCH<upper umlaut a>O<upper umlaut o>U".

-- 
Herbert Metzdorf

Followup 2

Date: Thu, 4 Apr 2013 08:13:10 +0200
From: =?utf-8?B?VG9tw6HFoQ==?= Novosad <tomas.novosad@linuxbox.cz>
To: openldap-its@openldap.org
Subject: (ITS#7447) backsql and german umlaute

Hello,

i got exactly same problem.

Only the discussed character is different ;-)).
When ThunderBird (or ldapsearch, it doesnt matter) send search query to LDAP
with some UTF-8 character,
the result query to DB (PGSQL in this case) is like
(upper(last_name) LIKE '%..EV%')

where the search parameter is:
%<lower case utf8 character>EV%

obviously backsql does not correctly handle UTF8 characters.

I can't find any way how to avoid this.
If only back-sql would leave the upper case conversion on DB - like
this:
(upper(last_name) LIKE upper('%..EV%'))

or use ILIKE

Anyone has any suggestion how to workaround this?

Thanks in advance

-- 
Tom.... Novosad

Followup 3

Date: Thu, 04 Apr 2013 09:49:59 +0200
From: Pierangelo Masarati <masarati@aero.polimi.it>
To: tomas.novosad@linuxbox.cz
CC: openldap-its@openldap.org
Subject: Re: (ITS#7447) backsql and german umlaute

On 04/04/2013 08:13 AM, tomas.novosad@linuxbox.cz wrote:
> Hello,
>
> i got exactly same problem.
>
> Only the discussed character is different ;-)).
> When ThunderBird (or ldapsearch, it doesnt matter) send search query to
LDAP with some UTF-8 character,
> the result query to DB (PGSQL in this case) is like
> (upper(last_name) LIKE '%..EV%')
>
> where the search parameter is:
> %<lower case utf8 character>EV%
>
> obviously backsql does not correctly handle UTF8 characters.
>
> I can't find any way how to avoid this.
> If only back-sql would leave the upper case conversion on DB - like
> this:
> (upper(last_name) LIKE upper('%..EV%'T

The solution is to augment table ldap_attr_mappings (with non-trivial 
implications on DN searching and matching) with a field that specifies 
the encoding for a particular attribute, and convert back and forth any 
time an operation affects those attributes.  Not trivial, but 
contributions are welcome.

p.

-- 
Pierangelo Masarati
Associate Professor
Dipartimento di Scienze e Tecnologie Aerospaziali
Politecnico di Milano