How to protect your php webpage from sql injection

Google+ Pinterest LinkedIn Tumblr +

Sql Injection

Sql injection is the most common form of website hacking, it happens when a user affects your database without permission using sql code. For example:

A vunerable website has a login page, the password feild is not protected from sql injection, when a hacker sees this, they type ‘OR”=’ into the password feild.

The login is processed like this:

SELECT * FROM `users` WHERE username=’$username’ and password=’$password’

Now we will insert our values:

SELECT * FROM `users` WHERE username=’admin’ and password=”OR”=”

Notice that the password check has been bypassed. Now the code says to select any user if the password equals nothing or nothing equals nothing, which is always true, so this will select the first user in the database, which most-commonly is the administrator account.

So how to you prevent these attacks?

There are many methods to preventing a SQL injection, some are easier and better, but i think that its handy to know more than one. Basically the main idea is to stop, or encode the apostrophy.

Method 1 – addslashes()

In php coding there is a function called addslashes. This function adds backslashes before any characters which could cause harm to your database, or fool it into giving out wrog information. An example can be seen below:

Who’s that man? would be inserted into the database as Who’s that man? (This is not database safe)

When using addslashes:

addslashes(Who’s that man?) would be inserted into the database as Who\’s that man? (this is database safe)

Having a backslash before the ‘ means that the database does not recognise it as valid sql code, and simply ignores it.

Method 2 – encryption

Encryption means that the information that is sent through is encrypted before it is sent to the database for validation, this means that the information in the database must also be encryped however.

Some forms of encryption include:

  • md5()
  • sha1()
  • AES_ENCRYPT()

The first two are ok for basic websites where the information that is being encrypted is not of great value, however, these have been cracked in various different ways, and tools that people can use to crack them are wildley availiable, they still provide protection from sql injection tho, example:

md5(‘OR”=’) = c499fdb7541fcdcfdefbace0d86bfd56

sha1(‘OR”=’) = 260144b2c2823de61faa908bc8f594ff02e2235d

Notice that once the values are encrypted they no longer contain the apostophy, this can safely be compared to the content in the database as the result of the encryption is always the same and unique for every string.

for more valuable or personal information such as email addresses, date of births and passwords, I recomend using AES_ENCRYPT().

AES stands for Advanced Encryption Standard and is strong enough to protect information at the “Top Secret” level. This function however uses a different method then a basic php striing encryption, you must state this function in the SQL code itself.

To have content encrypted by AES you must have the row that your encrypted information will be stored in set to BLOB, because AES stores information as binary code.

To insert the information into the database and encode it as AES you must use a query similar to this:

INSERT INTO `table_name` VALUES(AES_ENCRYPT(‘content you are encrypting’, password) …

The “password” is anything that you choose, it must be used to decypt the data. To decrypt AES you must use a query similar to this:

SELECT AES_DECRYPT(row_name, password) AS row_name FROM table_name …

Using AES encyption has become a web standard, I can’t say that it’s 100% secure, but I think you can sleep soundly at night knowing that noone has cracked it yet.

Method 3 – filter()

In php there is a set of functions called filters, these work similar to addslashes in that they encode the harmful characters in the string into their character code, there mare many different filter functions, but the two I think you would use the most are:

filter_var(“string goes here”,FILTER_SANITIZE_STRING);

This encodes all characters that are harmful to the database.

filter_var(“string goes here 123”), FILTER_SANITIZE_NUMBER_INT);

This actually removes all characters except for numbers, and + and -, this one is perfect to make sure the content your getting is a whole number, and not text.

The filter functions also have many other uses, like validating data and returning true or false based on the different function, for example, one of the functions can validate your string and tell you, based on the makeup of the string, whether or not the string is an email address.

A full list of the filter functions and how to use them, examples etc can be found here:

http://www.w3schools.com/php/php_ref_filter.asp

As well as protecting input feild from sql attack you should also protect any content coming from the url, eg GET variables, as these can also be changed by a user. What I use as a rule of thumb is, protect everything that a user can generate or has the ability to edit, and make sure any content that is sent through the database is exactly what you expect it to be, so if you expect it to be just numbers, dont accept text. If you follow that rule you should be fine.

Using the three methods described above, you should be able to safely know that no hackers are infiltrating your website, for my own websites, I use a combination of the filter functions and AES encryption when the content is personal e.g passwords, but if it’s just standard variables (eg, user id etc) then i would just use the filter functions.

For more information, I recomend these websites:

Basic SQL security:

http://searchsecurity.techtarget.com/generic/0,295582,sid14_gci1369365,00.html

Information on filter functions:

http://php.net/manual/en/book.filter.php

http://www.w3schools.com/php/php_ref_filter.asp

Information on AES encryption:

http://www.progressive-coding.com/tutorial.php

Share.

About Author

Leave A Reply