I have an ESQLC program that handles SQL statements as strings.
It executes all non-SELECT statements with an EXEC SQL EXECUTE
IMMEDIATE :SQLStmt statement.

This works fine except for strings that include single quotes

e.g.

name2 = 'O''MALLEY';

SQLStmt = 'UPDATE pepl SET name2 = ' + :name2 + ' WHERE ....'

results in

'UPDATE pepl SET name2 = 'O'MALLEY' WHERE ....'

which will fail

Has anyone out there ever come up with an algorithm for processing a
string like this to escape the correct single quote that will work
with ANY sql statement ?


Colin Hay

On Mar 27, 12:48*am, Colin Hay <wobb... (AT) hotmail (DOT) com> wrote:
Hi Colin,
I used the single quotes' ASCII representation X'27' to produce the
following :

name2='O'+X'27'+X'27'+X'27'+X'27'+'MALLEY';
SQLStmt='UPDATE mypal SET epwnymo = '+X'27'+ :name2 + X'27'+ '
WHERE .......';

which interpolated is: UPDATE mypal SET epwnymo = 'O''''MALLEY' WHERE
and inserts O''MALLEY in the table

On Mar 26, 11:48*pm, Colin Hay <wobb... (AT) hotmail (DOT) com> wrote:
Basically you have to replace each occurance of a single quote in
variable name2 by two single quotes.

As I know you are programming with OpenROAD here is an example:

SQLStmt = 'UPDATE pepl SET name2 = ' +
ReplaceText(text = name2, match='''', replacement = '''''') + '
WHERE ....';

This uses 4GL Procedure "ReplaceText" having the following code:

procedure ReplaceText(
text = varchar(2000) not null,
match = varchar(256) not null,
replacement = varchar(256) not null
)=
DECLARE
pos = integer not null;
v = varchar(2000) not null;
newtext = varchar(2000) not null;
ENDDECLARE
BEGIN
v = text;
while (length(v) > 0) do
pos = locate(v, match);
newtext = newtext + left(v, pos-1);
if pos <= length(v) then
newtext = newtext+replacement;
endif;
v = shift(v, -(pos+length(match)-1));
endwhile;
return newtext;
END

HTH,
Bodo.

On Mar 27, 5:17*pm, Bodo <Bodo.Bergm... (AT) ingres (DOT) com> wrote:
Slight correction - should set the single quotes around the name
(was also missing in your original example).:

SQLStmt = 'UPDATE pepl SET name2 = ''' +
ReplaceText(text = name2, match='''', replacement = '''''') +
''' WHERE ....';

This is fine if I know only pepl.name2 can have single quotes and only need to code explicitly for that column

However any varchar could have a quote in it - i.e. descriptions, addresses etc

Every SQL statement is being executed in C from a common handler OpenROAD procedure so
ideally what I want is a routine in that procedure that takes any SQL statement string, does some some semantic checking, adding the extra quote where deemed necessary

My first attempt counted the number of quotes delimited by a comma or '='
Where I can count up to 3 quotes I escape the 2nd so 'O'MALLEY' becomes 'O''MALLEY'
the problem then was that 'O'MALLEY, SEAMUS' doesn't work because my algorithm doesn't realise that the comma is part of the string ...

I guess I should then delimit by ' followed by any number of spaces followed by ,
I was just wondering if anyone had had this problem before and had a working solution before I spend any more time on this

The alternative is to have to scan the existing data in every char column and narrow it down to a list of possible problem columns
and explicitly code them ...


"Bodo" <Bodo.Bergmann (AT) ingres (DOT) com> wrote

In that case you can you regular expressions to substitute each quote
with its counterpart to the extend that you can substitute all quotes
except the first and last one.
You can use an external C regex library such as the Perl compatible
one PCRE
(www.pcre.org).
Depending on the platform you could fire up a shell and use sed or tr
or their GNU counterparts
to processes the string but that would require to pass the resulting
string back to your program so it might be better to do the processing
from within your C program by using the lib


Ï óõíôÜêôçò Colin Hay Ýãñáøå:

You could use use prepared queries instead, i.e.

strcpy(name2,"O'MALLEY");
....
EXEC SQL prepare updatestmt from 'UPDATE pepl SET name2 = ? where ...
";
EXEC SQL execute updatestmt using :name;

In this way there is need to look for any quotes in the values.

Regards
Kristoff



On Mar 27, 12:48*am, Colin Hay <wobb... (AT) hotmail (DOT) com> wrote:

On Mar 28, 6:49*am, "Colin Hay" <colin... (AT) bigpond (DOT) net.au> wrote:
Colin,
why can't you use the ReplaceText() function on any varchar value
(descriptions, addresses etc )when adding it to the query?