Create a Portfolio Client Area Using PHP and MySQL: Part 2

Continuing from Part 1, this tutorial will go over some security issues for our sign up form. When dealing with databases, it is important to make sure the client's information is secure. The tutorial will also go over some ways to make sure the user enters in correct data.In the last tutorial we left off with a solid database structure, a signup form that successfully input data into our database, and an overall plan for the project. Read up on all the other parts below:

As promised, in today's tutorial we're going to go over security when working with PHP and databases, and apply some security issues specifically to our sign up form.

This tutorial will go over SQL injections, basic HTML form security, md5 password encryption, and more.

Form Security

This will be a bit of a mini HTML tutorial and very basic, but it must be covered because many people may be working with secure forms for the first time. The first step to securing our sign up form is to hide the password on the HTML form itself:

Hidden Password Field

Doing this will help secure the password from being stolen off-screen. It is a bit of an obvious step, but an essential one nonetheless.

To hide the password in the HTML form, simply change the "type" attribute for the password field to "password" instead of "text".

<form action="signup.php" method="post">
Username:
<input name="username" type="text" />
Password:
<input name="password" type="password" />
Email:
<input name="email" type="text" />
PayPal Address:
<input name="paypal" type="text" />
<input type="submit" />
</form>

Password Encryption

The second form of security is to encrypt the password. Looking at our current clients in the database, we can see the password field is completely left open for administrators, or other viewers of the database to see. This is, of course, a bad practice to keep it in plain sight like this.

Md5 Encryption

To solve our problem, we're going to encrypt the password with the md5() function in PHP. This will allow us to encrypt a password that not even an administrator can decrypt. Easily enough, we can just place our password variable in as the parameter for the md5() function: md5($password).

<?php 
// Require the file that connect to the database.
// It is good practice to put the database connection
// information in a separate file. require('db.php'); 
// Since we used the post method in our form, we can
// securely call our data using the $_POST predefined
// variable, with parameters specified by the name
// attribute in our form.
$username = $_POST["username"];
$password = $_POST["password"];
$pw = md5($password);
$email = $_POST["email"];
$paypal = $_POST["paypal"]; 
// Finally, we use a MYSQL_QUERY to insert our information
// into the database. INSERT INTO defines what fields we want
// to insert information, and VALUES defines the values that
// we are entering.
$result= MYSQL_QUERY(
"INSERT INTO clients (client_ID, username, password, email, paypal)".
"VALUES ('', '$username', '$pw', '$email', '$paypal')"
); 		 
// Notice in VALUES, we leave the first field blank: ' '
// This is because our database automatically assigns this
// value to each client. 
echo "Thank you for signing up.";
?>

So what happens here is, first, the password is taken from the POST method via our form, and put into the variable $password. Then, we create a new variable, $pw, and put the encrypted $password string into it.

As you'll notice below, in our query to insert the information in the database, we've inserted the encrypted version of the password: $pw.

Let's test our or form and see if it worked:

Md5

Looks like it did! Our password is now encrypted and cannot be read in plain text.

SQL Injection

SQL injection is a type of malicious code that can be used to manipulate an unsecured database by entering some well thought-out data into a form.

For example, below is a basic SQL statement that selects data from "users" where the "name" is equal to whatever was input into the form field named "formfield."

SELECT * FROM users WHERE name = '$_POST(formfield)';

If someone were to enter in a normal name, say "Kayla" for example, the resulting PHP would execute the following.

SELECT * FROM users WHERE name = 'Kayla';

It would select the name equal to "Kayla" in the table "users". However, malicious code entered into the form could be this:

a';DROP TABLE users; SELECT * FROM data WHERE name LIKE '%

This seems like a bunch of nonsense until we put it into our SQL code:

SELECT * FROM users WHERE name = 'a';DROP TABLE users; SELECT * FROM DATA WHERE name LIKE '%';

This creates an entirely different purpose for the code, which finds a name "a", drops the "users" table, and selects all the data (%). Anyone can see how this can be very destructive.

