Posted by Gary L. Burnore on 07/15/07 23:05

On Mon, 16 Jul 2007 00:03:26 +0100, Andy Hassall <andy@andyh.co.uk>
wrote:

>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.

Funny answer: Point at something behind them and, when they look,
run!

>
>>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.
--
gburnore at DataBasix dot Com
---------------------------------------------------------------------------
How you look depends on where you go.
---------------------------------------------------------------------------
Gary L. Burnore | ÝÛ³ºÝ³Þ³ºÝ³³Ýۺݳ޳ºÝ³Ý³Þ³ºÝ³ÝÝÛ³
| ÝÛ³ºÝ³Þ³ºÝ³³Ýۺݳ޳ºÝ³Ý³Þ³ºÝ³ÝÝÛ³
Official .sig, Accept no substitutes. | ÝÛ³ºÝ³Þ³ºÝ³³Ýۺݳ޳ºÝ³Ý³Þ³ºÝ³ÝÝÛ³
| ÝÛ 0 1 7 2 3 / Ý³Þ 3 7 4 9 3 0 Û³
Black Helicopter Repair Services, Ltd.| Official Proof of Purchase
===========================================================================

[Back to original message]