Thread: Backslash problems with 8.1.4

Hello list,

I upgraded to postgres-8.1.4 and saw all of the backslash escape changes
and understand why, but I can't figure out how to put a literal \' in
the database. If \ is no longer escaping shouldn't I be able to use \”
and have postgres ignore the \ and use standard sql syntax to escape the
single '?

It seems that no matter what I try postgres returns an error message
because it thinks I'm trying to escape the '.

The data is coming from PHP, and yes, I know that embedded SQL is bad,
but I want to disable \ escaping now since I don't use it and it will be
a little while before I can convert to PDO.

Is there any way to disable \ escaping and pass a literal \' without
postgres kicking back an error on the query?

schu

Matthew Schumacher <matt.s@aptalaska.net> writes:
> I upgraded to postgres-8.1.4 and saw all of the backslash escape changes
> and understand why, but I can't figure out how to put a literal \' in
> the database.

You use the SQL-standard way, which is to repeat the quote mark:
    'Meet at Joe''s house'

> The data is coming from PHP,

I have met your problem, and its name is addslashes().  Don't use it.
addslashes is exactly the security hole we are trying to plug.

            regards, tom lane

Tom Lane wrote:
> Matthew Schumacher <matt.s@aptalaska.net> writes:
>> I upgraded to postgres-8.1.4 and saw all of the backslash escape changes
>> and understand why, but I can't figure out how to put a literal \' in
>> the database.
>
> You use the SQL-standard way, which is to repeat the quote mark:
>     'Meet at Joe''s house'
>
>> The data is coming from PHP,
>
> I have met your problem, and its name is addslashes().  Don't use it.
> addslashes is exactly the security hole we are trying to plug.
>
>             regards, tom lane


Thanks for the reply Tom, however I don't think you understand my issue.
   I'm not using addslashes and I am using the SQL standard way to
escape a single quote. The problem is that I want to put a literal \'
inside the database.  So if \ is no longer an escape character, and ''
is the SQL way to pass a literal ' then you would think that \'' would
put a literal \' into the database, however postgres rejects this and
spits out an error.

So the question isn't how to I escape ', the question is how do I insert
a literal \' into a varchar?

Thanks,
schu

Matthew Schumacher <matt.s@aptalaska.net> writes:
> Thanks for the reply Tom, however I don't think you understand my issue.
>    I'm not using addslashes and I am using the SQL standard way to
> escape a single quote. The problem is that I want to put a literal \'
> inside the database.  So if \ is no longer an escape character, and ''
> is the SQL way to pass a literal ' then you would think that \'' would
> put a literal \' into the database, however postgres rejects this and
> spits out an error.

Oh, you're mistaken about \ ... it's still an escape character, as of 8.1.
(Beginning in 8.2 there will be a way to make it not an escape.)  So
what you need for that is \\'' inside your literal string.

            regards, tom lane