Alex Stetsenko

There is a good MySQL string function QUOTE(str) which quotes a string to produce a result that enclosed by single quotes and with each instance of single quote (“’”) or backslash (“\”) preceded by a backslash.

Here is detailed description and simple usage example. But I found this function very helpful in dynamic SQL where string variable is used:

SET @SQL = CONCAT('INSERT INTO SomeTable (Field0, Field1, Field2)
SELECT ', QUOTE(someStringVariable), ', Field1, Field2
FROM AnotherTable');
 
PREPARE query FROM @SQL;
EXECURE query;
DEALLOCATE PREPARE query;

Here using QOUTE(str) helps to avoid writing SQL like below (no worry about quotes):

SET @SQL = CONCAT('INSERT INTO SomeTable (Field0, Field1, Field2)
SELECT \'', someStringVariable, '\', Field1, Field2
FROM AnotherTable');

as well as SQL statement will not fail in case someStringVariable contains single quote or backslash.