Lesson 6.4. Connect to MySQL in PHP

PDO or MySQLi

There is two main API for creating SQL queries from MySQL:

MySQLi : My SQL improved
PDO : PHP Data Objects

The choice of an API is questionable. In the following, we will use PDO for two reasons:

PDO is compatible with many databases, while MySQLi is dedicated to MySQL.
PDO allows the writing of parameterized requests. These queries separate the SQL commands fror parameters, which reduces the risk of SQL injection.

SQL injection

SQL injection is a hacking technique that exploits a flaw website security. This technique consists in injecting a malicious SQL query into a HTML form.

Assume a connection request that selects a user based his username and password:

SELECT uid FROM Users WHERE username = '(nom)' AND password = '(mot de passe)';

A login form allows a user to enter its username and password. Let's now assume that a malicious user enters Dupont'; -- as username.

The previous SQL query will become:

SELECT uid FROM Users WHERE name = 'Dupont'; -- AND password = '(mot de passe)';

In SQL, -- marks the beginning of a comment. Here, the password will not be checked: there is a risk that a hacker impersonates another user.

There are several techniques to prevent this risk. For example you may systematically escape the data entered by the user and / or use an API with parameterized queries that will separate the SQL commands from parameters. In the following, we'll study the second option.

This example is adapted from the Wikipedia page on SQL injection.

Connection

The following code allows connection to the MySQL database. Connection must be performed once and only once before any other MySQL query:

<?php
// Change for your connection data
$servername = "localhost";
$username = "username";
$password = "password";

try 
{
    $db = new PDO("mysql:host=$servername;dbname=myDB", $username, $password);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connection successfull";
}
catch(PDOException $e)
{
    echo "Connection failed: " . $e->getMessage();
    die();
}
?>

You will notice that the above script contains the server address, the username and the database password. This information should never be public. You will want to make sure that no user can access it.

You will also notice the use of the PHP error handling mechanism try ... catch, which allows you to continue executing the script in case of error.

You will finally notice that the $db variable is an instance of the PDO class. If your site must access multiple databases, you must create as many instances as databases. This variable should be kept as long as you need to communicate with the database because:

its attributes contain the connection configuration;
its methods allow dialogue with the database.

In the rest of the course, we will assume the existence of the $db variable.

Exercice

Using phpMyAdmin, create a database.
Using phpMyAdmin, create a new MySQL user with all rights.
Use the above PHP code to connect to your new database.

Lesson 6.3 Summary Lesson 6.5

Lesson 6.4. Connect to MySQL in PHP

PDO or MySQLi

SQL injection

Connection

Exercice

See also