1

i have a column contain string Father\'s Day If search only word father query show the result Father\'s Day but if enter string Father\'s Day Father''s Day

no result display

my query is

select * from temp where title like '%Father's%' datatype = 0

select * from temp where title like '%Father''s%' datatype = 0

select * from temp where title like '%Father\'s%' datatype = 0
Jocelyn
  • 11,209
  • 10
  • 43
  • 60
UMAIR ALI
  • 1,055
  • 5
  • 14
  • 25
  • From the top of my head, try '%Father\\\'s%'. \ is escape character, so it does not end up in your query, unless you have two of them. So \\ becomes \, and \' becomes ' – 1615903 Apr 26 '13 at 07:00
  • possible duplicate of [How to prevent SQL injection in PHP?](http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php) – Marc B Apr 26 '13 at 18:01

3 Answers3

1

Use mysql_real_escape_string to escape special characters,

$search = mysql_real_escape_string($search);

Using PDO way:

$stmt = $db->prepare("SELECT * FROM  `temp` WHERE  `title` LIKE ?");
$stmt->bindValue(1, "%$search%", PDO::PARAM_STR);
$stmt->execute()

Note: Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO, or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

Zoe
  • 27,060
  • 21
  • 118
  • 148
Rikesh
  • 26,156
  • 14
  • 79
  • 87
0

Your queries

  1. you close a quote after Father and break your query
  2. Same issue
  3. You can't escape ' inside LIKE pattern (only % and _ are escaped)

Correct query, I believe, but I didn't check, should be:

select * from temp where title like "%Father's%" AND datatype = 0
Nemoden
  • 8,816
  • 6
  • 41
  • 65
  • Database show error message #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'datatype = 0 LIMIT 0, 30' at line 1 – UMAIR ALI Apr 26 '13 at 07:01
  • I don't seee a LIMIT in your question post. Post the whole thing to tell what's wrong with tour code. And `php` code forming the query might be helpful. – Nemoden Apr 26 '13 at 07:03
  • Hold on - what is that `datatype = 0`? There should be AND between the Father-string and the datatype condition. – 1615903 Apr 26 '13 at 07:09
  • So, do you have this backslash in the database or not? Could you post `SELECT * FROM \`table\` WHERE id = \`id\`` ? – Nemoden Apr 26 '13 at 07:16
  • Father\'s Day this string in database my requirement is to search whole string – UMAIR ALI Apr 26 '13 at 07:17
0

Use this this will work sure:

select * from temp where title like "%Father\\'s%" datatype = 0;

This will work.

And for you knowledge I want to suggest you that when ever you insert that in database then always use stripslashes function to avoid the slash.

Code Lღver
  • 15,573
  • 16
  • 56
  • 75