0

I have a query that contains some characters that cause a syntax error because contains reserved characters and I am battling to understand how to escape the string correctly.

The query is:

SELECT * FROM `products` 
WHERE MATCH (code, description) 
AGAINST (UPPER(+("intel"*) +("cpu"*)) IN BOOLEAN MODE)

But when I run this query I get the following error:

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 ') +("cpu"*)) ) IN BOOLEAN MODE)' at line 1

Okay so fine it does not like the ) as it would indicate that the AGAINST is being closed however it is not (yet). So I tried to escape it with a backslash but it still throws the same error.

If I try this in PHP using a prepared statement while binding the search string +("intel"*) +("cpu"*) into the statement it works. So it seems that the way that it escapes it is not with a backslash or that there is something else.

So I was looking at the PHP documentation for mysqlescapestring and I saw that it: "prepends backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a.".

Which indicate that the single and double quotes need to be escaped and I tried to do this but it just throws the same syntax error but on the double quote character, i.e. to use near '\"intel\"*\)...

I do understand that it would be best to use prepared statements and that this solves the problem but I just want to understand what I have done wrong here and how I could escape a string like this within an AGAINST clause as I have done here.

If anyone could suggest where I have going wrong with this then it would be greatly appreciated. Thank you.

Drew
  • 24,851
  • 10
  • 43
  • 78
Craig van Tonder
  • 7,497
  • 18
  • 64
  • 109
  • What exactly do you want to be the parameter for AGAINST()? – e4c5 Jun 16 '16 at 05:50
  • @e4c5 Hey, thanks for your response. If I understand the question, anything containing words that start with `INTEL` and `CPU`. As stated, this does work but only if I prepare it in PHP, which is obviously doing some escaping but I am not sure exactly what it is escaping and how it does that. I pass into the prepared statement the following parameter: `+("intel"*) +("cpu"*)` – Craig van Tonder Jun 16 '16 at 05:53

1 Answers1

1

Well, I managed to solve this.

I read an answer on another so question that helped me here. What I realised is that when a prepared statement includes the relative object it encapsulates it with quotes, so in actual fact UPPER(?) would become UPPER("prepared string") which means that UPPER(+("intel"*) +("cpu"*)) should actually be UPPER('+("intel"*) +("cpu"*)').

So the result is:

SELECT * FROM `product` 
WHERE MATCH (code, description) 
AGAINST (UPPER('+("intel"*) +("cpu"*)') IN BOOLEAN MODE)

Which does work without syntax errors.

As a note, if you are escaping strings in MySQL it would be worthwhile to note that MySQL uses C escape syntax in strings.

Community
  • 1
  • 1
Craig van Tonder
  • 7,497
  • 18
  • 64
  • 109