Subject Re: [IBO] escaping apostrophes
Author Helen Borrie
At 09:13 PM 23/06/2005 +0000, you wrote:
>I've read the posts about escaping apostrophes. But I can't seem to
>get it to work. I'm using Delphi 5. I've tried QuotedStr and even
>AnsiQuotedStr. I've even written code to insert the two single quotes.

You should insert *one* single quote to escape another. It's not clear
here what you tried...

>I use this in an update storede procedure and it fails silently, no

SP's with invalid statements in them don't fail silently unless you code
them that way. It must have "succeeded" somehow in the sense that the SP
received a valid argument and didn't find a match...something like that.

QuotedStr of course is a Delphi function: SP's don't have a QuotedStr()
function available.

>I have a last_updated field and trigger, and it shows the
>record was not updated.

If, for example, the statement was something like

where aColumn = :aName

and the parameter received was, e.g. 'O' when you intended it to be
'O'Reilly' then no update would occur. That gives a clue to where things
are going wrong, though, i.e. it must be on the client side, not at the

For this statement, the following syntax should take care of double-quoting
any apostrophes that come through from e.g. an edit box:

If Edit1.Text <> '' then
InputString := QuotedStr(Edit1.Text);
MyDataset.ParamByName('aName').AsString := InputString;

>If I do something in IBExpert or some other tool like SET title
>= 'Joe''s' it works. Why does the Delphi code not work?

It does work if QuotedStr is used correctly. IBExpert is written in
Delphi! So is IB_SQL, where the tool itself takes care of escaping
apostrophes for you.

The trick will be to find out just what is happening in your parameter
assignment code and correcting that.

>By the way, I had a similar problem with Delphi, the BDE, and MySQL. I
>was hoping Firebird and IBObjects would not have this problem, and
>from what I've read here, it shouldn't! Does the CharSet of the
>database have anything to do with this, maybe?

For one-byte charactersets, no. If you are using a one-byte ANSI character
set, it would be wise to use AnsiQuotedStr. For multi-byte, it might or
might not work - that depends on the character set itself and whether the
mapping of the single-quote character is Ansi-standard. Delphi
out-of-the-box does multi-byte character sets very poorly. It does not,
for example, map Unicode_FSS characters correctly.

But, before making too many assumptions about the reasons why you encounter
the same problem in several different contexts, let's take a look at your
code, to see what you have tried so far that doesn't seem to work properly.