[patch_done] single quotes in queries

joycekai · Post by **joycekai** » 30.01.2013, 20:55

I just updated to Delphi 2010 from Delphi 2006. At the same time, I upgraded from Zeos 6.6.2 to 7.0.3.
Previously, when I tried to post data that contained a single quote, it posted just fine. Now it does not work. I get an SQL error.
The attachment shows the Delphi call stack for 2 different ways that I have generated errors.
1. I try to post "t'est" by typing it into a textbox that is hooked up to a DBNavigator and clicking the Post button.
The SQL statement that is generated is (I left out some non-relevant fields)
INSERT INTO customers(cust_id, fname) VALUES (9, ''t''est'')
2. I try to post "te'st" plus some other stuff after the following statement:
zqQuery.FieldByName('comments').AsWideString := memComments.Text;
The SQL statement that is generated is
UPDATE headers SET comments=''te''st'#$D#$A'...x'' WHERE head_id=2510
I think the problem has something to do with the way the single quotes are doubled.
I saw something in a previous post about using an EncodeCString function. If this is the solution, where do I get this function, or what unit do I have to include?

Post by **mdaems** » 30.01.2013, 21:45

Joyce,

Can you reproduce this using a simple console program against our test database tables? Or is it only giving errors when using components?

Not asking you to write a real test case for our test suite (which would also be fine, of course

). Just pick a table from https://sourceforge.net/p/zeoslib/code- ... gresql.sql . Then create a TZConnection and TZQuery object in code. Connect and open the query. Append, set the field values and post.

joycekai · Post by **joycekai** » 30.01.2013, 23:10

OK, I did a simple program like you asked, and got the same error. I had 2 buttons -- one that used components (that was mostly to get the right things in the uses clause) and one that did it completely in code. Both ways got the same result. I had manually added one record to the equipment table so that I could edit that record. My whole code for the unit is below.
--------------
unit Unit1;

interface

uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls, DB, ZAbstractRODataset, ZAbstractDataset, ZDataset,
ZAbstractConnection, ZConnection;

type
TForm1 = class(TForm)
ZConnection1: TZConnection;
ZQuery1: TZQuery;
Button1: TButton;
Button2: TButton;
procedure Button1Click(Sender: TObject);
procedure Button2Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;

var
Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
begin
ZConnection1.Connected := true;
if ZConnection1.Connected then
begin
ZQuery1.SQL.Clear;
ZQuery1.SQL.add('Select * From equipment');
ZQuery1.Active := true;
if ZQuery1.Active and (ZQuery1.RecordCount > 0) then
begin
ZQuery1.Edit;
ZQuery1.FieldByName('eq_name').AsWideString := 't''est';
ZQuery1.Post;
end
else ShowMessage('Query not active');
end
else showmessage('Connection not connected');
end;

procedure TForm1.Button2Click(Sender: TObject);
var
zc : TZConnection;
zq : TZQuery;
begin
zc := TZConnection.Create(self);
zc.HostName := 'localhost';
zc.Port := 5432;
zc.Database := 'test';
zc.Protocol := 'postgresql-8';
zc.User := 'biosystems';
zc.Password := 'biosystems';

zq := TZQuery.Create(self);
zq.Connection := zc;

zc.Connected := true;
if zc.Connected then
begin
zq.SQL.Clear;
zq.SQL.add('Select * From equipment');
zq.Active := true;
if zq.Active and (zq.RecordCount > 0) then
begin
zq.Edit;
zq.FieldByName('eq_name').AsWideString := 't''est';
zq.Post;
end
else ShowMessage('Query not active');
end
else showmessage('Connection not connected');

zq.Free;
zc.Free;
end;

end.

Post by **EgonHugeist** » 31.01.2013, 08:41

joycekai,

I would like to know which field type we're talking about. Is this field a varchar or a Text field?

Btw. Zeos provided the fun functon TZConnection.EscapeString for self generate statements. The encodeCstring function was an approach but did not catch all scenarios for all possible charactersets. I changed the escape behavior so postgre does the job now.

joycekai · Post by **joycekai** » 31.01.2013, 14:59

The problem occurs in both text and varchar fields.
Your answer is not at all clear. I could not find an "EscapeString" function on the connection object. I did find GetAnsiEscapeString and GetBinaryEscapeStringFromString. I tried both of those in the example by changing one statement
zq.FieldByName('eq_name').AsWideString := zc.GetBinaryEscapeStringFromString('t''est');
Both ways still generated errors.
The statement generated was
'UPDATE public.equipment SET eq_name=''~<|5|<~t''est~<|5|<~''WHERE eq_id=1'
The binary one generated the same thing, except with an 8.
Anyway, even if it did work, this will not fix the situation where I use a DBNavigator control to update the database.
I was able to do these things just fine in Zeos version 6.6.2, and it seems like a pretty basic thing.

Post by **mdaems** » 01.02.2013, 01:21

