Re: Problem with character ' (single quote) in text fields

On Tue, Jan 22, 2002 at 10:27:24AM +0100, Jordi wrote:
> Yes, this is the method I use but the problem is that I need to parse/change
> every text.  For example, if I have a struct Person with a char *Firstname =
> "D'Innocenzo" I can not use:
>
>   sprintf (output, "select X where FirstName=%s and Description=%s",
> Person.Firstname, Person.Description)
>
> so I need a lot of temporary buffers just to create the SELECT... and some
> of the structures I need to dump to the database have lots of text fields...
>
> Anyway, it is not a big problem but I tried to find a workaround  but I
> think it doesn't exist.

I beleive that the DB interface layers do this for you if you use the
prepare type statements. If I really had to do this a lot I would consider
writing my own function like this:

mydbsprintf( output, "select * from t where firstname = '$' and description = '$';",
          "D'Innocenzo", "blah" );

Then that function can do the escaping for you. Then you only need one
buffer to store the final query before executing it.

Actually, I've thought that we could take a leaf out of Perl's book w.r.t.
generalised quoting. Like q<delimeter>blah<delimeter>. Then you could write
queries like:

select * from t where firstname = q[D'Innocenzo] and description = q:blah:;

Since square brackets are much rarer than single quotes, it's just not an
issue. Thoroughly non-portable though. Another possibility would be to quote
by length instead. Maybe q@<number>@<n bytes>@. Then you get:

select * from t where firstname = q@11@D'Innocenzo@ and description = q@4@blah@;

Ofcourse the strings would be allowed to have embedded @ symbols.

Just silly ideas to throw around. Implementationwise they're not difficult,
it's just deciding whether it's a good idea or not.
--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/
> Terrorists can only take my life. Only my government can take my freedom.