Avoiding a single quote from an ODBC request

Question

Possible duplicate:
The correct way to avoid data entry before switching to ODBC

The error that I get from the ODBC request is this:

(pos: 72 '...M = 'Owen O'^Donavon' AND...') - syntax error 

and when I try to avoid this:

 (pos: 73 '... = 'Owen O\'^Donavon' AND...') - syntax error 

the ^ means it breaks down into

I tried the following:

 NAM = '".$var."' 

And also this:

 NAM = '".mysql_escape_string($var)."' 

then i despaired

 NAM = \"".$var."\" 

Where $ var is any name containing "in it".

if you need the whole request:

 UPDATE TABLE SET COLUMN1 = 'ERR' WHERE COLUMN_NAM = '".mysql_escape_string($var)."' AND COLUMN7 = 0"); 

Does anyone know how I can get a quote that is correctly escaped?

Answer 1

To include a single quote in a MySQL string literal (which is limited to single quotes), use two single quote characters. eg.

 'I don''t like it' 

In fact, when MySQL parses this, it will see two single quote characters and will interpret it as one single quote inside a literal, rather than seeing the "end" of a string literal.

But (as you will know), when you have only one quote, the MySQL parser has a chic approach to it. Consider this example:

 'I don't like it' 

What the MySQL parser sees is a five-character string literal containing 'I don' . Then MySQL sees that it is literal, followed by a few more tokens that need to be parsed: t like it . The parser does NOT see this as part of a string literal. This previous single quote marked the end of the string literal.

So, the MySQL parser cannot create headers or tails of what should be t like it . He sees the only quote following these tokens as the beginning of another string literal. (That way, you can be very smart about what appears there, and manage to get what MySQL understands ... and it will probably be even worse.)

(NOTE: This problem does not apply to ODBC; it affects clients that use string literals in the MySQL query text.)

---

One way to avoid this problem is to use bind variables in the request text, as well as string literals. (But with MySQL, what happens anyway is that escaping, that which is sent to the MySQL server (backstage, so to speak), is a string literal.

Sometimes we need to include string literals in the query text, and we won’t need to use bind variables as a workaround. So it's good to know how to “avoid” a single quote inside a string literal enclosed in single quotes.