(This lessons follows on from the previous one.)

When you open the magicTest2.php page in your browser, you'll see three textboxes: one for a username, one for a password, and one for an email address. There is also a button on the form.

Enter the following in the email address text box:

test1@test1.com

Click the button, and you should see following print out:

1
test1
test1
test1@test1.com

These correspond to the four fields in the database. The four fields are:

ID
username
password
email

So the username is test1, the password is test1, and the email address is test1@test1.com.

Now, suppose you were naïve enough to have a database table exactly like that one. An attacker will test to see if any syntax error messages can be returned. If so, this means that the author of the script has not dealt with single/double quotes correctly. The attacker can then go ahead with further probes.

Try your script again. Only this time, add a single quote to the end of the test email address in the textbox:

test1@test1.com'

Now click the Submit button. What you should find is that an error message is indeed returned. Something like this:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource

Because Magic Quotes are off, that single quote is not being escaped. The line in our new script that is doing the damage is the one:

$SQL = "SELECT * FROM members WHERE email = '$email' ";

The SQL this time has a WHERE clause added. The WHERE clause is used when you want to limit the results to only records that you need. After the word "WHERE", you type a column name from your database (email, in our case). You then have an equals sign, followed by the value you want to check. The value we want to check is coming from the variable called $email. This is surrounded with single quotes.

When an email address is entered in the text box on our form, this value goes straight into the variable without any checks. When you type that extra single quote on the end, that will be added to the SQL. This is then run on the database. Because it's a stray single quote, you'll get a syntax error. It's this syntax error that an attacker is looking for.

Next, the attacker will try to add some SQL to yours. Try this. In the email address textbox, type the following. Type it exactly as it is, with the single quotes:

hi' OR 'x'='x

When you click the Submit button, you should find that there are no errors, and that the username, password and email address are printed out!

The attacker is trying to find out whether or not the SQL can be manipulated. If the answer is yes, further attacks will be launched. Can the table and field names be guessed? Can a username and password be guessed? It's this kind of attack that you want to thwart.

Try this last one. Enter the following into the email address box:

' OR ''='

Now click Submit.

Again, the details are printed out. This is because an OR clause has been added. The OR clause is set to a blank string. Meaning that the records will be brought back if it's a valid email address or not!

To stop this kind of attack, you MUST use some inbuilt PHP functions. The one to use for this kind of attack is:

mysql_real_escape_string( )

Between the round brackets, you type the string you need to check, followed by an optional database handle. To test this out, there is another script like the one you've just tried. This one is called magicTest3.php (in the same scripts folder). If you open this up in your text editor, you should see this added to the code:

$email = mysql_real_escape_string($email, $db_handle);

Now, the $email variable is being checked for any of the following:

\x00
\n
\r
\
'
"
\x1a

If any of the above characters are found, a backslash is added. Try the new script. Enter the following in the email address text box (with the single quote on the end):

test1@test1.com'

What you should find is that the following gets returned:

test1@test1.com\'

So the single quote has had a backslash added to it. The point is that the dangerous SQL doesn't get executed. Try the above attacks again. This time, you shouldn't be able to get in, if any of the listed escape characters have been used.

But you need to use the function on all variables or data that will be used in your SQL. So you should do this kind of thing:

$username = mysql_real_escape_string($username, $db_handle);
$password = mysql_real_escape_string($password, $db_handle);
$email = mysql_real_escape_string($email, $db_handle);

Examine the code in the new script. Pay attention to where the new lines go: after you have opened a connection to your database.

The PHP manual recommends the following sample script, when working with SQL (all comments are theirs; bold and colour is ours):

The PHP manual script

We have adapted the magicTest3 script, with the recommended code added, so that you can see it in action. The new script is magicTest4.php. Open the script and study the code. See if you can figure out how the new additions work.

As well as using mysql_real_escape_string( ), you'll need to use the other function you saw earlier, in the forms section - htmlspecialchars().

It can be a lot of work, dealing with SQL injection attacks, and handling all those escape characters. But if you don't want your databases attacked, you HAVE to defend yourself!

Limit the charcters that a user can enter

Another security technique that some advocate is to limit the characters that can be entered. For example, you might have this in your script:

$valid_chars = "abcdefghijklmnopqrstuvwxyz";
$valid_nums = "1234567890";
$valid_other = "£$^&_@#~";

You can then use some Conditional Logic to test if the character the user entered was on your list. If it's not, then you can display an error message.

An excellent walkthrough of security blunders can be found at:

http://www.sitepoint.com/article/php-security-blunders

In the next part, we'll take a look at how to create a table using SQL.