Processing Web Input with MySQL - MySQL

Database Reference

In-Depth Information

Recipe 2.5 discusses similar placeholder and quoting techniques for Ruby, PHP, Python,

and Java. For JSP pages written using the JSTL tag library, quote input parameter values

using placeholders and the <sql:param> tag. For example, to use the value of a form

parameter named keyword in a SELECT statement, do this:

<sql:query dataSource= "${conn}" var= "rs" >

SELECT * FROM mytbl WHERE keyword = ?

<sql:param value= "${param['keyword']}" />

</sql:query>

One issue not covered by placeholder techniques involves a question of interpretation:

If a form field is optional, what should you store in the database if the user leaves the

field empty? Perhaps the value represents an empty string—or perhaps it should be

interpreted as NULL . One way to resolve this question is to consult the column metadata

(see Recipe 10.6 ). If the column can contain NULL values, interpret an empty field as

NULL . Otherwise, take an empty field to mean an empty string.

Placeholders and encoding functions apply only to SQL data values. They do not address

how to handle web input used for other kinds of statement elements such as identifiers:

names of databases, tables, and columns. If you intend to include such values into a

statement literally, you should check them first. For example, to construct a statement

such as the following, you should verify that $tbl_name contains a reasonable value:

SELECT * FROM $tbl_name;

But what does “reasonable” mean? If your tables don't have strange characters in their

names, it may be sufficient to make sure that $tbl_name contains only alphanumeric

characters or underscores. Alternatively, issue a statement that determines whether the

table actually exists. (Check INFORMATION_SCHEMA or use SHOW TABLES .) This is more

foolproof, at the cost of an additional statement.

A better option is to use an identifier-quoting routine, if you have one (see Recipe 2.6 ).

This approach requires no extra statement because it renders any string safe for use in

a statement. If the identifier does not exist, the statement simply fails as it should.

For additional protection in your web scripts, combine client-side checking of input

values with strict server-side checking. You can set the server SQL mode to be restrictive

about accepting input values so that it rejects values that don't match your table column

data types. For discussion of the SQL mode and input value checking, see Recipe 12.1 .