Input obtained over the Web cannot be trusted and should not be placed into a query without taking the proper precautions.
Sanitize data values by using placeholders or a quoting function.
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 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 ...