Database Reference
In-Depth Information
user). You must be able to handle any kind of input in a general way because you can't
predict in advance what kind of information a user will supply. It is not uncommon for
malicious users to enter garbage values containing problematic characters in a deliberate
attempt to compromise the security of your server. That is a standard technique for
exploiting insecure scripts.
The SQL
NULL
value is not a special character, but it too requires special treatment. In
SQL,
NULL
indicates “no value.” This can have several meanings depending on context,
such as “unknown,” “missing,” “out of range,” and so forth. Our statements thus far
have not used
NULL
values, to avoid dealing with the complications that they introduce,
but now it's time to address these issues. For example, if you don't know De'Mont's
favorite color, you can set the
color
column to
NULL
—but not by writing the statement
like this:
INSERT
INTO
profile
(
name
,
birth
,
color
,
foods
,
cats
)
VALUES
(
'De''Mont'
,
'1973-01-12'
,
'NULL'
,
'eggroll'
,
4
);
Instead, the
NULL
value must have no enclosing quotes:
INSERT
INTO
profile
(
name
,
birth
,
color
,
foods
,
cats
)
VALUES
(
'De''Mont'
,
'1973-01-12'
,
NULL
,
'eggroll'
,
4
);
Were you writing the statement literally in your program, you'd simply write the word
“NULL” without enclosing quotes. But if the
color
value comes from a variable, the
proper action is not so obvious. You must know whether the variable's value represents
NULL
to determine whether to enclose it within quotes when you construct the statement.
You have two means at your disposal for dealing with special characters such as quotes
and backslashes, and with special values such as
NULL
:
• Use placeholders in the statement string to refer to data values symbolically, then
bind the data values to the placeholders when you execute the statement. This is
the preferred method because the API itself does all or most of the work for you of
providing quotes around values as necessary, quoting or escaping special characters
within the data value, and possibly interpreting a special value to map onto
NULL
without enclosing quotes.
• Use a quoting function (if your API provides one) for converting data values to a
safe form that is suitable for use in statement strings.
This section shows how to use these techniques to handle special characters and
NULL
values for each API. One of the examples demonstrated here shows how to insert a
profile
table row that contains
De'Mont
for the
name
value and
NULL
for the
color
value.
However, the principles shown here have general utility and handle any special char‐
acters, including those found in binary data. (See
Chapter 19
for examples showing how
to work with images, which are one kind of binary data.) Also, the principles are not
limited to
INSERT
statements. They work for other kinds of statements as well, such as
