MySQL Cookbook, 2nd Edition

Handling Special Characters and NULL Values in Statements

Problem

You need to construct SQL statements that refer to data values containing special characters such as quotes or backslashes, or special values such as NULL. Or you are constructing statements using data obtained from external sources and want to avoid being subject to SQL injection attacks.

Solution

Use your API’s placeholder mechanism or quoting function to make data safe for insertion.

Discussion

Up to this point in the chapter, our statements have used “safe” data values that require no special treatment. For example, we can easily construct the following SQL statements from within a program by putting the data values literally into the statement strings:

SELECT * FROM profile WHERE age > 40 AND color = 'green'

INSERT INTO profile (name,color) VALUES('Gary','blue')

However, some data values are not so easily handled and can cause problems if you are not careful. Statements might use values that contain special characters such as quotes, backslashes , binary data, or values that are NULL. The following discussion describes the difficulties caused by these types of values and the proper methods for handling them.

Suppose that you want to execute this INSERT statement:

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 statement becomes syntactically ...