MySQL Cookbook

Including Special Characters and NULL Values in Queries

Problem

You’ve having trouble constructing queries that include data values containing special characters such as quotes or backslashes, or special values such as NULL.

Solution

Use your API’s placeholder mechanism or quoting function.

Discussion

Up to this point, our queries have used “safe” data values requiring no special treatment. This section describes how to construct queries when you’re using values that contain special characters such as quotes, backslashes, binary data, or values that are NULL. The difficulty with such values is as follows. Suppose you have the following INSERT query:

INSERT INTO profile (name,birth,color,foods,cats)
VALUES('Alison','1973-01-12','blue','eggroll',4);

There’s nothing unusual about that. But if you change the name column value to something like De'Mont that contains a single quote, the query becomes syntactically invalid:

INSERT INTO profile (name,birth,color,foods,cats)
VALUES('De'Mont','1973-01-12','blue','eggroll',4);

The problem is that there is a single quote inside a single-quoted string. To make the query legal, the quote could be escaped by preceding it either with a single quote or with a backslash:

INSERT INTO profile (name,birth,color,foods,cats)
VALUES('De''Mont','1973-01-12','blue','eggroll',4);

INSERT INTO profile (name,birth,color,foods,cats)
VALUES('De\'Mont','1973-01-12','blue','eggroll',4);

Alternatively, you could quote the name value itself within double quotes rather than ...