SOLVED: TZQuery.ParamByName bug: quoting apostrophe as \047

[ccezar]

Hello,

I've got PostgresQL 9.1 running on Linux machine, and I'm connecting to it using Delphi XE with Zeos 7.0.0-dev.
My Database works in UTF8, additionally I'm setting the following properities in TZConnection:

Code: Select all

con.Properties.Add('codepage=utf8');
con.Properties.Add('lc_all=Polish_Poland.utf8');

And now inserting data to database using the following code (with edtLogin.Text = '12''345':

Code: Select all

  sql.sql.Add('insert into dziennik(login,wpis,data) values (:LOGIN, :WPIS, NOW())');
  sql.Prepare;
  sql.ParamByName('LOGIN').AsString := 'dbtest';
  sql.ParamByName('WPIS').AsString := edtLogin.Text;
  sql.ExecSQL;

makes the WPIS filed in the dziennik table equal to: 12\047345. So it looks like ParamByName wrongly write the apostrophe char as \047.
Just to be sure it's ParamByName problem: I'v tested it using (in the same unit) the following code:

Code: Select all

sql.sql.Clear;
  sql.sql.Add('insert into dziennik(login,wpis,data) values (''dbtest'', ' + QuotedStr(edtLogin.Text) + ', NOW())');
  sql.ExecSQL;

And it made proper insert into database, eg: 12'345. Am I doing something wrong or is it really bug in ParamByName?
best regards,
Cezar

[ccezar]

Any ideas? Patches? Criticism?

[ccezar]

OK, the following code is the minimal code example to reproduce bug in ParamByName:

Code: Select all

junk := 'aaa''aaa';
  sql.sql.Clear;
  sql.sql.Add('insert into dziennik(login,wpis,data) values ('+quotedstr(junk)+', :WPIS, NOW())');
  sql.Prepare;
  sql.ParamByName('WPIS').AsString := junk;
sql.ExecSQL;

Inserting string directly give proper ' sign, using ParamByName give \0-47, here is a shot from SLQMonitor log:

Code: Select all

2011-11-01 13:36:33 cat: Connect, proto: postgresql-8, msg: CONNECT TO "xxxx" AS USER "xxxxx"
2011-11-01 13:36:33 cat: Execute, proto: postgresql-8, msg: SET CLIENT_ENCODING TO 'utf8'
2011-11-01 13:36:33 cat: Execute, proto: postgresql-8, msg: insert into dziennik(login,wpis,data) values ('aaa''aaa', 'aaa\047aaa', NOW()

So obviously ParamByName is coding an UTF8 version of ' sign as a series of chars: \047, not as it should: ''
What's interesting: there is no problem at all with other "special" chars like Polish or German umlauts, in both ways there are coded fine. So it looks like UTF8 subsytem works fine, and someone just made an mistake with apostrophe char.

Patch please...

[ccezar]

Tested also on SVN rev. 956 - same behavior

[papelhigienico]

The query returns a error? The query inserts data on table?

[ccezar]

Well, as I've wrote above: query works fine and inserts data without any errors or warnings. The problem is that when I use ParamByName the apostrophs inside the parameter string are not quoted as double apostrophe ('' ) but as ASCII literal: '\047' (just four ASCII chars, not char with \047 code!). All other "special" chars works fine

[ccezar]

I've tested this code against MySQL 5.1 working on Linux server (withe the sdame client written in DelphiXE running on Windows XP) and everything works fine. So it looks like it's Postgres releated problem. Just too be sure I'll test it with sqlite and then will start digging in the code looking for bug

[ccezar]

Confirmed: under Sqlite3 the above code works perfectly giving expected results.
Well, so it looks like it's time to bug hunt for me!

[cnliou]

Confirmed: under Sqlite3 the above code works perfectly giving expected results.
Well, so it looks like it's time to bug hunt for me!

The following message explains the reason:

https://support.zabbix.com/browse/ZBX-4145

Quick and dirty temporary work around is explicitly turning off the following parameter:

Code: Select all

standard_conforming_strings = off

[ccezar]

Cnliou,

Thank you very much - it's works!

By the way: it's sad to said but PostgresQL team since few versions doesn't take care about previous version compatibility, remember enabling strong type checking in 8.xx?

Thank you once again!