Database Reference
In-Depth Information
When clients send input to you over the Web, you don't really know what they're sending.
If you present a form for users to fill out, most of the time they'll probably be nice and
enter the kinds of values you expect. But a malicious user can save the form to a file,
modify the file to permit form options you don't intend, reload the file into a browser
window, and submit the modified form. Your form-processing script won't know the
difference. If you write it only to process the kinds of values that well-intentioned users
submit, the script may misbehave or crash when presented with unexpected input—or
perhaps even do bad things to your database. (
Recipe 20.7
discusses what kinds of bad
things.) For this reason, it's prudent to perform some validity checking on web input
before using it to construct SQL statements.
Preliminary checking is a good idea even for nonmalicious users. If a user neglects to
provide a required value, you must present a reminder to supply one. The check might
be simple (“Is the parameter present?”) or more involved. Typical validation operations
include the following:
• Checking content format, such as making sure a value looks like an integer or a
date. This may involve some reformatting for acceptability to MySQL (for example,
changing a date from
MM/DD/YY
to ISO format).
• Determining whether a value is a member of a legal set of values. Perhaps the value
must be listed in the definition for an
ENUM
or
SET
column, or must be present in a
lookup table.
• Filtering out extraneous characters such as spaces or dashes from telephone num‐
bers or credit card numbers.
Some of these operations have little to do with MySQL, except in the sense that you want
values to be appropriate for the types of the columns in which you store them or against
which you match them. For example, before storing a value in an
INT
column, you can
make sure that it's an integer first, using a test like this (shown here using Perl):
$val
=~
/^\d+$/
or
die
"Hey! '"
.
escapeHTML
(
$val
)
.
"' is not an integer!\n"
;
For other types of validation, MySQL is intimately involved. If a field value is to be stored
into an
ENUM
column, you can make sure the value is one of the legal enumeration values
by checking the column definition in
INFORMATION_SCHEMA
.
Having described some of the kinds of web input validation you might want to carry
out, I won't further discuss them here.
Chapter 12
describes these and other forms of
validation testing. That chapter is oriented largely toward bulk input validation, but the
techniques discussed there apply to web programming as well.