Hi,
Thanks for the test case.
I also saw the ticket you logged at https://sourceforge.net/p/zeoslib/tickets/21/

When Michaels answer wasn't really clear, please be aware that our native language isn't english at all. And I happen to know he has some non-zeoslib related activities at the moment, so he might not have been able to concentrate for 100% on your issue at that time.

Well I'm puzzled...
As you said, this is pretty basic stuff. If this would not work in most situations no way we could have gone stable on the 7.0 series.

For testing I copied on old bug report test 'for slashes and quotes' in our test suite and just replaced the AsString to AsWideString. This test was also using the equipment table. Then I pasted your code at the end.
I have no errors at all, testing 5 compilers against 11 different connections (including pg, mssql,oracle,mysql,fb and sqlite).

There are some problems with this way of testing :
- I'm not testing on D2010. Just D7->D2009 and FPC 2.7.1+Lazarus
- I don't know if you're running with special codepage settings?
- I'm testing with the 7.0-patches branch code. However, there have not yet been that much patches since 7.0.3-stable yet.
- I don't know how 7.0.3 behaves under your old compiler? Same issue?

Here's my test code. Maybe Michael can test it it using his compilers too. I think he has D2010 available. But remember, it can take a few days.

Code: Select all

{$IFDEF WITH_FTWIDESTRING}
{**
  Ticket 21.
  Incorrect transfer \' to the Query params with asWideString
}
procedure ZTestCompCoreBugReport.Ticket21;
var
  Query: TZQuery;
begin
  if SkipTest then Exit;

  if SkipClosed then Exit;

  Query := CreateQuery;
  try
    // Query.RequestLive := True;
    Query.CachedUpdates := False;

    { Remove previously created record }
    Query.SQL.Text := 'DELETE FROM equipment WHERE eq_id>=:id';
    Query.ParamByName('id').AsInteger := TEST_ROW_ID - 2;
    Query.ExecSQL;

    Query.SQL.Text := 'INSERT INTO equipment (eq_id, eq_name) VALUES (:id, :name)';
    Query.ParamByName('id').AsInteger := TEST_ROW_ID - 2;
    Query.ParamByName('name').AsWideString := 'ab''cd''ef';
    Query.ExecSQL;
    Query.ParamByName('id').AsInteger := TEST_ROW_ID - 1;
    Query.ParamByName('name').AsWideString := 'ab\cd\ef';
    Query.ExecSQL;
    Query.ParamByName('id').AsInteger := TEST_ROW_ID;
    Query.ParamByName('name').AsWideString := 'ab\''cd\''ef';
    Query.ExecSQL;

    { Opens a result set. }
    Query.SQL.Text := 'SELECT * FROM equipment WHERE eq_id>=:id ORDER BY eq_id';
    Query.ParamByName('id').AsInteger := TEST_ROW_ID - 2;
    Query.Open;
    CheckEquals(TEST_ROW_ID - 2, Query.FieldByName('eq_id').AsInteger);
    CheckEquals('ab''cd''ef', Query.FieldByName('eq_name').AsWideString);
    Query.Next;
    CheckEquals(TEST_ROW_ID - 1, Query.FieldByName('eq_id').AsInteger);
    CheckEquals('ab\cd\ef', Query.FieldByName('eq_name').AsWideString);
    Query.Next;
    CheckEquals(TEST_ROW_ID, Query.FieldByName('eq_id').AsInteger);
    CheckEquals('ab\''cd\''ef', Query.FieldByName('eq_name').AsWideString);

    query.SQL.Clear;
    query.SQL.add('Select * From equipment WHERE eq_id>=:id ORDER BY eq_id');
    Query.ParamByName('id').AsInteger := TEST_ROW_ID - 2;
    query.Active := true;
    if query.Active and (query.RecordCount > 0) then
    begin
    query.Edit;
    query.FieldByName('eq_name').AsWideString := 't''est';
    query.Post;
    end;
    { Remove newly created record }
    Query.SQL.Text := 'DELETE FROM equipment WHERE eq_id>=:id';
    Query.ParamByName('id').AsInteger := TEST_ROW_ID - 2;
    Query.ExecSQL;
  finally
    Query.Free;
  end;
end;
{$ENDIF}

joycekai · Post by **joycekai** » 01.02.2013, 16:41

When I said Michael's reply was not clear, I mostly meant that he did not provide an example of using the function, or even the correct name of the function. His English is fine, and I really appreciate that you are both paying attention to this problem.
I am in the U.S., and I just checked and made sure that my regional settings are English (U.S.). If there is something else I need to check about code page, then let me know, but as far as I know I am not doing anything weird in that regard.
I also just went into Component - Install Packages to check that the Zeos build I am using is 2010 / ZComponentDesign140.bpl. Since there is a different build for 2010 than 2009 I guess problems could occur in one and not the other.
I upgraded to Delphi 2010 so that I could do a Chinese translation of the software. When using the previous version of Zeos, there was the bug that you cannot do a multiple selection in a databound grid, so I was very glad to see that is fixed in this version. However, now I have this problem. I do not have Delphi 2009 to test it in -- I upgraded from Delphi 2006, and I can't go back because I need the Chinese now. Is Delphi 2010 the highest you have tested in, or is there a higher version now that works better?
Since your code example stored data in a slightly different way, I just tried your way in my sample program. I used the following code:
-----------
procedure TForm1.Button3Click(Sender: TObject);
begin
ZConnection1.Connected := true;
if ZConnection1.Connected then
begin
ZQuery1.SQL.Text := 'UPDATE equipment SET eq_name= :name WHERE eq_id=1';
ZQuery1.ParamByName('name').AsWideString := 'ab''cd''ef';
ZQuery1.ExecSQL;
end
else showmessage('Connection not connected');
end;
------------
When I set the parameter to 'abcdef' it worked fine.
When I set it to 'ab''cdef' or 'ab''cd''ef' it did not work fine.
I tried to run the example program in Delphi 2006 using Zeos 7.0.3, but I could not get it to compile. It decided it had to have ZAbstractConnection, but it could not resolve that unit. I don't think they had a package for Delphi 2006 (which I think is also Delphi 10). I tried it with the delphi9 and Delphi2007 packages. When it couldn't resolve the ZAbstractConnection unit I deleted and readded the controls, but it was still unhappy so I gave up.
The inner workings of Zeos are a mystery to me, but I am really hoping you can come up with a patch that I can try!

Post by **EgonHugeist** » 02.02.2013, 11:44

joycekai,

I've logged in to see the current prozess in this domain... Thanks Mark for appologize me.

I've checked your example and it works nice on my side. I know Mark uses PostgreSQL 8.3? I'm using PostgreSQL 9.1 currently. Joice i'm not able to determine your issue. I know Postgre is dealing with Standar_Conforming_Strings and ?Escape_Strings?. May i ask you which version do you use?

Is it possible the funtion PQEscapeString is not assigned on your side? You can test it if you set an break-point in ZPlainPostgreSqlDriver.pas, function TZPostgreSQLBaseDriver.EscapeString(..). I can't see another reason for your fails..

joycekai · Post by **joycekai** » 04.02.2013, 14:47

Somebody gave me a code snippet to display the PostgreSQL version, so here is what is displayed on my Help - About screen:
PostgreSQL 8.1.11 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)

