Database Reference
In-Depth Information
SELECT
. One of the other examples shown here demonstrates how to execute a
SE
LECT
statement using placeholders.
Processing of special characters and
NULL
values comes up in other contexts covered
elsewhere:
• The placeholder and quoting techniques described here are
only
for data values and
not for identifiers such as database or table names. For discussion of identifier
quoting, refer to
Recipe 2.6
.
•
Comparisons of
NULL
values require different operators than non-
NULL
values.
Recipe 3.6
discusses how to construct SQL statements that perform
NULL
compar‐
isons from within programs.
• This section covers the issue of getting special characters
into
your database. A
related issue is the inverse operation of transforming special characters in values
returned
from
your database for display in various contexts. For example, if you
generate HTML pages that include values taken from your database, you must per‐
form output encoding to convert
<
and
>
characters in those values to the HTML
entities
<
and
>
to make sure they display properly.
Recipe 18.4
discusses
that topic.
Using placeholders
Placeholders enable you to avoid writing data values literally in SQL statements. Using
this approach, you write statements using placeholders—special markers that indicate
where the values go. Two common parameter markers are
?
and
%s
. Depending on the
marker, rewrite the
INSERT
statement to use placeholders like this:
INSERT
INTO
profile
(
name
,
birth
,
color
,
foods
,
cats
)
VALUES
(
?
,
?
,
?
,
?
,
?
)
INSERT
INTO
profile
(
name
,
birth
,
color
,
foods
,
cats
)
VALUES
(
%
s
,
%
s
,
%
s
,
%
s
,
%
s
)
Then pass the statement string to the database server and supply the data values sepa‐
rately. The API binds the values to the placeholders to replace them, resulting in a
statement that contains the data values.
One benefit of placeholders is that parameter-binding operations automatically handle
escaping of characters such as quotes and backslashes. This is especially useful for in‐
serting binary data such as images into your database or using data values with unknown
content such as input submitted by a remote user through a form in a web page. Also,
there is usually some special value that you bind to a placeholder to indicate that you
want an SQL
NULL
value in the resulting statement.
