Re: PQescapeStringConn

On Jul 30, 2010, at 1:13 AM, Richard Huxton wrote:

> On 30/07/10 07:52, Scott Frankel wrote:
>> I have a number of very long strings that each contain many
>> instances of
>> semi-colons, single quotes, forward and back slashes, etc. I'm
>> looking
>> for an efficient and safe way to write them to my db using a prepared
>> statement.
>
> What language? From "C"?

Importing an SQL script.  eg:  \i my_script_of_prepared_statements.sql


>
>> PREPARE fooprep (VARCHAR(32), text, text) AS
>> INSERT INTO foo (name, description, body) VALUES ($1, $2, $3);
>> EXECUTE fooprep('foo1', 'this is foo1',

The full statement (below) illustrates the problem I'm encountering.
The text I'm trying to insert has single quotes and semi-colons in
it.  These get interpreted, causing errors.  I'm looking for a way to
insert strings with special characters into my db, hopefully avoiding
having to escape each one by hand.  (They are numerous and the strings
quite long.)  eg:

    INSERT INTO foo (name, body) VALUES ('foo', 'this will fail 'fer
sher;' on the characters inside the string');

Thanks again!
Scott


PREPARE fooprep (VARCHAR(32), text, text) AS
     INSERT INTO foo (name, description, body) VALUES ($1, $2, $3);
EXECUTE fooprep('foo1', 'this is foo1',
'#!()[]{};
qwe'poi'asdlkj"zxcmnb";
/\1\2\3\4\5\6\7\8\9/'
);




> This is basically PQprepare+PQexecPrepared, or PQexecParams if you
> want to do both in one step. There is no need to escape strings if
> they are passed as parameters - the library knows it's a string and
> handles that for you.
>
> Where you *do* have to worry about escaping strings is if you are
> building up a query and have e.g. a varying table-name. It's legal
> for table names to contain spaces etc. but they need to be quoted
> correctly.
>
> Every application language will have its own library, but they all
> have a similar prepare+exec option (and I think most use the "C"
> libpq interface underneath).
>
> --
>  Richard Huxton
>  Archonet Ltd
>