Sql Injection PHP MySql example

What is SQL Injection

SQL injection refers to the act of someone inserting a MySQL statement to be run on your database without your knowledge. Injection usually occurs when you ask a user for input, like their name, and instead of a name they give you a MySQL statement that you will unknowingly run on your database.

SQL Injection Example

Below is a sample string that has been gathered from a normal user and a bad user trying to use SQL Injection. We asked the users for their login, which will be used to run a SELECT statement to get their information.

MySQL & PHP Code:

// a good user's name
$name = "timmy";
$query = "SELECT * FROM customers WHERE username = '$name'";
echo "Normal: " . $query . "<br />";

// user input that uses SQL Injection
$name_bad = "' OR 1'"; 

// our MySQL query builder, however, not a very safe one
$query_bad = "SELECT * FROM customers WHERE username = '$name_bad'";

// display what the new query will look like, with injection
echo "Injection: " . $query_bad;


Normal: SELECT * FROM customers WHERE username = ‘timmy’
Injection: SELECT * FROM customers WHERE username = ” OR 1”

The normal query is no problem, as our MySQL statement will just select everything from customers that has a username equal to timmy.

However, the injection attack has actually made our query behave differently than we intended. By using a single quote (‘) they have ended the string part of our MySQL query

  • username = ‘ ‘

and then added on to our WHERE statement with an OR clause of 1 (always true).

  • username = ‘ ‘ OR 1

This OR clause of 1 will always be true and so every single entry in the “customers” table would be selected by this statement!

More Serious SQL Injection Attacks

Although the above example displayed a situation where an attacker could possibly get access to a lot of information they shouldn’t have, the attacks can be a lot worse. For example an attacker could empty out a table by executing a DELETE statement.

MySQL & PHP Code:

$name_evil = "'; DELETE FROM customers WHERE 1 or username = '"; 

// our MySQL query builder really should check for injection
$query_evil = "SELECT * FROM customers WHERE username = '$name_evil'";

// the new evil injection query would include a DELETE statement
echo "Injection: " . $query_evil;


SELECT * FROM customers WHERE username = ‘ ‘; DELETE FROM customers WHERE 1 or username = ‘ ‘

If you were run this query, then the injected DELETE statement would completely empty your “customers” table. Now that you know this is a problem, how can you prevent it?

Injection Prevention -mysql_real_escape_string()

Lucky for you, this problem has been known for a while and PHP has a specially-made function to prevent these attacks. All you need to do is use the mouthful of a function mysql_real_escape_string.

What mysql_real_escape_string does is take a string that is going to be used in a MySQL query and return the same string with all SQL Injection attempts safely escaped. Basically, it will replace those troublesome quotes(‘) a user might enter with a MySQL-safe substitute, an escaped quote \’.

Lets try out this function on our two previous injection attacks and see how it works.

MySQL & PHP Code:

//NOTE: you must be connected to the database to use this function!
// connect to MySQL

$name_bad = "' OR 1'"; 

$name_bad = mysql_real_escape_string($name_bad);

$query_bad = "SELECT * FROM customers WHERE username = '$name_bad'";
echo "Escaped Bad Injection: <br />" . $query_bad . "<br />";

$name_evil = "'; DELETE FROM customers WHERE 1 or username = '"; 

$name_evil = mysql_real_escape_string($name_evil);

$query_evil = "SELECT * FROM customers WHERE username = '$name_evil'";
echo "Escaped Evil Injection: <br />" . $query_evil;


Escaped Bad Injection:
SELECT * FROM customers WHERE username = ‘\’ OR 1\”
Escaped Evil Injection:
SELECT * FROM customers WHERE username = ‘\’; DELETE FROM customers WHERE 1 or username = \”

Notice that those evil quotes have been escaped with a backslash \, preventing the injection attack. Now all these queries will do is try to find a username that is just completely ridiculous:

  • Bad: \’ OR 1\’
  • Evil: \’; DELETE FROM customers WHERE 1 or username = \’

And I don’t think we have to worry about those silly usernames getting access to our MySQL database. So please do use the handy mysql_real_escape_string() function to help prevent SQL Injection attacks on your websites. You have no excuse not to use it after reading this lesson!

What Is SQL Injection? Understanding This Important Threat Vector

In August 2009 three people were charged with the theft of 130 million credit card numbers from Heartland Payment Systems, 7-Eleven and a supermarket chain called Hannaford Brothers. It was reportedly the biggest case of identity theft in U.S. history. This theft was carried out using SQL injection techniques. What is SQL injection and why should you be bothered about it?

We all log into Web sites on a daily basis, be it for shopping, banking or accessing protected content. In most cases usernames are stored in a relational database along with other user details and related information.

The proliferation of databases created to support this growth in websites with protected content has lead to many insecure and vulnerable sites, hastily put on line with little thought to the underlying security requirements. This gaping hole in the security of Web sites is being aggressively exploited by SQL injection attacks.

Even more worrying this attack can be carried out against fully patched databases. It is not a problem with patches, more an issue with the way databases work. This attack can be made against search pages, feedback forms, customer comment forms and any other Web site pages that rely on a database engine. All databases can be vulnerable to this attack including MySQL, SQL Server and Oracle. Despite its name SQL injection is not only specific to Microsoft SQL Server.

Anatomy of a typical SQL injection attack

Probably the most vulnerable page for a SQL injection attack would be the one used to login to a site. The database table that stores a user’s details would normally have at least two columns—one for the username and one for the password. The table would probably be called users or something very similar.

The database will parse the username and password typed into the logon screen and convert it into a string of SQL to send to the database, so the database engine receives a line of SQL similar to this:

SELECT * FROM users WHERE username = ‘Chris Date’ AND password = ‘userspassword’

This is a pretty standard SQL statement and would look more or less the same whichever database you were using to store the user’s data. Hackers are interested in SQL injection attacks as they can manipulate the database by sending it some duff data. Instead of giving a valid username they could type something like this:

SELECT * FROM users WHERE username = ‘ ‘ ‘ AND password = ‘ ‘

The hacker has supplied a ‘ character as the username and a blank as the password. The database, being well behaved, would parse this query but return an error message informing the user that they have made a mistake in the line of code they have just typed in.

Using this chink in the database security armour the hacker will start to dig around a bit more. The error messages returned from the database will start to become increasingly more helpful, and begin to point out the structure of the underlying tables in the database. We can start to fool the database further by adding in other strings to the username.

For example if we type in ‘or email=’345 as our username it will be resolved by the query parser as:

WHERE username = ‘ ‘or email=’345′ AND password = ‘ ‘

At this point the database may give an error if there is no such column in the users database as email. On the other hand it may not give an error message suggesting that there is a column called e-mail after all. All the hacker needs do is guess the e-mail address for a legitimate user. As most organisations have a standard e-mail address structure it is very easy to get an employee name and then use that as your user name in the SQL injection attack.

As you can see it is scarily easy to build up a picture of a database structure based on some simple fooling of the text entry screen and some intelligent guesses as to the likely structure of a database.

How can SQL injection be avoided?

It is possible to filter out single quotes, double quotes, slash, backslash, semi-colon and extended characters from user input strings, cookie values and parameters from a URL. In addition numeric values can be converted into integers before passing to the database.

Database administrators can take further measures in case they are still attacked. For example the simple measure of deleting all of the extended stored procedures that are not needed such as xp_sendmail and xp_cmdshell can immediately start to secure the database.

SQL injection is not a new attack and is avoidable if you take some basic security measures—whatever database you use.