SQL and PHP

Introduction

Having a dynamic website that responds quickly to user requests and provides updated information 24/7 is a must. Nowadays, many organizations are aware that a web site is not a onetime shot; a web site needs maintenance.

A web site that is frequently updated will attract more audience; provide always the latest news and the latest offers if promoting goods.

Most well structured web sites are supported by a database backend. Such backend provides a way to easily store, manage and retrieve data. In simple terms, if all data is stored in a database maintenance is easier.

Nowadays, databases enable you to store different sorts of data and amongst others in conjunction with a web site a database can be used to:

• Store user accounts
• Store products
• Record all transactions
• Keep track of visitors and actions
• Store images and files

To implement a database backend, you need to have a clear view of the requirements. Reason being that pushing data in one location may cause a size concern; store only what is required.

More than a file, a database backend is an environment and to implement such environment you need to install a Database Management System (DBMS). A DBMS provides the require tools to create, manage, maintain and secure your databases. The most commonly used are:

• Oracle database
• Microsoft SQL Server
• MySQL

In this blog I will use MySQL that is including in the XAMPP package, and will describe how to create a log-in page using a database table with PHP scripting.

Task Description

This blog describes my experience while performing a task assigned by my tutor. The task consists of the following stages:

• Log into the SQL server using command line and perform some commands such as listing the databases.
• Attempt to connect to SQL by using PHPMYADMIN.
• Create a database that stores usernames and passwords.
• Modify your PHP program from the previous lab session to connect to the database to authenticate the user.

MySQL command line

Open command line

In XAMPP the MySQL tools are located in <XAMPP installation folder>\mysql\bin. To access MySQL from a command line:

1.    Open a command line interface.

2.    Open the MySQL folder C:\XAMPP\mysql\bin and open MySQL using the following command line code mysql -u root -p

3.    The command line will prompt for the MySQL root password.

Command line examples

The following syntax will list all databases:

SHOW DATABASES;

The following syntax creates a new database named blog_database:

CREATE DATABASE blog_database;

The show databases syntax can be used to ensure that the new database has been added,

The following syntax adds a new user  ‘omar’ having a password ‘12345’:

CREATE USER 'omar'@'localhost' IDENTIFIED BY '12345'

In MySQL, users are stored in mysql.user table. To list all users the following syntax is used:

SELECT host,user FROM mysql.user;

The following syntax is used to add a new table tblUsers to the Blog_Database.

CREATE TABLE `Blog_Database`.`tblUsers` (

`uname` VARCHAR( 20 ) NOT NULL ,

`fldName` VARCHAR( 50 ) NOT NULL ,

`fldSurname` VARCHAR( 50 ) NOT NULL ,

`fldPassword` VARCHAR( 50 ) NOT NULL ,

`fldEmail` VARCHAR( 50 ) NOT NULL ,

PRIMARY KEY ( `uname` )

) ENGINE = InnoDB;

To display information on a specific table, the following syntax is used:

desc <table name>;

To display the list of tables in a database, the following syntax is used:

show tables;

Using phpMyAdmin

This free software is written in PHP and enables you to manage instances of MySQL over the web. For more information, refer to phpMyAdmin Home page. phpMyAdmin is also included with the XAMPP package. To launch phpMyAdmin from a XAMPP installation, launch the following page from an internet browser: http://localhost/phpmyadmin/ and key in your MySQL user name and password.

Manage your MySQL Databases

Create and run sql statements 

 

Manage tables

Insert data into a table

Easy to use query builder

Manage users and privileges

Alternatives

In my opinion, phpMyAdmin is a powerful tool to have but personally to manage MySQL instances I prefer using SQL yog or MySQL Workbench; interface is more intuitive and very easy to use.

Create a database

To create a database backend for my login page, I performed the following steps.

Step 1: Create a new database ‘blog_database’ using the following syntax:

CREATE DATABASE blog_database;

Step 2: Create a new table ‘tblUsers’ using the following SQL syntax:

