PHP and MySQL

Introduction

Welcome again to my blog. This weeks blog will focus on the usage of the MySQL database in the PHP language. Below is a list of tasks that I will be addressing in this post.

• Logging into the SQL server using command line and performing some commands such as listing the databases
• Attempting to connect to SQL by using PHPMYADMIN
• Creating a database that stores usernames and passwords
• Modifying my PHP program from the previous lab session to connect to the database to authenticate the user

Working with MySQL using Command Line

The first thing I did was to hold the window key and type r, to obtain the run window. By typing cmd into the TextField provided and pressing enter, I opened the windows Command Line window. At first I couldn't connect to the MySql database. Later I found out that to connect to the MySQL database, you need to find the Bin folder, in the MySQL directory first. I typed the command below to go to the apprapriate directory.

-> CD C:\xampp\mysql\bin

The first time you log into the MySQL database, you need to login as root, since no other user exists yet.  To login as root I used the command

-> msql -uroot -p

Immediatly, I was promted for the root password. This is what the login looks like.

As soon as i logged into mysql the command line path changed to simply mysql. In this state functions can be run in the command line. Below is a list of some commands I used in this exercise.

1 2 3 4 5

mysql -uroot -p show databases GRANT ALL ON *.* TO Mark@localhost IDENTIFIED BY 'password' exit mysql -u Mark -p

Command 2 in the table above lists all the databases available. The command looks like this in the CMD.

Before Starting the next tasks, I decided to create a new user called Mark. I granted Mark all privileges on all databases. GRANT ALL *.* does this job. This command if followed by TO username@host where username is the login name and the host address is the Mysql address. The command is followed by IDENTIFIED BY 'PASSWORD' where the word between the apostrophes is the user's password. The command is listed line 3 of the table provided. To login with the new user, you have to run the exit command to exit mysql and then login again. To login in as user Mark I used the command in Line 5.

There are other interesting MySql commands to explore. You can select a list of users existing in the mysql database. You do this by executing the Query:

Select  user FROM mysql.user;

This command produces the following output

You can also use the command show Tables, to view the tables in a database. To do this, first you must select the database you are using and then execute the show tables command. Once you have a list of tables in the database, you can view the details of each table by typing "desc" followed by a table name. The image below shows how the use command is executed to select a database, showing the message Database changed. It also shows the results for show tables and the description of the login table.

Loging In PHPmyAdmin

To login into PHPmyAdmin I typed localhost in the web address and logged into xampp main screen which was discussed in a previously dedicated post. From there I used PHPmyAdmin. Where I clicked on the shortcut, I was requested to login. I logged in as user Mark which I had previously created in the Command Line. The login screen looks like this.

phpMyAdmin provides a lot of tools that automate the creation of databases and tables without the need of writing any SQL code. Databases are listed on the left of the screen, and by clicking on the database name, you can enter in another screen that allows you to edit the particular database. The images below are show screenshots of this tool.

The image below shows the initial screen after logging into phpMyAdmin. To select a Database, you simply need to click on it from the list in the left panel.

When a database is selected, all tables in the database are listed.The image below shows the options available when editing a database.

User privileges can also be handled using phpMyAdmin. This can be done by clicking on the privileges tab.  The image below shows the various access options, that a user can be granted or denied.

phpMyAdmin allows you to also edit tables and table contents. If you click on the first icon in the Action column in the table list, you are allowed directed to another screen that allows you to edit table contents. Various option are available at this point, and are visible in the picture below.

The insert tab provides an easy to use graphical screen to input records in the  table.

The Sql tab allows you to run queries on the database and provides a list of fields of the current table in use to make query building easier.

For more advanced queries, phpMyAdmin provides a query builder, similar to the one used in Microsoft Access. In this query building tool, fields and conditions can be creating using the GUI tools. Finally these tools output the equivalent SQL code.

phpMyAdmin allows you to save time while editing the database and since it is running on the apache server, it can be run from all PCs on the network (if not restricted). There are various mysql manager and admin tools available other than phpMyAdmin. When Working from a local machine I prefer using SQLYog Which provides similar functionality, but is more user friendly.

A screenshot of SqlYog community edition

