-1

I had very bad time debugging a server 406 error and finally I found out that the reason was mysql_escape_string.

It look that this function doesn't handle well big string ( > 7685 char ) and anyway it is not quoting properly html content.

In the function definition it is written:

  • @deprecated since 5.3.0, use mysql_real_escape_string() instead

I cannot find any info on php.net related to the use of mysql_real_escape_string() as substitute of mysql_escape_string()

Here the portion of the code that is not working fine:

    $textQuoted = mysql_escape_string($text);
    $sql .= " lower('" . $textQuoted . "') LIKE CONCAT('%', lower(keyword), '%')")

UPDATE

All this is happening on Magento, that relay on Zend Framework.

Magento/Varien/Zend is using PDO so I have tried to use the quote() function provided by Varien/Zend Framework but the issue persist.

Finally the issue is related to MySql and the setting max_allowed_packet that is too low: so big strings was creating a big SQL query that was crashing the page.

note
I don't know why someone thinks this question is somehow related to SQL injection ... anyway the world is nice because everybody is different.

Thanks for the effort to the nice stackoverflow comunity

WonderLand
  • 5,494
  • 7
  • 57
  • 76
  • 11
    What you need to do is drop the `mysql_*` functions altogether. Switch to either PDO or MySQLi and use Prepared Statements. Have a look at the red box on the manual page: http://php.net/mysql_real_escape_string – gen_Eric Apr 23 '14 at 19:29
  • possible duplicate of [How can I prevent SQL injection in PHP?](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) – Bill Karwin Apr 23 '14 at 19:32
  • this is not related to prevent injection, it was causing 406 error, read the question please – WonderLand Apr 23 '14 at 19:34
  • Not sure about this, but maybe you can split the string up and apply `mysql_real_escape_string()` to each "set" of 7685 characters and concatenate it all at the end? – Joseph Apr 23 '14 at 19:35
  • mysql_real_escape_string() works well, no need to split the string ... I just want to know if it is equivalent to the previous function – WonderLand Apr 23 '14 at 19:39
  • 1
    @WonderLand: http://php.net/mysql_escape_string: This function is identical to mysql_real_escape_string() except that mysql_real_escape_string() takes a connection handler and escapes the string according to the current character set. mysql_escape_string() does not take a connection argument and does not respect the current charset setting. – gen_Eric Apr 23 '14 at 19:45
  • 1
    http://www.youtube.com/watch?v=_jKylhJtPmI This video talks about the difference between `mysql_escape_string` and `mysql_real_escape_string` (http://www.youtube.com/watch?v=_jKylhJtPmI&t=5m34s). – gen_Eric Apr 23 '14 at 19:51
  • finaly the 406 error was caused by the big query and not from the 'quote' function ... anyway it was a good occasion to dig into PDO and quote function as well – WonderLand Apr 24 '14 at 11:35

3 Answers3

3

Increase mysql setting max_allowed_packet solve the problem ... the long string was causing the current limit to be touched

WonderLand
  • 5,494
  • 7
  • 57
  • 76
1

All mysql_*() functions are deprecated, including mysql_real_escape_string():

Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: mysqli_real_escape_string() PDO::quote()

So the easiest (from a procedural re-factoring perspective) is to use mysqli_*() functions and use mysqli_real_escape_string().

AbraCadaver
  • 78,200
  • 7
  • 66
  • 87
  • @WonderLand: If you are using PDO, then you can't use `mysql_real_escape_string`. – gen_Eric Apr 24 '14 at 02:03
  • I have updated the question ... the problem was not the 'quote' function but the big query content, sorry for the confusion, I hope in someway this question helps other to figure out similar problem – WonderLand Apr 24 '14 at 11:34
-1

mysql_real_escape_string() is my preferred way of sanitizing queries in php.

Please take a look at this here.

Community
  • 1
  • 1
gKreator
  • 69
  • 4