Server-Side - PHP and MySQL

Introduction

---

When we started discussing server side technologies we talked about the different types of servers and technologies involved in a typical web application. We discussed web servers, server-side languages and we also briefly mentioned database servers. In a client-server model the database server takes requests from the client who accesses the database through its command line interface or by connecting to a database socket. 

In the most part database servers in a dynamic web application help manage information which is either create by the user or generated by the application. Tasks performed by a database server include;

• Processing data modifications and retrieval requests
• Performing data-intensive processing
• Enforce database rules and constraints
• Enforce database security

Most of the tasks performed by a relational database server are done by a DBMS (Database Management System). The DBMS manages the creation, maintenance and use of a databases. A database designer can communicate with a database server either by connecting to the database from a command line terminal or by using a database management tool that come with the database package. Both of these methods require the designer to have a good knowledge of SQL. SQL comes in many flavours depending on the implementation of the database engine. There are two classes of SQL command which are DDL (Data Definition language) and DML (Data Manipulation Language).


Task Summary

---

Throughout this blog we will be using MySQL as a database to store information related to our tasks. MySQL was installed with the installation of XAMP. The tasks that will be demonstrated throughout this blog are;

• Log into the database server using the command line and perform some commands such as listing of databases
• Attempt to connect to SQL using PHPMYADMIN
• Create a database that stores user names and passwords
• Modify program created in the previous blog post as to connect to the database to authenticate the user.

Log into the database server using CLI and perform some commands

---

Throughout this section we will be using commands used to manage a database from the command line interface. First we need to log in to the MySQL command line terminal. To do this the following command is issued from the command prompt;

Once the command is entered a password has to be entered. The executable which is called when this command is dispatched resides in the XAMP/bin folder.

Since we need a database to do the tasks required I decided to create a database which will host the neccessary tables later. The name given to the database is sqlblogdb. This can be achieved by using the create database command. After each command that is executed a result message is displayed which states the time and status of the command execution.

CREATE DATABASE sqlblogdb;

To confirm that the database is created the show databases command can be used to list the databases available under this mysql instance.

SHOW DATABASES;

The database is listed and to use the database the use database command can be used.

This command can be used to use a special database called mysql.

USE mysql;

This database contains data pertaining to the mysql database server instance such as users.
To list the users currently available in mysql the following command can be dispatched;

SELECT user FROM mysql.user;

To create a new user the create user command is used followed by identified by followed by the password.

CREATE user ‘gmifsud’@’localhost’ IDENTIFIED BY ‘gregory1983’;

Since this user was just created the rights allocated are the bare minimum which means that if the user tries to log in the mysql system database will not be available. To change the password for a user the following command is used;

SET PASSWORD FOR ‘user’@’hostname’ = PASSWORD(‘newpassword’);

Changing the privileges of a user requires the use of the GRANT command. This command requires the database and host name as parameters along with any specific tables being effected by the privilege. This command is usually followed by a FLUSH command to reset the change into affect.

GRANT ALL PRIVILEGES ON mysql.* to ‘gmifsud’@’localhost’;
FLUSH PRIVILEGES;

Having a better understanding of the system database now we can proceed in managing the newly created database sqlblogdb. Since the database has just been created no tables are available. We can confirm this by using the SHOW TABLES command once using the database. To create a table the DDL syntax CREATE TABLE is used. For this demonstration we will be creating a users table named tbl_Users which will contain three fields the id, username and password.


CREATE TABLE tbl_Users (
id INT NOT NULL AUTO_INCREMENT,
username VARCHAR(60) NOT NULL,
password VARCHAR(60) NOT NULL,
firstname VARCHAR(60) NOT NULL,
surname VARCHAR(60) NOT NULL,
email VARCHAR(60) NOT NULL,
PRIMARY KEY (id)
) ENGINE = InnoDB;

Attempt to connect to phpMyAdmin

---

phpMyAdmin is a web based open source tool who objective to aid database administrators in managing mysql. Amongst other features phpMyAdmin helps in manging databases by creating, deleting and modifing databases as well as administer users and privileges. phpMyAdmin is accessed by navigating to the <domainname>/phpmyadmin which in our current scenario is http://localhost/phpmyadmin/. In our current implementation of XAMP phpMyAdmin can be accessed from the XAMP dashboard on the left navigation bar. On entering the web application a user name and password is requested. The credentials inputted are the same used in accessing mysql from the command line interface. 

From the home page on the left hand side of the screen the databases are listed. For this demonstration we will navigate through some of the options available. Databases can be managed by selecting the Databases tab. With each database listed an icon is available that links to the privileges for that database.