CREATE TABLE `Blog_Database`.`tblUsers` (

`uname` VARCHAR( 20 ) NOT NULL ,

`fldName` VARCHAR( 50 ) NOT NULL ,

`fldSurname` VARCHAR( 50 ) NOT NULL ,

`fldPassword` VARCHAR( 50 ) NOT NULL ,                                   

`fldEmail` VARCHAR( 50 ) NOT NULL ,

PRIMARY KEY ( `uname` )

) ENGINE = InnoDB;

Step 3: Add some data (three users) using the following SQL syntax:

INSERT INTO `tblUsers`

(`uname`, `fldName`, `fldSurname`, `fldPassword`, `fldEmail`)

VALUES

('christ', 'Christine', 'Bezzina', '53241', 'christb@inbox.com');

INSERT INTO `tblUsers`

(`uname`, `fldName`, `fldSurname`, `fldPassword`, `fldEmail`)

VALUES

('eliza', 'Eliza', 'Grech', '101010', 'eligre@inbox.com');

INSERT INTO `tblUsers`

(`uname`, `fldName`, `fldSurname`, `fldPassword`, `fldEmail`)

VALUES

('omar', 'Omar', 'Zammit', '12345', 'omarz@inbox.net');

Step 4: Create a user with only SELECT privilege over tblUser table.

CREATE USER 'webuser'@'localhost' IDENTIFIED BY '12345';

REVOKE ALL PRIVILEGES ON *.* FROM webuser@localhost;

GRANT SELECT ON blog_database.tblUsers TO webuser@localhost;

Step 5: View all table data from a command line

SELECT * FROM blog_database.tblUsers;

Log-in pages

The log-in page enables the user to enter valid credentials to log-in to the system. Once the credentials are entered the log-in page will search the values from a database table using a select statement similar to the following:

SELECT * FROM `tblusers` WHERE uname='$User' and fldPassword='$Password'

Where $User and $Password are the user name and password parameters.

If the values are found, the database table row is stored in a variable and the fields’ data is displayed in a new page (welcome.php).

The index.php page

When this page is submitted, the values supplied by the user are validated using the validateUser(<User name>,<Password>) function; Line 10.

The values returned by the validateUser() function are stored in a session variable in line 19. Once stored, the welcome.php page is called.

If validateUser() returns a null value, the error at line 25 is displayed in the index.php page.

In line 33, the validateUser() function, creates an SQL connection using a MySQL user account.

In line 37 is using the mysql_select_db, to instruct the MySQL instance to use and run the queries on the blog_database.

In line 40, the SELECT statement will search for the user name and password submitted by the user. If credentials submitted are valid, the statement will return a database table row; this is stored in $rows.

In line 44, the first row is split into a new array ($row) and in line 50 this array is returned as a function result.

The welcome.php page

In line 13, the welcome.php page checks if a session containing the user details, exists. If the session is found, the details are stored in a variable $username (line 18). Lines 21 to 24 display the user information.

Using the log-in page

Launching index.php

Submitting invalid credentials – the error message

Submitting valid credentials – the welcome page

A bit of security

It is highly recommended that passwords are not stored into a database in plain text; if the database password is compromised, all passwords are accessible. The best way to protect your password is by using encryption and hashing algorithms. Such cryptographic methods will encrypt the password in an unreadable format and store the encrypted value. During validation, the user input is encrypted and compared with the stored value.

Crypt

PHP has its own cryptographic function and can be called using the following syntax:

$Variable= crypt (<Value>, <Salt>);

Where Value is the string text to encrypt and Salt the characters that will be used to encrypt the Value.

The crypt function supports four types of encryption algorithms:

Algorithm	Salt - Number of characters
CRYPT_STD_DES	2
CRYPT_EXT_DES	9
CRYPT_MD5	12
CRYPT_BLOWFISH	16

For more information, refer to PHP crypt function.

MD5

