Connecting MySQL to PHP

Introduction

This post will talk about how I connected a MySQL database to the PHP web application.

Creating a Schema

First, a schema needs to created. I did this using MySQL Workbench, however, you can execute your SQL create statements from any tool, including the command line.

The database is a simple schema, consisting of two tables.

user table. This will store the usernames and passwords. Fields are:

• id - generated automatically. The primary key.
• username - a string. Must be unique.
• password - a very long string, as I plan to store hashed values.

user_score table. This will store all scores of any user who's played the game. Fields are:

• id - generated automatically. The primary key.
• user_id - the user's id.
• score - a numerical value. The score attained.

A Note on Using MySQL under XAMPP in Mac OS X Lion

The version of XAMPP I'm using comes with certain security features, including not allowing remote connections. Graphical tools such as MySQL Workbench require this, so I needed to apply this fix.

SQL

The SQL code required to create the two tables is shown below:

CREATE TABLE `user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(45) NOT NULL,
  `password` varchar(500) NOT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `user_id` (`user_id`),
  UNIQUE KEY `user_id_2` (`user_id`),
  UNIQUE KEY `username_UNIQUE` (`username`),
  KEY `user_id_3` (`user_id`)
);

CREATE TABLE `user_score` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `score` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
);

Some sample data was added to both tables.

Connecting to the Database with PHP

The next step is to connect to the database using PHP code. This is easily achieved using two simple lines.

mysql_connect("localhost", "root", "");
mysql_select_db("CMT3313_coursework2");

Executing Queries

One line is required to execute a query:

$result = mysql_query($sql);

Retrieving Results

The result of the query is stored in $result. In order to view the results, one needs to loop through the array of rows. This is fairly easy to do:

if ($result) {
 while ($row = mysql_fetch_array($result)) {
  if ($row['username'] == $u) {
   $logged_in = true;
  }
 }
}

Security Concerns

When exposing the database your users, care must be taken to avoid exposing your machine, and to avoid exposing the database.

Escaping Code

To help prevent SQL injection attacks, any user-entered strings should be sanitised using the mysql_real_escape_string() method.

Hashing Passwords

Passwords should always be hashed, in case the database is compromised. There are several ways to do this. I opted to use the SHA256 method.

$password = mysql_real_escape_string($password);
$password = hash("sha256", $password);

Closing a Database Connection

To lighten the load on the server, MySQL Database connections should always be closed after use. This is simple to achieve using the command: mysql_close().

Putting it all Together

I decided to create one common PHP file that all other scripts in the web application can use (using either require or include).

<?php

function openConnection() {
 mysql_connect("localhost", "root", "");
 mysql_select_db("CMT3313_coursework2");
}

function closeConnection() {
 return mysql_close();
}

function executeQuery($sql) {
 $result = mysql_query($sql);
 return $result;
}

function hashPassword($password) {
 $password = mysql_real_escape_string($password);
 return hash("sha256", $password);
}

?>

Conclusion

This brief post highlighted how to properly connect to a MySQL database, and how I'm using it in my web application.