Fortunately, the fix is very easy. We just need to add mysql_real_escape_string() around each of our variables associated with an input field in the form. For an example, the updated sign up form is below.

<?php
// Require the file that connect to the database.
// It is good practice to put the database connection
// information in a separate file.
require('db.php');
// Since we used the post method in our form, we can
// securely call our data using the $_POST predefined
// variable, with parameters specified by the name
// attribute in our form.
$username = mysql_real_escape_string($_POST["username"]);
$password = mysql_real_escape_string($_POST["password"]);
$pw = md5($password);
$email = mysql_real_escape_string($_POST["email"]);
$paypal = mysql_real_escape_string($_POST["paypal"]);
// Finally, we use a MYSQL_QUERY to insert our information
// into the database. INSERT INTO defines what fields we want
// to insert information, and VALUES defines the values that
// we are entering.
$result= MYSQL_QUERY(
"INSERT INTO clients (client_ID, username, password, email, paypal)".
"VALUES ('', '$username', '$pw', '$email', '$paypal')"
);
// Notice in VALUES, we leave the first field blank: ''
// This is because our database automatically assigns this
// value to each client.
echo "Thank you for signing up.";
?>

Check for Invalid Characters

The next step is to check for any invalid characters that may be inserted into any of the fields. Because a username & password should be allowed different characters than an email, we'll create two separate pieces of code to handle them. The first we need to create is to handle invalid characters for the username and password.

To specify, let's make sure the client can only enter in letters a-z, A-Z, or numbers 1-9.

We can use regular expressions (/^[a-zA-Z0-9]+$/ in the code below) to identify what characters we'd like to allow, and use the preg_match() function to compare it to whatever our string is ($str). Our string in this case would be either the username or password.

!preg_match('/^[a-zA-Z0-9]+$/', $str);

The exclamation point in front of the function (!) means NOT, so the statement above is going to return true if the $str and regular expressions do not match, and false if everything is ok. So, to create some workable code to deal with this, we can use a basic if/else statement.

if(!preg_match('/^[a-zA-Z0-9]+$/', $username)) // If our username is invalid
{
echo "The username can only contain letters or numbers."; // Tell the user
}
if(!preg_match('/^[a-zA-Z0-9]+$/', $password)) // If our password is invalid
{
echo "The password can only contain letters or numbers."; // Tell the user
}

Similar to checking for invalid characters, we'll need to do the same for an email. The regular expressions have to be different, however, to make sure the email can use the @ symbol, and to make sure everything is in the right spot.

// If our email or PayPal addresses are invalid
if(!preg_match("^[_a-z0-9-]+(.[_a-z0-9-]+)*@[a-z0-9-]+(.[a-z0-9-]+)*(.[a-z]{2,3})$", $email))
{
echo "The email or PayPal address you entered is invalid."; // Tell the user
}

To put this all into action, we need to combine the three if statements. In basic pseudo code, here is what we need to do:

If the username is invalid, tell the user.
Or else if the password is invalid, tell the user.
Or else if the email or PayPal address is invalid, tell the user.
Else, if everything is ok, insert the data into the database and tell the user they've successfully signed up.

// If the username is invalid, tell the user.
// Or else if the password is invalid, tell the user.
// Or else if the email or PayPal address is invalid, tell the user.
// Else, if everything is ok, insert the data into the database and tell
// the user they’ve successfully signed up.
if(!preg_match('/^[a-zA-Z0-9]+$/', $username)) // If our username is invalid
{
return "The username can only contain letters or numbers."; // Tell the user
}
else if(!preg_match('/^[a-zA-Z0-9]+$/', $password)) // If our password is invalid
{
return "The password can only contain letters or numbers."; // Tell the user
}
// If our email or PayPal addresses are invalid
else if(!eregi("^[_a-z0-9-]+(.[_a-z0-9-]+)*@[a-z0-9-]+(.[a-z0-9-]+)*(.[a-z]{2,3})$", $email))
{
return "The email or PayPal address you entered is invalid."; // Tell the user
}
else{
// Inserts the data into the database
$result= MYSQL_QUERY(
"INSERT INTO clients (client_ID, username, password, email, paypal)".
"VALUES ('', '$username', '$pw', '$email', '$paypal')"
);
echo "Thank you for signing up.";
}

