Welcome to part 2 of this build. In part 1 we created a simple login form that authenticates users using server side validations and saw how to redirect the browser to the private area of the website. We also saw how to use sessions and session variables to check whether a user is logged in.
Till now, our valid username and password were hard-coded in our PHP script. We will now see how to store credentials on a MySql database and how we can query this database using PHP. We will build a simple registration page that will allow us to register new users, and modify the login page such that authentication is done against the database.
I have already created a database called "db_webspace" and a table within it (called "users") to store my user data.
1. The Regisration Page
We first need a page where users can register for our website. To keep it simple we will only ask for a username, a password and an email address. Here's what the form looks like:
We obviously want to validate the user input to make sure that the username is unique and that the passwords match. Since I have already covered server-side validation in my previous post I will not go into too much detail, however I will discuss how to validate against the database. Before that however, we need to register our first user and to do that we need a connection to our database:
<?php
$conn = mysql_connect("localhost", "root", "mydbpassword");
if (!$conn) {
die ("Unable to connect to database server".mysql_error());
}
//Connection successful;
?>
To connect to our database we first declare a variable ('$conn' in this case) and assign it the return value of the "mysql_connect" function. This function takes the MySQL server name, username and password as arguments and opens a connection to that database. It then returns a reference to that connection which we are storing in "$conn". If the connection fails, "$conn" is set to FALSE.
I'm not very comfortable having my database password stored in plain text in the script. In the real world I would probably store all connection properties within another file and encrypt them. However, it must be said that if someone gains access to your php scripts, you're already in trouble no matter where and how you store your settings!
We now have a connection to our database. Assuming that the user input is valid, here's how to register a new user:
$Result = mysql_select_db("db_webspace",$conn);
if (!$Result){
$Out.="Unable to select DB: ".mysql_error()."
";
}
else
{
$Uname = mysql_real_escape_string($_POST['txtUname']);
$Pass = mysql_real_escape_string($_POST['txtPassword']);
$Conf = mysql_real_escape_string($_POST['txtConfirm']);
$Email = mysql_real_escape_string($_POST['txtEmail']);
//Validate...
//Register the user
$SQL = "INSERT INTO users Values ('$Uname', '$Pass', '$Email')";
$Success = mysql_query($SQL, $conn);
if ($Success)
{// Show Thank-you message}
else
{// Show Error}
}
After successfully connecting to our database server, we use the 'mysql_select_db' function to select the actual database we want to work with which in our case is "db_webspace". In actual fact we are setting "db_webspace" as the currently active database for our connection ($conn) and every subsequent SQL query made using "$conn" will be executed on this database. Next, we use the "mysql_real_escape_string" function to sanitize user input. This is very important to make sure that the user input can be safely used in an SQL query. After performing some validations and assuming that our data is valid we construct an SQL statement to insert the values into the "users" table and execute the query using the 'mysql_query' function. Our user is now registered but the password is stored in plain text on the database. Here's a screenshot from "phpmyadmin" showing my registered users and their passwords in all their glory!
To fix this I will use the SHA1 hashing algorithm to hash the user's password before storing it into the database:
...
//Hash the password
$Pass = sha1($Pass);
$SQL = "INSERT INTO users Values ('$Uname', '$Pass', '$Email')";
...
Here's what the hashed password looks like for the 'secadmin' user:
We now have a way of registering our users and storing their passwords securely on our database.
2. Authenticating against the Database
Let's turn our attention now back to the login page and add the necessary code to authenticate our newly registered users against the database:
...
$conn = mysql_connect("localhost", "root", "mydbpassword");
if (!$conn){
die ("Unable to connect to database server".mysql_error());
}
$Result = mysql_select_db("db_webspace",$conn);
if (!$Result){
//Show error and terminate script execution
}
else {
// Sanitize user input }
$User = mysql_real_escape_string($_POST['txtUname']);
$Pass = mysql_real_escape_string($_POST['txtPassword']);
//Hash the password to compare it to hash in db
$Pass = sha1($Pass);
$Sql = "Select Count(*) As myCount
From users
Where userid='$User'
And password='$Pass'";
$Result = mysql_query($Sql, $conn);
$Row = mysql_fetch_array($Result);
if ($Row['myCount'] > 0){
//Set session variables and redirect to private page
}
else{
//Incorrect username or password
}
}
...
Just like our registration page, we first need to create a connection to and select our database. Next the user input is sanitized and the password is hashed so that it can be compared to the hashed value in the database. Finally an SQL select statement is executed to return the number of matching records for our username and password combination. Unlikely as it may seem, the next part is what confused me the most: how to read the result back from the query. If the query is successful, "mysql_query" returns a "resource" ($Result) which I then passed to the "mysql_fetch_array" function which in turn returns an array to the fetched row, which in this case is made up of just one column "myCount" (an alias for the count(*) sql expression). I can then access the value of "myCount" through this associative array and grant/deny access accordingly.
What next?
With the authentication mechanism out of the way I will shift my focus on the application functionality itself: uploading and managing files, quota management and eMail notifications.
I must say however that even at this relatively early stage, the code is starting to look untidy and not as modular as I would like so I've been looking into the Model View Controller (MVC) pattern for PHP. Even from the outset it is clear that MVC would make the code much more maintainable by achieving a clear separation between application logic and the presentation layer (HTML). Also, using Object Oriented Principles (OOP) will add further modularity (separation of concerns) of the code. The downside to MVC is that it takes some getting used to and unless using an existing framework, it takes some effort to implement from scratch. Having said that, I have found some useful resources online to get me started so I might implement MVC on this project in time for my next post.
Subscribe to email feed
