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
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.If using a parameter, the code would be:
If using
$token
and$email
as string literals instead, the code could look like this: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 parameterstandard_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 thestandard_conforming_strings
setting. General you don't want to stuff literals from php variables intoE'...'
postgresql constructs, it's unnecessarily hard because of the multiple levels of quoting involved.