The privileges screen shows a list of users each with an icon which links to the privileges for that user. When editing privileges for a user the screen is divided into four sections;

• Global privileges
• Database-specific privileges
• Change password
• Change login information / Copy User

In the SQL tab a database administrator can enter SQL syntax to manage databases. 

Each database structure can be managed by clicking on the database. Columns can be created, modified or deleted.

Create a database to hold user names and password

---

In the first task of this blog we already created the database and table (tbl_Users) which will be storing the user names and password.

To create the database the following sql was entered in the command line interface.

CREATE TABLE tbl_Users (
id INT NOT NULL AUTO_INCREMENT,
username VARCHAR(60) NOT NULL,
password VARCHAR(60) NOT NULL,
firstname VARCHAR(60) NOT NULL,
surname VARCHAR(60) NOT NULL,
email VARCHAR(60) NOT NULL,
PRIMARY KEY (id)
) ENGINE = InnoDB;

Once the table is created and confirmed by calling show tables some data is populated in the table by calling the sql queries shown below;

INSERT INTO tbl_Users 

(username,password,firstname,surname,email) VALUES

(‘gmifsud’,’gregory1983’,’Gregory’,’Mifsud’,’gregorymifsud@gmail.com’);
INSERT INTO tbl_Users
(username,password,firstname,surname,email)

VALUES

(‘bmifsud’,’bmifsud1987’,’Bernard’,’Mifsud’,’bmifsud@gmail.com’);

INSERT INTO tbl_Users
(username,password,firstname,surname,email)
VALUES

(‘dsant’,’dsant1987’,’Duncan’,’Sant’,’dsant@gmail.com’);
To confirm that the data has been populated successfully a query was executed.

SELECT * FROM tbl_Users;

Login Screen using database

---

At this point we have the table (tbl_Users) which contains the users that will be validated through the login screen and the login screen described in the previous two blog posts. Our current system validates the user details entered by matching with data retrieved from a text file. In this task we have to replace the function which validates the user with a function that connects to the database and checks that the entered user and password match one of the records available in the table.

The function validating the user with the tbl_Users first connects to the database by using the mysql_connect function. This function takes a hostname, username and password as arguments. Once connected to the database the database is selected which in this case is tbl_Users by using the mysql_select_db. This function takes a database and the previously instantiated connection. Since we are connected to the database a query is dispatched to the table were all the users are selected using the mysql_query function. 

The result from this function is an associative array were the key is the column name set for the tables. The array is iterated and each of the two fields is then matched against the inputted username and password.

Conclusion

---

Writing this blog has been very informative and interesting. The login application has some security issues since no filtering is being done on the fields entered by the user. This could easily lead to sql injection. There are various security steps that once could take to make his/her web application more secure. One of the function I research which helps prevent sql inject was mysql_real_escape_string(). This function escapes special characters such as ‘ or = which are the main characters used in sql injection.

Server-Side - Using Session and Cookies in PHP

Introduction

---

In last week’s blog we discussed PHP as a server-side scripting language, we also got through the basics of PHP such as arrays. In this week’s blog entry we will focus on some of the more advanced mechanisms of php.

The HTTP is a stateless protocol which implies that a web server doesn’t differentiate or keep track of requests coming from a user’s browser. The hypertext transfer protocol doesn’t provide a way by which requests from different clients can be identified. Having a state-less systems means that no resources are taken to keep track of a requests state. Not being able to identify the client meant that a web application wasn’t able to know whether a user is logged in or what the user’s preferences were. This problem was countered by creating a mechanism called cookies by which states could be managed. To better understand cookies one should take a closer look at an instance of an HTTP transaction in conjunction with the cookie mechanism.

Whenever a user makes a request the server includes a Set-Cookie header in the response. This means that a cookie will be set and be available in future requests within the Cookie header. A cookie is stored on the client and data is stored in the form of a key-value pair. The cookie can be used to differentiate between clients. In PHP cookies are created by calling the SetCookie() function. The SetCookie() function must be placed before the HTML tag to have effect. The SetCookie() function takes a name which is used as a key to the value passes as a second parameter. Other arguments include the expiration date, path and the domain name. To get a value present in a cookie the $_COOKIE variable is used. A value stored in a $_COOKIE can be retrieved by allocating a key with the array notation [] in PHP such as $_COOKIE[“UserId”]. Deletion of a cookie is performed by setting the expiration date of a cookie in the past.

