MySQL Cookbook

Using Web Input to Construct Queries

Problem

Input obtained over the Web cannot be trusted and should not be placed into a query without taking the proper precautions.

Solution

Sanitize data values by using placeholders or a quoting function.

Discussion

After you’ve extracted input parameter values and checked them to make sure they’re valid, you’re ready to use them to construct a query. This is actually the easy part, though it’s necessary to take the proper precautions to avoid making a mistake that you’ll regret. First, let’s consider what can go wrong, then see how to prevent the problem.

Suppose you have a search form containing a keyword field that acts as a frontend to a simple search engine. When a user submits a keyword, you intend to use it to find matching records in a table by constructing a query 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 query is:

SELECT * FROM mytbl WHERE keyword = 'eggplant'

The query returns all eggplant-matching records, presumably generating a small result set. But suppose the user is tricky and tries to subvert your script by entering the following value:

eggplant' OR 'x'='x

In this case, the query becomes:

SELECT * FROM mytbl WHERE keyword = 'eggplant' OR 'x'='x'

That query matches every record in the table! If the table is quite large, the input effectively becomes a form of denial-of-service attack, because it causes ...