MD5 is one of the most commonly used one way hashing algorithm. This method transforms a message into a fixed-length outcome. Making the contents difficult to translate and decipher. To transform a value using MD5, the following PHP syntax is used:

$variable = md5(<Value>);

For more information, refer to the PHP MD5 function.

Other methods

Other more secure methods exist, and amongst others, one can find:

• Mcrypt – an encryption library with 22 different algorithms. For more information, refer to the Mcrypt manual.
• Mhash – an encryption library with 12 different algorithms. For more information, refer to the Mhash manual.

Conclusion

The phpMyAdmin included with the XAMPP package enabled me to perform some database tasks. The tool is powerful and easy to use but personally I prefer SQL yog or MySQL Workbench.

While performing the steps in this task, I was really impressed how easy it is to interface with a database from a PHP script; compared to other scripts it’s pretty straight forward.

Various encryption methods exist to protect plain text passwords, I prefer MD5 because it’s easy to implement and secure.

Resources

Download the database log-in sample files.

Sessions and Cookies in PHP

Introduction

Sessions and Cookies enable web developers to store data in a way that is easily accessible from multiple pages. If data is stored using such mechanisms, users will not lose data when navigating from one page to another.

In this blog I will describe my experience and what I’ve learned while performing a task assigned by my tutor.

Cookies

Cookies are stored on the client side and usually are used to identify a user. In PHP a cookie can be created using the following syntax:

setcookie(<Cookie name>,<Cookie value>,<Expire date in seconds>);

Cookies are stored in an array ($_COOKIE), and to retrieve the value of a cookie the following syntax is used:

$_COOKIE[<Cookie name>];

Cookies can be deleted by setting an expired date. For example, the following code deletes ckname cookie.

setcookie("ckname", "", time()-3600);

The isset() function can be used to check if a cookie exists, this returns true if a cookie exists and false if not. Such function is commonly used in an if statement as follows:

If(isset($_COOKIE["ckname"]))

echo "Cookie found";

else

echo "Cookie not found";

Sessions

Session variables are stored on the server and are available to all pages. Sessions related to a user are deleted when the user leaves the web site. When a session is created a unique id (UID) is created and stored in a cookie on the client side, this will be used by the server to identify the user.

Before using sessions, the following syntax is used to start the sessions:

session_start();

The following syntax is used to store a session variable:

$_SESSION[<Session variable name>]=<Value to store>;

If the variable name does not exist, it will be automatically created. A session variable can be destroyed by using the following syntax:

unset($_SESSION[<Session variable name>]);

To destroy completely a session and all its variables, the following syntax can be used:

session_destroy();

Task Description

This blog describes my experience while performing a task assigned by my tutor. The task consists of the following stages:

• Use PHP to create a login screen that accepts a user and password that 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.
• Replace the cookie mechanism with PHP sessions

Using Cookies - Login Screen

Design

The log-in page, index.php, enables the user to key-in credentials and log-in to the system. The Remember my credentials enable the user to store a log-in cookie and log-in automatically.

The welcome page, validate.php, displays the user name and enables the user to log-out and delete the log-in cookie.

Flow

The following drawing shows the flow when the index.php page is launched.

Please note that when a ‘Remember me’ cookie exists, the user is redirected automatically to validate.php. When a user clicks the Log-in button in index.php, user name and password keyed in by the user are passed to the validate.php. The following drawing shows flow when the validate.php page is launched.

When a login cookie is found, the cookie value is read and displayed in the welcome page. If the cookie is not present, the user name and password are compared with a text file; containing user accounts. If the account is valid and the ‘Remember Me’ was selected, the log-in cookie is created.

A log me out link will be added to enable the user to log-out and delete the log-in cookie. When this link is clicked, a flag is created and validate.php is launched. As shown in the drawing above, when validate.php is loaded, it checks the flag and if exists the cookie is deleted and the user is redirected to index.php.

Development

The index.php page

In line 42, the script is checking if a parameter ErrorMessage exists. If this parameter exists in lines 43 to 45 an error image and the error text is added to the page.

