MySQL: escape a string for use in a SQL statement

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.

2 Comments so far

  1. Vitaliy on December 15th, 2008

    good stuff!

  2. Ben Nadel on October 31st, 2017

    Ha – this is exactly what I was looking for – a way to generate SQL INSERT statements. Awesome!

Leave a reply