Thread

Re: Escaping strings

Giles Lean <giles@nemeton.com.au> — 2001-03-25T21:01:33Z

> 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