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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
