在准备好的语句Postgres中使用字符串文字

Question

I'm using PHP and Postgresql 9.0. I wish to insert a string eg.

"TiMxji+bhCJlk9OGcYosmBpEK8K+Li1Ygut9MJWFtpT8t0MlbGgMWJ7\/SHj8PjSWXoeGRmjjAqBTPQMe"

into a column using a prepared statement. The string generated is part of an authentication system and therefore must be entered exactly. My problem is that the backslashes are interpreted as escape characters rather than literal characters. Normally I believe I would just use the E operator to denote a string literal but this just throws up errors. Is there a way I can tell Postgres that this is a string literal while using a prepared statement?

Below is a simple example statement, where $1 is the string I wish to denote as a string literal.

pg_prepare($p->db,'setToken','UPDATE users SET token=$1 WHERE email=$2'); 

Thanks for your help,

Mark

Answer 1

$1 is the string I wish to denote as a string literal.

There's a contradiction in terms here, because writing a string as a literal is the opposite of feeding it via a parameter through the $1 placeholder.

1. If using a parameter, the code would be:

   $result=pg_prepare($p->db, 'setToken','UPDATE users SET token=$1 WHERE email=$2');
   // error checking on $result skipped
   $result=pg_execute($p->db, 'setToken', array($token, $email));
   

2. If using $token and $email as string literals instead, the code could look like this:

   $query = sprintf("UPDATE users SET token='%s' WHERE email='%s'", 
                    pg_escape_string($p->db, $token),
                    pg_escape_string($p->db, $email));
   
   $result=pg_prepare($p->db, 'setToken', $query);
   // error checking on $result skipped
   
   $result=pg_execute($p->db, 'setToken', array());
   

Both methods work, but method #1 is generally considered more foolproof and efficient.

Note about the backslash character: it is not true in general that \ must be escaped. In standard SQL, backslash is a normal character that must not be escaped. For compatibility reasons, PostgreSQL has a parameter standard_conforming_strings that when ON, tells that \ is normal, and when OFF tells it's an escape character.
The default value of this parameter is OFF up to 9.0 and ON starting with 9.1.
pg_escape_string knows about this setting and will take it into account automatically (unless you use an antiquated version of the postgresql client library).

And since you mentioned the E'...' prefix notation, when using it the \ is always an escape character in the string that follows, regardless of the standard_conforming_strings setting. General you don't want to stuff literals from php variables into E'...' postgresql constructs, it's unnecessarily hard because of the multiple levels of quoting involved.