How to stop SQL injection attacks

If you have a website which is powered by a database it is potentially vulnerable to SQL injection attacks – this is relatively simple to protect against and the risks of not doing so are to compromise the integrity of the data stored on your server and possibly the website itself.

What is an SQL Injection attack?

Most websites will pass data from one page to another in order to maintain continuity. For example you might be looking at a list of products and you click on a particular product for more information. The website will pass the ID of the product in question to the next page so that it know what informationt o serve to the customer.

In many cases this is passed in the URL, so you might haveĀ  something like

An SQL Injection attack occurs when the recipient page takes the XYZ in the URL and makes a query with the database without verifying first that it doesn’t contain any additional and unexpected nasties.

How do I stop Injection attacks?

The key is that every time you retrieve a variable which you intend on passing to the database, check that it is in the expected format. Usually a product ID would be numeric, and not include any other characters. A date might be in the dd/mm/yyyy format. Once you have predetermined what format you expect each variable to be in, create a rule to check it prior to submitting it to the database – and if it fails stop the process.

I know what format I expect it to be in, but how do I check it?

The PHP code preg_match is probably the best method as it allows you very specific controls.

The following would check the variable $input for a date in the format yyyy-mm-dd. If there are any other characters, the page would be redirected to a page where no database queries are made, so stopping the threat.

if (!preg_match(‘/^[\-+]?[0-9]*\-?[0-9]*\-?[0-9]+$/’, $input)) {

The following test just allows numeric characters, any number of them, but anything else kicks it out:

if (!preg_match(‘/^[\-+]?[0-9]+$/’, $input))

You can also allow alphabetical characters, in the following case any character in either upper or lower case:

if (!preg_match(‘/^[\-+]?[a-zA-Z]*+$/’, $input))

There are plenty of guides on the Internet about how to use preg-match()

Simplify the process

You will probably find that you use one or two formats a lot, so create a function where you pass the variable and the type of format expected, then each time you retrieve a variable run it through your function to verify it is of the expected format.




Leave a Reply

Your email address will not be published. Required fields are marked *

* Copy This Password *

* Type Or Paste Password Here *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>