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.
good stuff!
Ha – this is exactly what I was looking for – a way to generate SQL INSERT statements. Awesome!