I think you are probably on to something here.

I realize 8.1 is outdated and no longer supported, and probably Zeos was not planning to support it either. I was getting ready to do research on how to upgrade PostgreSQL for all my end users who already have my software. I was hoping to be able to delay this for a few months, so it would be really nice if I could get it to work with PostgreSQL 8.1.

I guess what I should do is try installing 8.3 and see if it fixes the problem.

I will also try doing the breakpoint you suggest and see what info I get from that.

If the answer is that I have to upgrade PostgreSQL, it is not exactly the answer I want, but at least I will know what is wrong, and that is much, much better than a mysterious bug.

joycekai · Post by **joycekai** » 04.02.2013, 16:43

I just tried the breakpoint you suggested, and the EscapeString function is not assigned, as you theorized.

Is there a patch I can try to get it assigned?

I would really like to keep using PostgreSQL 8.1 for a few more months.

Post by **EgonHugeist** » 04.02.2013, 19:25

joycekai,

Oh this is strange, because i can find this function is documented since PG7.3! I'll prepare a patch with the deprecated version PQEscapeString + some Zeos related functions tonight. I hope i can attach the patch tomorrow. Or do you use our SVN repository?

Post by **EgonHugeist** » 05.02.2013, 15:21

joycekai,

i've commited a patch to \testing-7.1 (SVN) R2161 which reintroduces the old Postgre internal escaping functions. I hope this will fix your issue. Can you test it please?

joycekai · Post by **joycekai** » 05.02.2013, 15:28

Is it possible to give the full URL of the repository? I have not used it before, so I don't know where it is. (Luckily, we have just started using SVN at work, so I know how to use it. I know I saw a link for it somewhere, so I will also go try to find it.)

joycekai · Post by **joycekai** » 05.02.2013, 15:49

OK, I hate to be a needy person, but...
I think I have found the correct repository at
http://svn.code.sf.net/p/zeoslib/code-0 ... g-7.1/src/
Is the only file I need at
http://svn.code.sf.net/p/zeoslib/code-0 ... Driver.pas
or do I need to get more of the files? If I need to get more files, which ones do I need to get?
While I am waiting for an answer, I will see if just getting that one file will fix the problem.

Post by **EgonHugeist** » 05.02.2013, 16:10

I propose you download the whole \testing-7.1 branch or the complete \src directory.

You can see the change log on SourceForge.net -> Subversion->browse commits. Theire you can see that some of the postgre-dbc files are changed too.