Thread: Escaping strings

Hi,

what's the postgresql equivalent of 

mysql_real_escape_string()

to escape strings that are going to be passed to queries?

(http://www.mysql.com/doc/n/o/node_641.html)

Thanks

Daniel

> what's the postgresql equivalent of 
> 
> mysql_real_escape_string()
> 
> to escape strings that are going to be passed to queries?

There doesn't seem to be a function to do this in libpq, which I find
slightly odd.

DBD::Pg has quote() function as per usual for perl's DBI, but that's
not a lot of help for C. For reference it only doubles single quote
characters ' to '' and backslash characters \ to \\.

What I do -- and this may not be correct, so I encourage the more
knowledgeable to speak up! -- is this:

1. single quotes ' become '' (typical SQL)

2. PostgreSQL supports backslash escape sequences, so unless your  input uses these protect \ as \\.

3. I translate nul, formfeed, newline, and carriage return characters  to \0, \f, \n, and \r respectively.
  In comparison mysql_real_escape_string() omits \f but also escapes  ^Z and ".

For binary data probably other control characters need to be escaped
as well.  I'm not clear on this yet, but with TOAST in 7.1 I'm sure
there'll be more interest in storing arbitary binary data.

Regards,

Giles