Posted by Andy Hassall on 07/15/07 23:03

On Sun, 15 Jul 2007 22:46:19 -0000, pnberry@gmail.com wrote:

>I'm trying to write a query using the mysql interface and I do not
>know how to escape the parentheses I'm using in the query.

Short answer: you don't need to, and your problem is probably somewhere else
nearby.

>Here's the query as I tested it in the mysql monitor and it works as
>it should:
>
>SELECT * FROM tblCustomers WHERE InactiveFlag = "0"

You should use single quotes for strings in SQL, not double quotes. Double
quotes are supposed to be for quoting table and column names ("identifiers").
MySQL can, in some cases, accept either, but you should stick with the right
one to avoid potential trouble, and to stay closer to standard SQL conventions.

http://dev.mysql.com/doc/refman/4.1/en/string-syntax.html

"If the ANSI_QUOTES SQL mode is enabled, string literals can be quoted only
within single quotes because a string quoted within double quotes is
interpreted as an identifier."

>AND (FirstName
>LIKE "%Paul%" or BusinessOrLastName LIKE "%Paul%" OR Id LIKE "%Paul
>%") ORDER BY BusinessOrLastname;
>
>Here is what I've tried in my PHP script (the strings $inactive &
>$search have the values "0" and "Paul" respectively):

With or without the quotes?

>$result = mysql_query("SELECT * FROM tblCustomers WHERE InactiveFlag=
>\"$inactive\"

If with the quotes, you've now got too many quotes (and they're the wrong sort
anyway).

>AND \(FirstName LIKE \"%$search%\" OR BusinessOrLastName LIKE \"%
>$search%\" OR Id LIKE \"%$search%\"\)
>ORDER BY BusinessOrLastName",$db);
>
>I've attempted to escape the ()'s in the mysql_query() function with
>backslashes \( and \) but it isn't working.

What happened? What error did you get? Always check the return value of
mysql_query(), and use mysql_error() to get more information.

> I've read the PHP manual
>about mysql_real_escape_string() but it's not clear if this will work
>or how I would apply it.

You use escaping on all the variable values you're using in the SQL.

Personally I recommend using ADOdb, since it emulates placeholders for
versions of MySQL that don't support them, so you don't do the escaping
yourself - you would write it something like:

SELECT *
FROM tblCustomers
WHERE InactiveFlag = ?
AND (
FirstName LIKE concat('%', ?, '%')
OR BusinessOrLastName LIKE concat('%', ?', '%')
OR Id LIKE concat('%', ?, '%')
)
ORDER BY BusinessOrLastname

... and then pass it three values in the Execute function, separately to the
SQL statement - these then go into the places marked with a "?" - but the key
point is that it does this in the way that's appropriate for the databases
you're connected to.

In older versions of MySQL that means escaping the values and embedding them
in the SQL. The "mysqli" interface for MySQL 4.1+ directly supports binding
values separately, and ADOdb can also use that if it's available.

http://adodb.sourceforge.net/

http://uk.php.net/mysqli

The main problem with doing all the escaping yourself is that if you get it
wrong or forget it at any point, at best you risk obscure errors, but they can
lead to SQL injection attacks which are much more serious.

--
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

[Back to original message]