Freelance Web Developer & Freelance Web Developer London & Freelance Web Developer United Kingdom

In this tutorial I will explain what SQL injection is, I'll then build a dummy application that is susceptible to SQL injection in order to demonstrate, and show you how to prevent against this critical security problem using PHP.

Transcript:

Hi there I’m Abdi Gulaid A professional freelance web developer at BestCoder.co.uk

I've put this video together in order to explain, demonstrate, and show you how to prevent against SQL injection attacks when developing websites for the web. If you are a novice or an experienced web developer and what to learn how to make your clients websites more secure then this video tutorial is for you.

This tutorial will be as clear and as concise as possible in order to make the learning process easy and fun. During this tutorial I will go over what an SQL injection is, I will also demonstrate an SQL injection attack, to do this I will develop a basic PHP application to demonstrate how it all works.

After showing you how to carry out an SQL injection, I will demonstrate how you can prevent against one by using secure web programming techniques.

So what is SQL injection you ask?

SQL injection is a method of deliberately altering an applications SQL query by injecting specially constructed statements which deliberately alter how the application was originally designed to work.

Hackers typically use SQL Injection as a way of gaining unauthorised access into a website or to steal sensitive database, which can include but is not limited to: Credit card information, banking information, any form of information stored in a database.

That’s SQL injection explained; now let’s proceed by developing our dummy app in order to demonstrate how to carry out an SQL injection attack. Once we do this we'll then go through the steps that can be taken to prevent against an SQL injection.

I'll now quickly develop a very basic PHP application that's vulnerable to an SQL injection; it’s a login system that allows for people to login.

Well that’s our basic PHP application finished. Now let’s demonstrate how to carry out an SQL injection attack. I’m just going to test our form to make sure it can differentiate between a wrong logon and a valid logon.

First I’ll put in the wrong logon details to see if it rejects it. The valid logon details are in fact the username of 'username' and the password of 'password', not secure I know but we're keeping things simple here for the purposes of this tutorial. We've also made the password field visible, in order to show you how it all works.

Lets login, great our app has detected and has rejected those details. We'll now try the correct details to see if we are given access.

Great, our application is working as designed and can differentiate between the wrong logon details and the correct logon details.

Ok what we'll now do is carry out an SQL injection attack to see if we can gain access into our application by injecting some specially constructed statements which circumvent our application's security procedure.

We'll just put 'anything' into the username field, and use the password field to inject the code. What we've just put into our password field will essentially become part of our SQL query makeup, it essentially adds an additional 'or x equals x' statement, and as we know X is equal to x making the statement true thus granting us access into the system.

This animation shows how the SQL injection attack will work. The injected code will become part of the query just like this. Now let’s logon to see if the injection works, and there you have it we have successfully carried out an SQL injection attack and have gained access into the system.

Ok that’s the demonstration part complete, now let’s discuss the many ways in which SQL injection can be prevented in PHP. There's the Magic Quotes method, the Addslashes method, MySQL's own MySQL_real_escape_string method, and finally the prepared statements method also called parameterization. Let’s discuss each method in more detail, let’s firstly begin with the Magic Quotes method.

Magic Quotes is a built in PHP setting that is supported in a number of PHP versions. This built in setting automatically escapes all POST and GET variables, by adding the addslashes method to all POST and GET variables. This method was made available by PHP as way of protecting applications that were developed by developers who lacked security knowledge.

Magic Quotes shouldn't be relied upon and I recommend you turn it off if it’s turned on. This feature has been deprecated as of PHP 5.3 and removed as of PHP version 6, relying on this feature is highly discouraged as the application environment can change leaving your application completely open to SQL Injection if the server doesn't support Magic Quotes; hence why I recommend turning it off and to manually secure your application by using methods that I will shortly explain.

Leaving Magic Quotes turned on will also hamper the performance of your application as PHP will automatically escape every single GET and POST variable, this blanket wide approach will consume valuable system resources because many variables which you won’t use for SQL will essentially go through the magic quotes function for escaping thus slowing your application down.

You can turn off Magic Quotes in the PHP.INI file if it’s turned on.

Then there is the addslashes method, addslashes is PHP's own escaping function and can be applied to all POST and GET variables when running database queries. I do not personally recommend it as I and other web developers have found ways to circumvent its limited security by injecting multi-byte characters instead of normal characters again this is a method I wouldn’t recommend, it does work but can be bypassed by injecting multi-byte characters in a specific sequence.

We now come to the MySQL_real_escape_string method, MySQL_real_escape_string is MySQL's own escaping function. This is a very secure method of preventing against SQL injection, but is prone to errors during the web development process because you have to explicitly apply to each and every statement that you wish to escape. I have no problem in recommending this method for both novice and experienced web developers, although our next method is the best method and is less prone to errors.

That method is the use of prepared statements. Prepared statements can help increase security by separating SQL logic from the data being supplied. Security wise prepared statements are completely resistant to any kind of SQL injection this is because with bound parameters the data and query are transmitted separately and the data is never interpreted by the database engine. The prepared statements method is advanced and requires some additional learning before affectively implementing it.

There are some negatives to prepared statements; sometimes prepared statements can actually be slower than regular queries. The reason for this is that there are multiple round trips to the server, which can slow down simple queries that are only executed a single time. In cases like this one has to decide if it is worth trading off the performance impact of this extra round trip, in order to gain the security benefits of using prepared statements.

That’s the prevention methods explained, what I'll now do is show you how to apply the MySQL_real_escape_string and prepared statements methods to our application in order to prevent against an SQL injection attack.

Both the username and password variables are currently not sanitised. We'll now sanitise the variables to prevent against an SQL injection attack, by using the MySQL Real Escape String method.

Ok we've now applied the sanitisation to our variables. Let’s test the application again to see if we can gain access, by carrying out the same SQL injection attack, I'm now going to login to see if we can gain access. Brilliant, the system has denied us access and the SQL injection attack has failed.

I’m now going to use the prepared statements method instead to sanitise the data. Prepare statements work in a different way, and we have to delete and restructure our existing SQL code. That’s the old code deleted, what we'll now do is rewrite our code using prepared statements in order to prevent against SQL injection attacks.

I've now rewritten our code, using the prepared statement method; the data should now be sanitised. Let's retest our application to see if this is true, I’m just going to try and login again by carrying out an SQL injection. As you can see the data has been sanitised, and our SQL injection has been successfully prevent.

Well that’s SQL injection explained and demonstrated, you should now know how to carry out and prevent against an SQL injection attack in order to make your clients websites more secure. Thanks for watching and if you like my videos don't forget to subscribe.