Sessions are used to extend state management over the web by storing data pertaining to a particular client. As opposed to cookies sessions are stored on the server while cookies are stored on the client’s machine. Data in sessions is stored in a session data store were it gets updated with each request. The unique id which identifies a record within a session data store is called the session identifier. In PHP the function call session_start() is called to determine whether a session identifier is included in the request. If a session is present then the data for that request is made avaiable in the $_SESSION variable. Retrieving sessions in PHP is similar to retrieving cookie except $_SESSION is used. To free a session variable the unset() function can be used while to completely destroy a session as the name implies the session_destroy() is used.




Task Summary

---

Throughout this section of this blog entry the following tasks will be performed;

• Create a login screen that accepts a user name and a password which are validated on the server-side.
• Add a “remember me“ button that uses a cookie so that the user does not have to log in again.
• Use sessions to store user information

Create login screen that accepts a user name and a password and which are validated on the server-side

---

A login screen is essentially a form from which a user can be authenticated to access some restricted section of a web site. In our current scenario the user will be authenticated to view a home page. The first thing I considered is the file I needed to create to have a good working environment. For this task I created three files; the login page (login.php), the login code behind file (logincb.php), a functions file and the home page.

Each of the files will contain code specific to the task at hand. In this case the user will navigate to the login screen through the portal. The login screen contains a text field for the user name a text field for the password and a submit button to post the page.

The controls mentioned are placed with a form tag which point to to the code behind file logincb.php. This means that a post action will be in effect on logincb.php on submitting the form. Once in the code behind file I started to validate the user inputted fields. The validation function will do the following tasks;

• Check that required fields are not empty
• Validate the fields with the user name and password in store.

In PHP one can check if a field is empty by using an inbuilt function called empty() (Other functions such as isset() can be used. The main difference between empty() and isset() is that isset() return false whenever a variable doesn’t exist) which will return true if the fields are kept empty.





Each if statement check whether both user name and password are present. If one of the fields is missing the user is redirected to the login.php screen along with three arguments; the first argument holds the error message and the last two arguments determine which of the fields is empty and show an asterix next to the respective text field.

If the the fist part of this validation procedure is skipped the next step would be to validate the entered user name and password against a list of user names and passwords. The aforementioned list is created in a text file (cred.txt). The functions which will handle this part of the validation is hosted in the functions.php file. In order to gain access to the function the include() had to be used. The validateUserId() function creates a pointer to the text file by calling the fopen() function. Iterating through the list is accomplished by using a while loop which with each iteration checks for the end of file. Also with each iteration the line loaded from the text file is split using preg_split() function and compared to the user credentials entered by the user.

If details do not match any of the lines loaded an error message is return which is then obtained by using the $_GET variable and printing the message.

If the details entered match one of the lines loaded a true value is returned from the function and the user is redirected to the home page using the header(“Location:xxxxx”) construct. This concludes the second step of this validation algorithm.





Add a “remember me“ button that uses a cookie to remember a successful login.

---

The “remember me” feature is quite common amongst web sites. This feature allows for a cookie variable to be stored on the client’s machine. Whenever the client tries to navigate to the same page the web site will use the cookie stored to authenticate the user. The first thing included in the login screen was a check box with an name of “chkRemember”. On submit of the login screen whenever the checkbox is checked a cookie is set by using the setcookie()  function with a duration of one week.

The user is then redirected to the home page. In the home page the user name is shown by accessing the cookie variable $_COOKIE. A post conditional statement was set to display the user name on the home page upon determining the way the home page was accessed.

An anchor tag is also included which provides the user a way to log out and expire the cookie. The anchor tag points to the code behind file logincb.php and passes a parameter logout=true which is then obtained by accessing the $_GET variable. A function was created in the functions.php file which expires a cookie given two parameters; the cookie name and the cookie value.

If the user decides to close the web page without logging out and tries to access the login page the user is redirected to the home page.






Using Sessions

---

Sessions extend the cookie concept on the web. By using sessions information can be transfered from one page to another. In this case the user name once authenticated will be displayed in each page by accessing the $_SESSION variable. Upon authentication the function session_start()  is called. This function either starts or resumes a session based on the current session identifier. The session identifier can be obtained by calling the built-in function session_id(). After initialising the session the already authenticated details are assigned to the $_SESSION variable each with their respective names.





The user name is displayed in a place holder in the home.php page.





Each page that is visited after logging is started with the snippet shown below. This allows for the page to check if the user is logged in and if so to display the user name.






Conclusion

---

There are a number of security issues that one must watch out for when using cookies and sessions. One must be diligent by not storing sensitive information in cookies such as passwords.