MySQL full text search and apostrophe

Asked May 01 '18 at 13:01

Active Apr 04 '20 at 21:29

Viewed 117 times

I'm just learning about full text search in MySQL and trying to implement it into a website thats currently using the MYSQL LIKE command.

It works really well except when someone types something like McDonald's

The apostrophe causes:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 's' IN BOOLEAN MODE)

Here is the code I'm using:

AND
        MATCH(client.clientName, client.clientDesc, offer.offerSaving, offer.offerDesc) AGAINST(+'$keyword' IN BOOLEAN MODE)    
        GROUP BY offer.idoffer ORDER BY distance ASC, offer.offerScore DESC

The McDonald's is saved in the database as McDonald's however it would be good if it could come back with with McDonald's if they type it with the apostrophe or without. Is this possible too?

edited Apr 04 '20 at 21:29

Dharman

21,838
18
57
107

asked May 01 '18 at 13:01

Daniel Robinson

1

https://dev.mysql.com/doc/refman/5.7/en/mysql-escape-string.html – in need of help May 01 '18 at 13:04
Can you mark this up as a proper answer please it works perfectly? – Daniel Robinson May 01 '18 at 13:18
1

The error is in whatever language you use to write your application. You're composing SQL by concatenating strings, while you should be passing values separately (prepared statements). – Álvaro González May 01 '18 at 13:20
Does this answer your question? [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) – Dharman Apr 04 '20 at 21:29

MySQL full text search and apostrophe

0 Answers0