The development list of the OpenXPKI project, a project to build a high-end Certificate Authority / trust center software

Issues with DBI/SQL abstraction layer

Hi,

I have a (severe?) issue with the SQL abstraction layer. I am using 
mysql as backend and unfortunatly no clue about other rdbms - therefore 
I need some feedback if the issue is mysql only or not.

Problem:
I am searching a certificate by its subject, the subject I am looking 
for contains a backshlash character.

my $issuer_dn = "OU=Trustcenter,O=Snakeoil\, Inc.,C=US";
CTX('dbi_backend')->first(
   TABLE   => 'CERTIFICATE',
   COLUMNS => [ 'IDENTIFIER' ],
   DYNAMIC => {
     'SUBJECT' => $issuer_dn,
     'PKI_REALM' => $pki_realm
});

I get an empty result, which I tracked down to:

According OpenXPKI::Server::DBI::SQL, Line 979 the query uses a "like" 
statement on the condition. In mysql the backslash is an escape 
character when used in like statements:
http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html

Workaround: I ended up now in adding slashes to this special query, 
which does the job, but I expect that can make some headache one day in 
any other query, too. Besides I do not now what happens on other RDBMS.

(Continue reading)

headers

Oliver Welter | 8 Feb 11:56

Problems with Umlaut / UTF8

Hi All,

I was trying to issue certificates using german Umlaute and run into 
several problems...

The main issue is, that the Serialization system crashes with 
I18N_OPENXPKI_SERIALIZATION_SIMPLE_READ_HASH_KEY_LENGTH_FORMAT_CORRUPTED 
when you try to transmit umlauts. Remarkably, the message with the 
umlauts is received and properly decoded (as war as I see) , but the 
next following one throws the error.

However, in the database the umlaut chars show up as two characters and 
when you view such information on the frontend again, it gets decoded to 
clumsy boxes. Besides, the database (mysql 5.1) is by default 
initialised in  latin1, but changing it to utf8 does not help either.

I use a debian squeeze with de/en utf8 locale, the webpages are also 
delivered using utf8 as charset, so I dont see anything "outside" to 
create this problems.

Anybody here has positive experience with Umlauts and can help out?

Oliver
--

-- 
Protect your environment -  close windows and adopt a penguin!
PGP-Key: 3B2C 8095 A7DF 8BB5 2CFF  8168 CAB7 B0DD 3985 1721

Attachment (smime.p7s): application/pkcs7-signature, 4447 bytes

(Continue reading)

headers

Martin Bartosch | 8 Feb 13:46

Re: Problems with Umlaut / UTF8

Hi Oli,

> Anybody here has positive experience with Umlauts and can help out?

Interesting problem, and I am not sure why this happens. Originally we designed OpenXPKI to be fully
UTF8-compliant. I also know that it was used by our Russian friends to some success, and they also kept a
keen eye on keeping the code UTF8-aware. However, I remember Sergei writing to the list that some problems
occurred with later commits regarding UTF8 support.
It also seems to be an annoying truth that the semantics of handling UTF8 in Perl has changed significantly,
possibly breaking things in OpenXPKI. Sergei once mentioned that the changes were so deep that fixing the
behaviour was not really possible without breaking backwards compatibility with pre 5.10 releases.

The problem you are seeing with the serialization is very likely the result of the difference between bytes
and characters when encoded in UTF8. I know that this used to work before.
It might also point into the direction of a bug in Perl (5.10?) itself:

In December 2011 I also encountered severe problems with a superfluous UTF8-encoding applied to binary
data in Oxi with perl 5.10. This is probably unrelated, but might point in the right direction. Debian
Squeeze uses the same Perl version, possibly it is a side effect.

