Database Reference
In-Depth Information
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 writing the data values literally in 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 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 these values cause and the proper techniques 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 some‐
thing like
De'Mont
that contains a single quote, the statement becomes syntactically
invalid:
INSERT
INTO
profile
(
name
,
birth
,
color
,
foods
,
cats
)
VALUES
(
'De'
Mont
','
1973
-
01
-
12
','
blue
','
eggroll
'
,
4
);
The problem is the single quote inside a single-quoted string. To make the statement
legal by escaping the quote, precede it with either a single quote or 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, quote the
name
value itself within double quotes rather than within single
quotes (assuming that the
ANSI_QUOTES
SQL mode is not enabled):
INSERT
INTO
profile
(
name
,
birth
,
color
,
foods
,
cats
)
VALUES
(
"De'Mont"
,
'1973-01-12'
,
'blue'
,
'eggroll'
,
4
);
If you are writing a statement literally in your program, you can escape or quote the
name
value by hand because you know what the value is. But if the name is stored in a
variable, you don't necessarily know what the variable's value is. Worse yet, single quote
isn't the only character you must be prepared to deal with; double quotes and backslashes
cause problems, too. And if the database stores binary data such as images or sound
clips, a value might contain anything—not only quotes or backslashes, but other char‐
acters such as nulls (zero-valued bytes). The need to handle special characters properly
is particularly acute in a web environment where statements are constructed using form
input (for example, if you search for rows that match search terms entered by the remote
