Database Reference
In-Depth Information
20.7. Storing Web Input in a Database
Problem
Input obtained over the Web cannot be trusted and should not be entered into a database
without taking the proper precautions.
Solution
Sanitize data values by using placeholders or a quoting function so that SQL statements
you construct are valid and not subject to SQL injection attacks. Enable strict SQL mode
so the MySQL server rejects values that are invalid for column data types.
Discussion
After you've extracted input parameter values in a web script and checked them to make
sure they're valid, you're ready to use them to construct an SQL statement. This is ac‐
tually the easy part of input processing, although it's necessary to take the proper pre‐
cautions to avoid making a mistake that you'll regret. Let's consider what can go wrong,
and then see how to prevent problems.
Suppose that a form acts as a frontend to a simple search engine and contains a keyword
field. When a user submits a keyword, you intend to use it to find matching rows in a
table by constructing a statement like this:
SELECT * FROM mytbl WHERE keyword = ' keyword_val '
Here, keyword_val represents the value entered by the user. If the value is something
like eggplant , the resulting statement is:
SELECT * FROM mytbl WHERE keyword = 'eggplant'
The statement returns all eggplant-matching rows, presumably generating a small result
set. But suppose that the user is tricky and tries to subvert your script by entering the
following value:
eggplant' OR 'x'='x
In this case, the statement becomes:
SELECT * FROM mytbl WHERE keyword = 'eggplant' OR 'x' = 'x'
That statement matches every row in the table! If the table is quite large, the input
effectively becomes a denial-of-service attack because it causes your system to divert
resources away from legitimate requests into doing useless work. This type of attack is
known as SQL injection because the user is injecting executable SQL code into your
statement where you expect to receive only a nonexecutable data value. Likely results
of SQL injection attacks include the following:
Search WWH ::




Custom Search