On the system I deployed (on Solaris 11 using Perl 5.10.1 from OpenCSW) I observed the following: all data
downloaded from the PKI system (e. g. PKCS#12, CRLs and certificates in DER format) had an additional and
superfluous UTF8 encoding applied. When manually decoding the downloaded binary file from UTF8 to
latin1 the data was perfectly OK.

After hours of debugging I could pinpoint the exact position in the code where this happens:

in trunk/perl-modules/core/trunk/OpenXPKI.pm, method read_file() you will find

my $result = do {

(Continue reading)

headers

Martin Bartosch | 8 Feb 13:51

Re: Issues with DBI/SQL abstraction layer

Hi Oli,

> I have a (severe?) issue with the SQL abstraction layer. I am using mysql as backend and unfortunatly no
clue about other rdbms - therefore I need some feedback if the issue is mysql only or not.
> 
> Problem:
> I am searching a certificate by its subject, the subject I am looking for contains a backshlash character.
> 
> my $issuer_dn = "OU=Trustcenter,O=Snakeoil\, Inc.,C=US";
> CTX('dbi_backend')->first(
>  TABLE   => 'CERTIFICATE',
>  COLUMNS => [ 'IDENTIFIER' ],
>  DYNAMIC => {
>    'SUBJECT' => $issuer_dn,
>    'PKI_REALM' => $pki_realm
> });

I discussed this with Oli, we think the situation is not very easy to solve. The problem here is that the quote
character in fact quotes the string for OpenSSL, not for the database. If we simply quote the query string
we are effectively also qouting all SQL wildcards a search might want to use (and the frontend does this liberally).
Oli has proposed to introduce heuristics which determine if SQL wildcards are used and to use like in this
case, equality match otherwise.

We will be testing this in dev before it gets committed.

If anybody has a decent idea how to handle this properly please comment...

cu

Martin

(Continue reading)

headers

Sergei Vyshenski | 8 Feb 21:07

Re: Problems with Umlaut / UTF8

Hi Oliver,

Sorry no Umlaute here, but still...

- Exact version of openxpki?
- Exact version of perl?
- Exact version of Apache?
- Exact version of openssl?
- Exact version of your browser while composing CSR?
- Which language is default in your browser?
- Your mysql 5.1. Have you uft-8'ised it following this advice:

http://www7.openxpki.org/docs/howto/OpenCA_MySQL_UTF8.txt

And now the main questions.

How exactly you are trying to issue certificates?
Have you composed CSR via web interface of the OpenXPKI?
Can you see Umlaute in the CSR? During composing? And after submitting?
And the same Umlaute are distorted when it comes to view the cert?
How do you view this cert? Via your browser and via web interface of the
OpenXPKI,
or with the openssl executable after downloading?

All the best, Sergei

On 08.02.2012 14:56, Oliver Welter wrote:
> I was trying to issue certificates using german Umlaute and run into
> several problems...
>

(Continue reading)

headers

Oliver Welter | 9 Feb 09:07

Re: Problems with Umlaut / UTF8

Hello Sergei,

nice to hear from you ;)

> - Exact version of openxpki?
Somewhat ahead of the current development branch 
(https://github.com/oliwel/openxpki/tree/feature/nice).

> - Exact version of perl?
This is perl, v5.10.1 (*) built for x86_64-linux-gnu-thread-multi
Default packages of Debian Squeeze

> - Exact version of Apache?
Apache/2.2.16 (Debian)

> - Exact version of openssl?
OpenSSL 0.9.8o 01 Jun 2010

> - Exact version of your browser while composing CSR?
Firefox 10.0 <at> Ubuntu 64bit / Shell Test Scripts with OpenXPKI::Client

> - Which language is default in your browser?
de.utf8
> - Your mysql 5.1. Have you uft-8'ised it following this advice:
> http://www7.openxpki.org/docs/howto/OpenCA_MySQL_UTF8.txt
This does no longer apply to mysql 5.1 - but I am sure that the db is 
working correct

> And now the main questions.
>

(Continue reading)

headers

Sergei Vyshenski | 9 Feb 18:47

Re: Problems with Umlaut / UTF8

Hi,

A couple years ago when we here last investigated this utf-8 related
problems, it seemed that a solution was found, which _should_ work with
different perls.

Today we should test this solutinon anew with 5.10 and maybe 5.14, after
which it could be backported from our local branch to the current svn
branch.

Martin! Is it ok to take the latest svn snapshot as a base for these
experiments and possible commits?

All the best, Sergei

On 08.02.2012 16:46, Martin Bartosch wrote:
> Interesting problem, and I am not sure why this happens. Originally we designed OpenXPKI to be fully
UTF8-compliant. I also know that it was used by our Russian friends to some success, and they also kept a
keen eye on keeping the code UTF8-aware. However, I remember Sergei writing to the list that some problems
occurred with later commits regarding UTF8 support. 
> It also seems to be an annoying truth that the semantics of handling UTF8 in Perl has changed
significantly, possibly breaking things in OpenXPKI. Sergei once mentioned that the changes were so
deep that fixing the behaviour was not really possible without breaking backwards compatibility with
pre 5.10 releases.
> 
> The problem you are seeing with the serialization is very likely the result of the difference between
bytes and characters when encoded in UTF8. I know that this used to work before.
> It might also point into the direction of a bug in Perl (5.10?) itself:

------------------------------------------------------------------------------

(Continue reading)

headers

Martin Bartosch | 10 Feb 09:09

Re: Problems with Umlaut / UTF8

Hi Sergei,

> A couple years ago when we here last investigated this utf-8 related
> problems, it seemed that a solution was found, which _should_ work with
> different perls.
> 
> Today we should test this solutinon anew with 5.10 and maybe 5.14, after
> which it could be backported from our local branch to the current svn
> branch.
> 
> Martin! Is it ok to take the latest svn snapshot as a base for these
> experiments and possible commits?

this sounds great, I'd be happy to see your solution. I've been banging my head over the UTF8 thingy in
read_file (and it is impacting at least one productive installation).

Yes, the SVN head would be a good start to fork of. If it is possible and acceptable for you I would suggest
using GitHub for the experimental stuff. The easiest way to do this is to fork off the project
https://github.com/openxpki/openxpki in your GitHub account, create a branch from master and commit
your changes in your own GitHub repo. Then way we can simply pull in the changes here.

Another way of course is a simple diff against the SVN head, works fine as well.

cu

Martin

------------------------------------------------------------------------------
Virtualization & Cloud Management Using Capacity Planning
Cloud computing makes use of virtualization - but cloud computing

(Continue reading)

headers

Sergei Vyshenski | 10 Feb 10:20

git and svn

Hi Martin,

Sorry for hitting this point again, but lastmidnight generator is stuck:

http://www7.openxpki.org/lastmidnight/index.html

For use with lastmidnight generator
I have a working copy of a "git snapshot" which was created with a command:

git clone git://openxpki.git.sourceforge.net/gitroot/openxpki/openxpki

To update this snapshot nightly from a SF repository I used to issue
command:

git svn rebase

which now says:

Unable to determine upstream SVN information from working tree history

What is a methodical way to update my git snapshot?

All the best, Sergei

------------------------------------------------------------------------------
Virtualization & Cloud Management Using Capacity Planning
Cloud computing makes use of virtualization - but cloud computing 
also focuses on allowing computing to be delivered as a service.
http://www.accelacomm.com/jaw/sfnl/114/51521223/

(Continue reading)

headers

Martin Bartosch | 10 Feb 10:26

Re: git and svn

Hi Sergei,

> For use with lastmidnight generator
> I have a working copy of a "git snapshot" which was created with a command:
> 
> git clone git://openxpki.git.sourceforge.net/gitroot/openxpki/openxpki
> 
> To update this snapshot nightly from a SF repository I used to issue
> command:
> 
> git svn rebase
> 
> which now says:
> 
> Unable to determine upstream SVN information from working tree history

Try the following: enter the top level of the repository tree. You should find a directory .git in this directory.

Edit .git/config

Check if you have got a section that looks like this:

[svn-remote "svn"]
        url = https://mbartosch-V/rW0nbxQoA7kfSZdNC4/qQD96bmaF075NbjCUgZEJk <at> public.gmane.org/svnroot/openxpki
        fetch = :refs/remotes/git-svn

If this does not exist, add it. Replace the user name with yours on SF or replace the URL with the read-only SVN
base URL.

Once you have done this the command "git svn rebase" should work properly.

(Continue reading)

13	May 2012
31	April 2012
49	March 2012
20	February 2012
6	January 2012
1	December 2011
5	November 2011
5	September 2011
3	August 2011
1	July 2011
6	May 2011
2	February 2011
1	November 2010
2	October 2010
8	August 2010
15	April 2010
5	March 2010
2	January 2010
6	December 2009
5	June 2009
2	April 2009
6	March 2009
5	February 2009
7	January 2009
4	November 2008
2	September 2008
5	August 2008
15	July 2008
5	June 2008
1	May 2008
8	April 2008
1	January 2008
6	December 2007
11	November 2007
23	October 2007
3	September 2007
15	August 2007
17	July 2007
33	June 2007
12	May 2007
32	April 2007
2	March 2007
30	February 2007
36	January 2007
9	December 2006

Mon	Tue	Wed	Thu	Fri	Sat	Sun

		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29