Check to See if the Username has Been Taken

The last thing we'll do is check to see if the username has been taken. In the code below, we use a simple MySQL query to get an array of clients. We then save that in the variable $result.

Finally, with a while statement, we go through each instance of the array, $result, naming each instance $row. Inside the statement we can check to see if our current username and the username we're at in the database ($row) match.

If they do, we create a variable and call it true. Otherwise, we create the same variable and make it false.

//Check to see if the username is already taken
$query = "SELECT * FROM clients";
$result = mysql_query($query) or die(mysql_error()); // Get an array with the clients
while($row = mysql_fetch_array($result)){ // For each instance, check the username
if($row['username'] == $username){
$usernameTaken = true;
}else{$usernameTaken = false;}
}

Now we can add a few simple lines to our if/else if statement to check for duplicate usernames too.

if($usernameTaken)
{
echo "That username has been taken.";
}

Simply read, the above code says: If the username has been taken ($usernameTaken), echo out that it has been already taken.

Our Final Signup.php

Below is our final signup.php page for your convenience.

<?php
require('db.php');
// Create the variables, while encrypting the password and
// preventing SQL injection
$username = mysql_real_escape_string($_POST["username"]);
$password = mysql_real_escape_string($_POST["password"]);
$pw = md5($password);
$email = mysql_real_escape_string($_POST["email"]);
$paypal = mysql_real_escape_string($_POST["paypal"]);
//Check to see if the username is already taken
$query = "SELECT * FROM clients";
$result = mysql_query($query) or die(mysql_error()); // Get an array with the clients
while($row = mysql_fetch_array($result)){ // For each instance, check the username
if($row['username'] == $username){
$usernameTaken = true;
}else{$usernameTaken = false;}
}

// If the username is invalid, tell the user.
// Or else if the password is invalid, tell the user.
// Or else if the email or PayPal address is invalid, tell the user.
// Else, if everything is ok, insert the data into the database and tell
// the user they’ve successfully signed up.
if($usernameTaken)
{
echo "That username has been taken.";
}
else if(!preg_match('/^[a-zA-Z0-9]+$/', $username)) // If our username is invalid
{
echo "The username can only contain letters or numbers."; // Tell the user
}
else if(!preg_match('/^[a-zA-Z0-9]+$/', $password)) // If our password is invalid
{
echo "The password can only contain letters or numbers."; // Tell the user
}
// If our email or PayPal addresses are invalid
else if(!eregi("^[_a-z0-9-]+(.[_a-z0-9-]+)*@[a-z0-9-]+(.[a-z0-9-]+)*(.[a-z]{2,3})$", $email))
{
echo "The email or PayPal address you entered is invalid."; // Tell the user
}
else{
// Inserts the data into the database
$result= MYSQL_QUERY(
"INSERT INTO clients (client_ID, username, password, email, paypal)".
"VALUES ('', '$username', '$pw', '$email', '$paypal')"
);
echo "Thank you for signing up.";
}
?>

Wrapping Up

Security is probably one of the most boring issues in web development, but of course, one of the most important. For any other experienced programmers out there, let me know if I missed something with the security, and I can discuss it in the next tutorial.

Otherwise, next time we'll be going over creating the log in form, PHP sessions, and creating a profiile page for the client.

12 Comments

  1. Nick July 8, 2009
  2. Kayla July 8, 2009
  3. John July 15, 2009
  4. Michal Kopanski July 19, 2009
  5. charles July 20, 2009
  6. Bruno Correia July 21, 2009
  7. Bruno Correia July 21, 2009
  8. Stuart Pringle July 22, 2009
  9. Stuart Pringle July 22, 2009
  10. Stuart Pringle July 22, 2009
  11. Derek July 26, 2009
  12. Vector September 6, 2009