In line 49, the script is checking if a cookie ckname exists; this is created when Remember my credentials is selected during a log-in. If the cookie exists, the script calls validate.php with a flag checkcookie; this flag is used to instruct validate.php to use the cookie and skip the validation.

The validate.php page

At line 21 the script is checking if a parameter resetCookie exists; this is parameter is created when a user clicks Log-out. If this parameter exists, the script calls resetCookie() function.

The resetCookie() will delete the cookie by setting the expiry date.

In line 31, the script checks if checkcookie parameter exists; this parameter is sent by index.php to use cookies and skip validation. If exists, the cookie value (user name) is stored in a global variable $LoggedinName. In line 35, some text is added to notify the user that log-in was done using cookies.

If checkcookie parameter is not found, the script within lines 37 and 58 is executed. In line 39, the script is checking the user input (username and password). If the input is empty the index.php is launched with an error parameter (Line 55). If the input is not empty, the user name and password are validated using the validateUser(<User name>,<Password>) function; will be explained later in this blog. If the credentials are invalid, the index.php is launched with an error parameter (Line 47).

In line 62, the script checks if Remember my credentials is selected; if selected, a cookie with the username is created.

Test

I will perform some tests to ensure that the system is working as expected.

ü  Test the result when a username and password are not provided and the user clicks Log-in.

ü  Test the result when an invalid username and password are provided.

ü  Test the result when valid credentials are provided and Remember my credentials not selected

ü  Test the result when Remember my credentials is selected and the user tries to access the index.php page for the second time.

Using Sessions - Login Screen

Design

The log-in page, index.php, enables the user to key-in credentials and log-in to the system.

The welcome page, welcome.php, displays the user name and the session id.

Flow

The following drawing shows the flow when the index.php page is launched.

This page contains all the PHP syntax to validate the user, if the user is valid, this page starts a session and calls the welcome.php page. When welcome.php is launched, the session value and the session ID are displayed. The following is the welcome.php flow:

Important to note that sessions can be removed from the client side by closing the Internet browser. In addition the server can be configured to remove the session after a pre defined time, this can be done from a PHP script by adding the following:

ini_set('session.gc_maxlifetime', '<time>');

Where time is an integer in seconds.

Development

The index.php page

In line 7, the isset() function is checking the user input, if the input is not empty the input is validated.

In line 8 using the validateUser(<User name>,<Password>) function, the credentials are validated and if valid the script within lines 9 and 12 is executed.

NOTE: validateUser() function will be explained later in this blog.

In line 10 the session is started and in line 11 a new session (user) having the username is created. At line 12 the welcome.php is called to display the values.

If the credentials are invalid, the error to display is stored in variable $error and displayed using the following code:

The welcome.php page

In line 13 the isset() function is checking if a user session exists. If the session does not exist the user is redirected to the index.php page (line 14). If the session exists, the value is stored in a variable $username and displayed using the following code:

In line 25, the session id is displayed using the session_id() function.

Test

I will perform some tests to ensure that the system is working as expected.

ü  Test the result when an invalid username and password are provided.

ü  Test the result when valid credentials are provided.

Validation

Flow

To validate user credentials, a function was created that gets the user input and compares the credentials with a list stored in a text file. The flow is as follows:

Development

In line 23, using the fopen() function, the file is opened in readonly mode. Iteration is done using a while loop in line 27. Terhehe iteration will loop until the pointer is at the end of file; this is done using the feof() function.

Each line is split in two and stored in an array in line 29; text file is tab delimited.

The array values, $SingleUser[0] and $SingleUser[1] are compared with the $User and the $Password parameters respectively. If these values match the function returns true.

When all text file credentials are checked and no values match the function returns false.

Conclusion

Working with cookies and sessions was an interesting and challenging task. There are more things to learn about this matter and in this blog I’m describing the basics. Hope this helps.

Happy coding…

Resources

Download the session  and the cookie sample files from Windows Live.