This is PHP with MySQL week ! I had already done some work previously with MySQL but usually using the VB.net platform. I usually prefer to use MS SQL over MySQL but this is basically because I am more familiar with the former. My experience with MySQL comes more from tweaking web sites that use this popular database system in their CMS back-ends.
I also did some reading about the history of the MySQL relational database system simply out of interest where this free system came from. I was not aware, for example, that MySQL is now owned by Oracle and that it sells for USD 2000. Thankfully MySQL has a "Community Version" which is licensed under the GPL. I must admit I had to do some searching to be able to understand the difference between the Community Version and the Standard Version. The Enterprise Version which is even more expensive does seem to have advanced features such as "partitioning" and an integrated backup system, however the difference between the Standard and Community editions was not immediately clear. It seems that basically both version have the same functionality and defer only in the availability of (Oracle) support and the licensing policy. The Community edition is published under the GPL so if the application being developed is not published under the GPL license it is not legal to bundle MySQL with the application and the user would have to purchase the Standard Edition at least. When compared with the limitations that MSSQL Express has over the commercial edition of the same database, the MySQL version are much more close. This in turn has had to be one of the main reasons why MySQL is so popular for web based applications.
The MySQL Command Line
Although I had used MySQL in the past, it was usually remotely through shared hosting on third party servers so I rarely had the opportunity to use the command line. On shared hosting systems, Telnet is usually not allowed so there is not much chance of using the command line. I had done some work with MySQL with Delphi using ODBC connectors as well as with VB.NET.
Next screen shows logging into the local MySQL server using the command line interface. The server reports that the version is the "Community" version.
Next screen shows use of the "status" command which displays the current status of the server. It also shows the character set encoding. I have had some bad experiences with the encoding of MySQL databases in my work designing and implementing websites using Joomla. I have had to produce languages in various languages including Maltese, Arabic and even some Japanese. Joomla, by default installs with an encoding that is not capable of displaying the special characters in these languages. To my detriment I discovered that there is no way, through phpMyAdmin to change the encoding globally. This meant that I had to change the encoding for every table separately which meant a lot of extra work. Usually I install Joomla through the automated "Fantastico" panel which installs it automatically and for some reason it chooses a character set that does not display the special characters in these languages correctly.
Next screen shows the help content and the root user connecting. It also shows that when first connected, there are no databases selected.
Next screen shows the "show databases" command which lists the databases that are available. It shows the "test" database that was created in another series of experiments prior to my writing of this blog.
This next screen shows selecting a database, in this case selecting the "test" database and then using the "show tables" command which lists the existing tables, namely the "users" database.
The next screen shows creating a new table called "userstable" and then listing the tables available which includes this new table proving that it was created successfully.
Usually when I do any work with MySQL I use a third party application called "Navicat for MySQL" which has the look and feel of the "SQL Developement Studio" that comes bundled with Microsoft SQL Server which I am much more accustomed to. The next screen show shows Navicat in action.
Checking login against a MySQL Database
I have modified the code so that instead of comparing the login and password with values stored in an array, we now compare them with records in a database.
The code below is from the page that generates the form. I have originally made all the markup to draw the form in an "echo" block but after reading I realised that this is not the preferred method although it works just as well. It is better to open and close the "php tag" rather then echo markup to keep the script more understandable.
loginFormDbase.php
<html>
<head>
<title>Php Form</title>
</head>
<body>
<?php
session_start();
if(isset($_SESSION['user'])) {echo "Welcome " . $_SESSION['user'] . "!<br />";
echo "<form name='login_form' method='post' action='logoutdbase.php'>
<input type='submit' name='Logout' value='logout'>";
}
else {?>
<form name='login_form' method='post' action='checklogindbase.php'>
<table border='1' width='30%'><tr><td>
<table width='100%' cellpadding='10' cellspacing='1'>
<tr><td colspan='2'><center>Login Form</center></td></tr>
<tr><td>Login Name</td><td><input name='user' type='text' id='user'></td></tr>
<tr><td>Password</td><td><input name='pass' type='password' id='pass'></td></tr>
<tr><td>Remember me</td><td>
<input type='checkbox' name='rememberme' id='rememberme' value='yes'></td></tr>
<tr><td colspan='2'><input type='submit' name='Submit' value='Login'></td></tr>
</table>
</td></tr></table>
<?php
}
?>
</body>
</html>
The next script checks the login and password against the database. I have enclosed the processes in this script with the MySQL connection "If" block to ensure that the script does not attempt to continue if a connection to the underlying database cannot be made for some reason. The if (is_resource($result)) block which also surrounds the processes on this page is another safety measure and is there to make sure that the script is terminated if a result set is not returned from the MySQL transaction.
During lectures we also attempted to hack our own scripts using SQL injection techniques. This was not too difficult to achieve as we had not yet included any measure to prevent these attacks. The @mysql_real_escape_string replaces suspicious characters and should be effective to prevent SQL injection attacks.
During lectures we also attempted to hack our own scripts using SQL injection techniques. This was not too difficult to achieve as we had not yet included any measure to prevent these attacks. The @mysql_real_escape_string replaces suspicious characters and should be effective to prevent SQL injection attacks.
checklogindbase.php
<?php
//get variables from the form
$user=$_POST['user'];
$pass=$_POST['pass'];
//remove to protect against sql injection
$user = mysql_real_escape_string($user);
$pass = mysql_real_escape_string($pass);
//Open connection to the local MySQL Server
$con = mysql_connect("localhost","root","root");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
//will only continue if a good connection is made
else {
mysql_select_db("test", $con);
$result = mysql_query("SELECT * FROM usersTable");
//Will only continue if the result has been retreived from the database
if (is_resource($result))
{
$loginOk = false;
while($row = mysql_fetch_array($result))
{
if (($row['username'] == $user) and ($row['password'] == $pass)) {
$loginOk = true;
//Remember me was checked so we set the session
if(isset($_POST['rememberme'])) {$rememberme=$_POST['rememberme'];
session_start();
echo " and we will remember you too !";
echo "<a href='loginFormDbase.php'><br />Click to try again</a>";
$_SESSION['user'] = $user; // store session data
}
}
}
if ($loginOk==true) {echo "<br />good login";}
else {header("Location: badlogindbase.html");}
}
else echo "Could not get the data from the database";
}
?>
logoutdbase.php
<?php
session_start();
unset($_SESSION['user']);
echo "You are now logged out <br />";
echo "<a href='loginFormDbase.php'>Click to login again</a>";
?>
session_start();
unset($_SESSION['user']);
echo "You are now logged out <br />";
echo "<a href='loginFormDbase.php'>Click to login again</a>";
?>
badlogindbase.html
<html>
<head>
<title>Bad Login</title>
</head>
<body>
The credentials you supplied are incorrect
<a href='loginFormDbase.php'>Click to try again</a>
</body>
</html>
