3

I'm using PHP with MySQL to make a basic search engine on my website to look up books that people post, but the problem is that I'm using something for example:

`title` LIKE '%".(string)$searchTerm."%'

NOTICE: The variable $searchTerm is being escaped before-hand.

I know this is widely used and works pretty good, but the problem is that, if I have a book title for example:

Writer's Reference

The result(s) would show, if someone searches for "Writer's Reference" (with no double quotes) But, if someone looks up just "Writers Reference" the result won't show up, which is why I'm here asking what may be a good idea to resolve this.

Using AJAX to query every time on every onkeyup event? Collecting every output into an array? But that might slow-down the process. Is there a way in MySQL that filters and ignores apostrophes, but at the same time, if someone uses an apostrophe it would work? A regex operation?

Thanks.

EDIT: I'm not trying to prevent SQL Injections, I'm looking for a way that if someone looks up a word without an apostrophe, the result with an apostrophe shows up, but at the moment with the LIKE %% won't show up, because it takes direct words from the database. So if the search term does not have an apostrophe and the result has one, it won't show up.

EDIT 2: As I followed the comments, I recently updated the two columns in the database to use FULLTEXT via ALTER TABLEdbname.bookADD FULLTEXT (title,description)

But after so, I'm using a switch case to see if the user is selecting to search in the title or description, or both, both work, as I'm using MATCH(title,description) AGAINST('".(string)$sTerm."') but the others that look in the title or description only won't work as planned, of course, I'm having something like: MATCH(title) AGAINST('".(string)$sTerm."') but won't work unless I make it MATCH(title,title) AGAINST('".(string)$sTerm."') (Adding another title in the MATCH)

Also, still, when someone searches for "Writes Reference" without the apostrophe, it still won't work for some reason, might it be my php code?

Death.System
  • 144
  • 1
  • 9
  • [The Great Escapism (Or: What You Need To Know To Work With Text Within Text)](http://kunststube.net/escapism/) – deceze Feb 15 '14 at 08:29
  • Or are you saying you have the text "Writer's reference" in your database and you want it to match even if someone is searching for "Writers reference"? It's pretty unclear what the issue is exactly. – deceze Feb 15 '14 at 08:31
  • Yeah sorry about that, I typed it in a rush without going over it, I edited it and added what I was after, yes, basically asking if there's a way to show the result with an apostrophe if someone searched the title without an apostrophe. – Death.System Feb 15 '14 at 08:32
  • Like will match the input string to the col as if you supply LIKE %Writers Reference% you are asking to find the result contains "Writers Reference" anywhere in your column. So it will not get "Writer's Reference". – Abhik Chakraborty Feb 15 '14 at 08:37
  • 1
    I think you'll need to look into fulltext indices. – deceze Feb 15 '14 at 08:40
  • Yes fulltext index would help even if it fails for Writers it will find the matching result "Reference" – Abhik Chakraborty Feb 15 '14 at 08:42
  • So, would I be using something like MATCH and AGAINST? – Death.System Feb 15 '14 at 08:46
  • Yes thats correct !! But make sure to create the index for the column https://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html – Abhik Chakraborty Feb 15 '14 at 08:52
  • Well it works in some cases, but still won't work as I planned, also it broke my other methods (using MATCH AGAINST to only find something from only the title or description, that is) – Death.System Feb 15 '14 at 10:40
  • I really don't understand why this is a duplicate where I'm not even asking anything about SQL Injections... – Death.System Feb 16 '14 at 14:56

1 Answers1

0

You could use something like:

REPLACE(`title`, '\'', '') LIKE REPLACE('%".(string)$searchTerm."%', '\'', '')

... with the drawback that MySQL will not be able to use the indexes defined on title.

Razvan
  • 2,436
  • 18
  • 23