O'Reilly logo

MySQL Cookbook by Paul DuBois

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required