Creating The Database

To create the database I used the create database Query. The table below lists the queries I will be using in this section.

6 7 8 9

create Database phpfileupload use phpfileupload CREATE TABLE `login` ( `UserName` VARCHAR (10) NOT NULL, `Password` VARCHAR (10) NOT NULL, CONSTRAINT PK_Login PRIMARY KEY (UserName) ); INSERT INTO login (username, PASSWORD) VALUES ('Mark','Galea')

I assigned phpfileupload as the name for my database. Command number 6 shows how to create a database and command 7 directs the queries to a particular databases. The CREATE TABLE command on line 8 is used to create a table in the database, where login is the table name, UserName and Password are columns of the table and UserName is the primary key of the table. VARCHAR is the datatype to Store String values and the value in the brackets is the length of the string (number of characters allowed). NOT NULL indicates that the fields cannot be left empty.

Command 9 shows a simple insert command used to insert values in the table Login. In this case I inserted a row with Mark as Username and Galea as password.

Login using the login table in the MySQL database

For this task I used the same code as last weeks post, but I altered my code such that it obtains a list of logins from my database, instead of an inbuilt array. The Code below shows the what happens when the Login button is clicked in the login screen.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52

<?php if(isset($_POST['btnLogin']))     {              $con = mysql_connect("localhost","Mark","password");              if (!$con)         {         die('Could not connect: ' . mysql_error());         }         else         {         $username = $_POST['txtUserName'];         $password = $_POST['txtPassword'];         $Remember = isset($_POST['ckRemember'])? true:false;                  mysql_select_db("phpfileupload",$con);                   $SqlQuery= sprintf("SELECT username,password from login where username = '%s'",         mysql_real_escape_string($username));                  $Passwords=mysql_query($SqlQuery);                  mysql_close($con);              while($row= mysql_fetch_array($Passwords))           {             if ($row['username'] == $username && $row['password']==$password )             {                          $date = getdate();                          setcookie('LastLogin', Date('d/m/Y').' '.$date[hours].':' .$date[minutes].':'. $date[seconds],time() + 3600);                    if ($Remember == true)                 {                     $_SESSION['User']=$username;                 }                 else                 {                     session_destroy();                 }                  header('Location: fileuploadwelcome.php?Name=' . $username);             }         }        echo "<script>alert('Invalid Login')</script>";     }                } ?>

Line 5 shows the mysql_connect() command. Given the host address, username and password of a database, this command creates a connection. Line 7 show the code that tests the connection,. If the connection fails, the die function displays an error message. The function mysql_select_db() connects to a database. the function takes in the database name and a connection as inputs.

To check if a login exists, I queried the table login to display the user with the login name provided. Instead of using the normal concatenation sign, I used the code shown on lines 20 and 21. The function sprintf is used to output a string and save it in a variable. This function also allows you to insert a string in the original string. The  new strings will replace the %s characters. In my case I used this method to insert the username in my query string. I also used the method mysql_real_escape_string($username)  such that any special characters in the variable are assigned an escape sequence such that the string becomes safe to be inserted in a query and SQL injection is avoided.

The variable $Passwords is assigned the results of the query on line 23. The query is executed with the command mysql_query() which takes in a string command as input. As soon are the data needed is retrieved, I used the command mysql_close($con) to close the database connection. Rows in the variable $Passwords are obtained using the function mysql_fetch_array($Passwords).  Values in rows can be viewed by writing the row name in the square brackets of the row array. Example $Row['UserName'] gets the username from the row. In line 29 I verify the login details to check if they are valid. The rest of the code remains unchanged from last week's session.

It is good practice to avoid storing passwords as plain text. The best way is to encrypt or generate a hash code for the passwords and store these in the mySql Database. To authenticate a login then you must simply encrypt the password and compare the encryption result with the encrypted password in the database. In this particular example I used no encryption, but it can easily be added later.

Conclusion

PHP provides quite a good and easy to use functionality for queering the database. In fact PHP avoids using DataReaders or DataAdapters which are slow and difficult to handle. Instead it provides simple functions that given a connection and a database, they are able to run an SQL command and Retrieve SQL Data easily.