Page 1 of 1
SOLVED: TZQuery.ParamByName bug: quoting apostrophe as \047
Posted: 30.10.2011, 18:15
by 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
Posted: 01.11.2011, 12:54
by ccezar
Any ideas? Patches? Criticism?
Posted: 01.11.2011, 13:47
by 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...
Posted: 01.11.2011, 16:30
by ccezar
Tested also on SVN rev. 956 - same behavior
Posted: 01.11.2011, 17:13
by papelhigienico
The query returns a error? The query inserts data on table?
Posted: 01.11.2011, 17:55
by 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
Posted: 02.11.2011, 11:35
by 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
Posted: 02.11.2011, 11:45
by 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!
Posted: 03.11.2011, 09:16
by cnliou
ccezar wrote: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:
Posted: 03.11.2011, 11:13
by 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!