Escaping single quotes - odd behavior

rtdev

Consider this SQL statement:
SELECT x from y WHERE col1 = '\'abc\' is not allowed';

I am trying to submit the above statement to my database using PHP 5 with odbc. When I run this using odbc_exec it returns no rows.

However if I enter this statement into isql (the unixodbc client query tool) then it runs just fine.

Now, to get the above sql statement to run from php with odbc_exec, I have to change the '\ to '' (two single quotes). So the statement becomes:
SELECT x from y WHERE col1 = '''abc'' is not allowed';

The problem with two single quotes is that I really do not want to be passing around unescaped quotes for fear or sql injection vulnerabilities.

So why do you think \' will not work when passed through php and odbc_exec when it works just fine when typed into a sql query tool like isql?

Thanks!

laserlight

rtdev wrote:
The problem with two single quotes is that I really do not want to be passing around unescaped quotes for fear or sql injection vulnerabilities.

But the quotes are escaped. They are escaped by doubling them.

rtdev

I got a look at what the database is receiving from the odbc_exec statement.

It seems it is sending this:

"\'abc\' is not allowed"

Instead of:
"\'abc\' is not allowed"

So something in odbc_exec (or whatever it is doing internally) looks like it is trying to be smart and help me out by encoding the single quote. But it doesn't realize that the quote is already escaped. Hence it is messing up the query.

Any thoughts on how to work around this so that \' will work instead of '' (two single quotes)?

laserlight

rtdev wrote:
It seems it is sending this:

"\'abc\' is not allowed"

Instead of:
"\'abc\' is not allowed"

Uh, they look the same to me.

rtdev wrote:
So something in odbc_exec (or whatever it is doing internally) looks like it is trying to be smart and help me out by encoding the single quote. But it doesn't realize that the quote is already escaped. Hence it is messing up the query.

I suggest that you double check: are you sure that quotes are supposed to be escaped by prepending a backslash?

rtdev wrote:
Any thoughts on how to work around this so that \' will work instead of '' (two single quotes)?

If I remember correctly, standard SQL calls for escaping quotes by doubling them. Prepending a backslash is a non-standard extension allowed by some database vendors, e.g., MySQL.