Problems With MySQL Fulltext Search and Apostrophes

[matthewamzn]

I'm having a problem with a site search engine. It uses mysql fulltext. The query doesn't work if someone enters an apostophe or the '&' symbol. Anyone know how to solve this?

[jatar_k]

you would need to escape them before passing the to mysql.

what scripting language do you use? They should have built in quoting functions.

[matthewamzn]

I'm using PHP

[matthewamzn]

This is the query I'm using.

$query = "SELECT * FROM $table WHERE MATCH (name,description) AGAINST ('$var')";

$var is just the keywords collected

[txbakers]

I use ASP, so I have to do a REGEXP on every string that comes into a query to change the single quote to TWO single quotes so it doesn't crash.

I'm not sure of the syntax in PHP.

[coopster]

Syntax in PHP with MySQL would be mysql_real_escape_string(). Message #5 in this recent PHP Peer Code Review [webmasterworld.com] has an example and a more detailed discussion can be found in Preventing SQL Injection [webmasterworld.com]

[matthewamzn]

I solved the apostrophe problem by doing this:
$var = addslashes($var);

But how can I solve the '&' symbol problem. If you searched for m&m's nothing would show up.

[coopster]

Don't need to escape the & in that MySQL query. It will find your search. If it isn't working, dump your variable or even your entire query to the browser and have a look at what is going on. You can always copy and paste the query to the command line to see what errors, if any, you are getting.

[rocknbil]

We solved the quote issue by simply replacing all incoming multiple quotes for doubles. That is:

&comments=I+am+making+a+'comment'+about+your+site

foreach $key (keys %incoming) { $incoming{$key} =~ s/'+/''/g; }

This makes your select statement do

insert into tablename (comments) values ('I am making a ''comment'' about your site');

Actually I put it into the read/parse routine, but that's the idea and it works flawlessly, if you always quote your select values.