Secure PHP and SQL Injections

I spent the last 1-2 weeks upgrading a site for a client and also securing it. How are a lot of PHP sites compromised? Well the point I’m looking it in this post is via forms and user entered data. There are many other security issues as well, this is just one of them. More commonly known as SQL Injections. has a good page on this subject explaining SQL Injection giving real life examples. As per their description:

Direct SQL Command Injection is a technique where an attacker creates or alters existing SQL commands to expose hidden data, or to override valuable ones, or even to execute dangerous system level commands on the database host. This is accomplished by the application taking user input and combining it with static parameters to build a SQL query.

So what does this mean? When you have a form on a page requesting user input and then use this input in MySQL queries you must not rely on what the user has entered. 99% of the time when you ask a user for their username and password, they will enter this. However along comes someone who wants to crack your system, suddenly you won’t be receiving a username and password through the form but most likely code aimed to inject extra operations into your MySQL query. Perhaps it’s easier to understand by way of example. My own logins usually accept a username and password from a form. The usual method is to accept a username and password and then run a SELECT statement to count the number of records that contain both the username and password. If the result is zero then the username and/or password is of course incorrect. A loosely written PHP script could be:

Insecure code to control user login
  1. $sql = mysql_query(“SELECT COUNT(*) FROM users WHERE username = ‘”.$_POST[‘username’].”‘ AND password = ‘”.$_POST[‘password’].”‘”);
  2. $count = mysql_fetch_row($sql);
  3. if ($count[0] == 0) {
  4. // user/password combo incorrect
  5. $errormsg = “Your Username and/or Password are incorrect. Please try again”;
  6. } else {
  7. // user/password combo correct proceed with login
  8. header(“Location:”);
  9. exit;
  10. }

As you can see from the above, the user input from the $_POST array is entered straight into the query. Oh and

Insecure code to control user login
  1. $username = $_POST[‘username’];
  2. $password = $_POST[‘password’];
  3. $sql = mysql_query(“SELECT COUNT(*) FROM users WHERE username = ‘”.$username.”‘ AND password = ‘”.$password.”‘”);

is no different 😉

So how can this be an issue? Well, all of your registered users will most likely enter their username and password. No issue there. Your MySQL statement would look like this:

SQL Statement
  1. SELECT COUNT(*) FROM users WHERE username = ‘sarah’ AND password = ‘sunshine’

However, a visitor wanting to crack your system could enter the following information:

username: sarah
password: ‘ or ‘1’=’1

Which would give:

SQL Injection Code
  1. SELECT COUNT(*) FROM users WHERE username = ‘sarah’ AND password = ” or ‘1’ = ‘1’

The above would return the total number of records in the table. So going by the very loosely written PHP script above, the if statement of

IF Statement
  1. if ($count[0] == 0) {
  2. } else {
  3. }

would provide a value from $count[0] to be above zero (providing there were records in the table), thus giving someone a login without a proper username or password.

Okay so yes, the example is a highly unsecure example, but it does happen. Plenty of people believe that providing there is no direct link to a file on a server, it won’t be found out. Trouble is it can be. So pulling the above example apart the first steps security would be

1. Ensure all restricted pages check that the member is logged in by using cookies or sessions or both.
2. Switch the if statement to ensure that only one record is found and allow the user to log in from that, otherwise assume the username/password is wrong and prevent the login.
3. Escape the user entered data.

At this point I’ll briefly mention Magic Quotes which is a setting in the php configuration file that can be set to on or off. If set to on then it will escape all user entered data, using the same method as the function addslashes(). It was introduced due to the amount of people who don’t escape their user entered data, however it causes nightmares for most PHP developers and is even stated on the site that most developers will choose to turn it off, preferring to escape the data when required. However on most shared hosting it’s not necessarily easy to do this as you have no control over the php.ini file, so the best way is to check to see if Magic Quotes are on, clean the variable if they are (using stripslashes() function) and then escape the variable. The function that I use (courtesey of Khalid is:

escapeString Function
  1. function escapeString($string) {
  2. if (get_magic_quotes_gpc()) $string = stripslashes($string);
  3. return mysql_real_escape_string($string);
  4. }

Here this function uses the mysql_real_escape_string() built in function. The function will add a backslashes to the beginning of any of the characters \x00, \n, \r, \, ‘, ” and \x1a. This will protect you from any SQL injections thus protecting your database.

However don’t be fooled into thinking that when I say “user entered data” that I just mean text inputs. Going back to the site I secured last week the original developers had not thought about a couple of issues, which could be have been used in conjunction with one another and allowed someone to wreak havoc on the site or in the database. Considering the site accepted money this is even more important to ensure its security. What was wrong?

Firstly they were storing the user’s id and password in cookies. Not necessarily a problem however they were then taking the raw data from the cookies and using it to query the database. Cookies can be edited therefore must be escaped. Then some user entered data was being escaped (but not as securely as the function above will do) but other data wasn’t. The site had even had a security update before me and there was still an emerging pattern. When a page had some secured data on it, the unsecured data wasn’t secured because the form variables were from checkboxes, radio buttons or a select list. I admit, when I first start out writing PHP I figured that this information couldn’t be tampered with therefore didn’t require securing. However take a normal form that’s there for people to complete and the details go into a database. A questionnaire for example. Plenty of input boxes, radio buttons, check boxes and select lists. The form is submitted to perhaps the same page or another page. We know full well that another completely unrelated site could submit to that php script as well. Take the same form names, which are plain to see in the HTML source, but then instead of a radio button having a prespecified value suddenly someone could submit to the processing script a radio button with their own value.

The simple rule is, never trust data that could have been tampered with. If it’s coming from $_GET, $_POST, $_COOKIE or even $_SERVER (after all if the user agent or http referrer can be altered who knows what it would be altered to?) then escape it. These are the 4 I deal with, if there are any further global arrays that should be escaped feel free to add to this list.

For people who use ASP, this Site Point article will be of use.

You may also like...

5 Responses

  1. Amine says:

    that’s really interesting. I would like to ask a question: can we get a class which can encrypt the sent data and the recieved one from the $_GET, $_POST, … for ex.??

  2. Sarah says:

    Hi Amine, I’m not quite sure what you’re asking for. I use the escapeString function above to encode/clean up information received via all the super globals (GET, POST, COOKIE, SERVER etc), unless I use other means of error checking as outlined here –

  3. Marie says:

    Very useful, thank you. Most of those issues I’ve already tried to solve on the site I’m working on, but unfortunately mysql_real_escape_string doesn’t seem to be working for me/on my server.

    Will certainly use the “count” trick for logins.

    • Sarah says:

      Hi Marie. What version of PHP are you running on? the mysql function has been around for a while. Make sure you’ve already connected to MySQL before running it though. Feel free to post up the actual error you get from trying to use it and hopefully we can try and solve it for you 🙂

  1. Sat, 15 July, 2006

    […] Don’t assume Magic Quotes are on, and to be honest, if they are, reverse their work and use a more secure method. […]