Subject Re: [firebird-support] using a parameter containing a single quote
Author Kjell Rilbe
russell@... skriver:
> Thanks Helen.
> Yes caught me out to, a single quote is allowed in email addresses to
> the left of the @ apparently.

But unusual enough to be allowed to be treated as "probably invalid
entry" when validating data entry in an email field. :-)

> I have tried QuoteStr and that fails as well.
> The behaviour you suggest is what happens if I use format to create
> the query (SELECT * FROM PERSON WHERE EMAIL = '%s'). In this case I
> get the exception the neill (the email address is
> a.o'neill@...) is an unknown token. It’s as if it sees it as
> 'a.o'neill@...

OK, so this line of code:
emailvar := QuotedStr ('a''oneill@...');
will pass this string to the function QuotedStr:
The function will put apostrophes at the ends and duplicate any
apostropes inside, returning this string:

If you then use this in Format as follows:
Format('SELECT * FROM PERSON WHERE EMAIL = ''%s''', emailvar)
the resulting string will look like this:
SELECT * FROM PERSON WHERE EMAIL = ''a''oneill@...''
That is not valid SQL, but it should complain about the a, not the

This is what we call "fnuttologi" in Swedish, where "fnutt" is slang for
apostrophes, quotes etc. and "ologi" is the same as the English suffix
"ology". :-)

I suggest you make sure your generated SQL string is correct by
displaying it somewhere, e.g. a message box, to make sure you get the
apostropes right. Do not use the watch or inspector which will show the
apostrophes inside the string escaped as '', making it hard to read.

If the SQL is correct, with or without a parameter, check what SQL
string actually gets passed to FB, either by tracing into the FB
component source code or using some tracer/logger tool, e.g. Sinática
Monitor or FbTraceManager.

I'm pretty sure the SQL string gets mangled on its way from your SQL
string to FB, i.e. by the FB component(s) you use.


Kjell Rilbe
E-post: kjell.rilbe@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64