Re: C API, PQconnectdb and options Q.

On Wednesday 10 September 2003 18:47, D. Stimits wrote:
>
> I found a partial answer to this...it seems to be a bug in
> PQescapeString(). Turns out that if I do something with an insert using
> quotes for a varchar field, e.g.:
> INSERT INTO foo VALUES ('bar')
>
> ...then it escapes this to:
> INSERT INTO foo VALUES (''bar'')
>
> It doesn't like the pair of single quotes.
>
> But I also can't do this, due to requirements of SQL syntax:
> INSERT INTO foo VALUES (bar)
>
> How can I use PQescapeString() with input I would like to make somewhat
> safer via escaping? How would I use PQescapeString() without writing my
> own replacement that works with inserts?

I think the idea is to escape just the parameters to the SQL statement. So
(not in C syntax):

query = "INSERT INTO foo values ('" + PQescapeString("O'Neill") + "')"

This will double the single-quote in O'Neill.

I tend to use higher-level languages where this sort of thing is handled by
functions in e.g. Perl's DBI layer. If there isn't a suitable interface layer
available, you could write your own that does something like:

query = build_sql("INSERT INTO foo values (?,'?')", 1, "fred");

I can't believe there isn't something like this available though.

--
Richard Huxton
Archonet Ltd