0

I am searching on MySQL database using PHP and LIKE method, everything works great except when I type some characters like #, _, & it returns all rows even that there is not any row that has any of this character on it.

Other characters like /, ", ' works great and shows just the rows that has those characters.

The follow code is the code I use to get those results. 

mysql_query("SELECT * FROM articles WHERE title LIKE '%$value%'")
Prix
  • 19,417
  • 15
  • 73
  • 132
edoniti
  • 107
  • 12
  • `$value = addcslashes($value,'_%);`, [and some reading on LIKE](http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html#operator_like). Also, don't use the deprecated `mysql` extension anymore, use `mysqli` or `PDO`, and use prepared statements if you can. – Wrikken Aug 01 '13 at 23:05
  • 1
    _ is the placeholder for a single char – Philipp Aug 01 '13 at 23:06

2 Answers2

2

_ is a special character, representing any single character, in the same way that % represents any sequence of multiple characters

\ is an escape character, used if you want to search for a literal percent character \%, underscore \_ or slash \\ (unless NO_BACKSLASH_ESCAPES mode is enabled)

EDIT

See manual for details

Mark Baker
  • 209,507
  • 32
  • 346
  • 385
  • So if I use regex and replace all this characters like _ with \_ and so on... that is the solution? What about # and & as far as I know they are not special characters here at LIKE – edoniti Aug 01 '13 at 23:08
  • I can't answer about `#` or `&`, I've listed the documented special characters for MySQL, and added the link to that documentation – Mark Baker Aug 01 '13 at 23:10
  • You can only use \\ at the end of a pattern-string, otherwise, you should use \\\\. [see this fiddle](http://sqlfiddle.com/#!2/657c4/1) – Wrikken Aug 01 '13 at 23:14
  • I have escaped successfully _(underline) but still having problems with symbols like +, #, &. I am using jQuery and Get method to send the search value to PHP script, I am seeing now that this symbols are not going at PHP at all, maybe Javascript escapes them and does not send to PHP? – edoniti Aug 01 '13 at 23:25
  • In that case, make sure that you urlencode the value before using jQuery to send it to the server: but a bit of debug in our PHP script will tell you what value is being received by the server – Mark Baker Aug 02 '13 at 06:58
  • Thanks a lot, urlencode fixed everything, it seems like javascript had those characters as special and did not send at PHP at all. – edoniti Aug 02 '13 at 14:39
-4

You may try using mysql_real_escape_string() function.

http://php.net/manual/tr/function.mysql-real-escape-string.php

diegoperini
  • 1,796
  • 21
  • 37
  • I have used it for $value variable. The problem still exists. – edoniti Aug 01 '13 at 23:05
  • `mysql_real_escape_string()` is deprecated - read [here](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) –  Aug 01 '13 at 23:05
  • While one _should_ indeed escape ones variables for the deprecated mysql extension, this won't help in a LIKE scenario. – Wrikken Aug 01 '13 at 23:08