CODEWITHSUNDEEP
mysql
Thomas
Thomas

Reputation: 613

SQL replace function with MATCH() AGAINST()

I would like to use the replace function inside a match function, to remove \n characters before it searches matching rows. Otherwise, for example, if the text is FULLTEXT\nsearch, and the search is search, it will not match.

Here is my query (simplified) :

SELECT * FROM messages WHERE MATCH(REPLACE(body,'\\n',' ')) AGAINST ('mysearch' IN BOOLEAN MODE)

But it throws an error...

[EDIT]

After @Shadow 's answer, I tried this :

SELECT * FROM (SELECT REPLACE(body,'\\n',' ') AS rb FROM messages) AS rbody WHERE MATCH(rb) AGAINST ('mysearch');

I think the idea is correct, but I get an error ERROR 1210 (HY000): Incorrect arguments to MATCH. I think this is because I didn't index the column rb (FULLTEXT INDEX (rb)), so the MATCH () AGAINST () operation won't work.

So I update my question : How can one index a column of a subquery

Upvotes: 1

Views: 1415

Answers (2)

Thomas
Thomas

Reputation: 613

Actually, waiting for a better solution, I will just add a column raw_body to my table, where I will store the exact body (I won't escape it with real_sacpe_string, I will just manually replace " and ' by \" and \'), and I will prepare the query and bind the params. However, I don't know if it is secure enough against sqlinjection.

[UPDATE]

Actually I found out that I didn't even needed to manually escape quotes, since the prepared statement is enough to prevent sqli. So I think I will just keep this solution for the moment

Upvotes: 0

Shadow
Shadow

Reputation: 34284

The answer is that you cannot dynamically remove \n character sequence within a match() call. As MySQL manual on match() says:

MATCH() takes a comma-separated list that names the columns to be searched.

You either have to store \n differently, not as a character sequence or you need to have a separate field in which these characters are already filtered out and this additional field is used for fulltext searches.

Upvotes: 1

Related Questions