|
|
Posted by Jerry Stuckle on 07/16/07 00:31
Andy Hassall 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.
>
>> 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.
>
No back ticks are for quoting table and column names. Double quotes are
a non-standard MySQL extension to the spec (and will fail if MySQl is
running in strict ANSII SQL mode).
> 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."
>
That does not mean it is a column or table name.
>> 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.
>
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================
Navigation:
[Reply to this message]
|