Project mySQL PDO: Step by step guide to working with a mySQL database with the PDO to help eliminate SQL injection along with using encryption

Purpose
The purpose of this project is to demonstrate how to use the PDO with mySQL from PHP. I'll also show how you can encrypt and decrypt data with AES; if you need to generate some encryption/decryption keys, check out this simple password generator. Let's get started!

Insert Data with the PDO:

From the snippet below you can see that the PDO is quite similar to the fashion with which you create parameters in .NET and SQL. Here, the columns are specified in the prepare statement and the placeholders (like :columnA) is where the data is ultimately put which you are passing. With bindParam -> PDO::PARAM_INT (or PDO::PARAM_STR), those are optional to use in most cases.

$columnA = 1;
$columnB = "somestring";
$mySQLDatabaseuser = "database user name created in mySQL";
$mySQLDatabasePassword = "database user password created in mySQL";
$mySQLDatabaseName = "name of your database";
$connDB = new PDO("mysql:host=localhost;dbname=$mySQLDatabaseName", $mySQLDatabaseuser, $mySQLDatabasePassword);
$connDB->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
try {
// Query
$statement = $connDB->prepare("INSERT INTO tablename (columnA, columnB) values (:columnA, :columnB)");
// Assign and execute query
$statement->bindParam(':columnA', $columnA, PDO::PARAM_INT);
$statement->bindParam(':columnB', $columnB, PDO::PARAM_STR);
$statement->execute();
}
catch(PDOException $e) { print $e->getMessage(); }
//Close the connection
$connDB = null;

Insert Data with the PDO and AES Encryption:

Performing an insert with AES encryption is straight-forward as shown below. The column which will hold the encrypted data must be a blob (binary data). The encryption key resides only in your PHP code (it is not saved into the mySQL database) so if your mySQL database is compromised that does not necessarily mean the encrypted data in it is compromised because the key is not there.

$columnA = 1;
$columnB = "somestring";
$mySQLDatabaseuser = "database user name created in mySQL";
$mySQLDatabasePassword = "database user password created in mySQL";
$mySQLDatabaseName = "name of your database";
$encrypt_key = "4ldetn43t4aed0ho10smhd1l";
$connDB = new PDO("mysql:host=localhost;dbname=$mySQLDatabaseName", $mySQLDatabaseuser, $mySQLDatabasePassword);
$connDB->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
try {
// Query
$statement = $connDB->prepare("INSERT INTO tablename (columnA, columnB) values (:columnA, AES_ENCRYPT(:columnB, '$encrypt_key'))");
// Assign and execute query
$statement->bindParam(':columnA', $columnA, PDO::PARAM_INT);
$statement->bindParam(':columnB', $columnB, PDO::PARAM_STR);
$statement->execute();
}
catch(PDOException $e) { print $e->getMessage(); }
//Close the connection
$connDB = null;

Updating Data with the PDO:

An update is similar to an insert with the PDO. Check it out.

$rowNumber = 1;
$columnA = 1;
$columnB = "somestring";
$mySQLDatabaseuser = "database user name created in mySQL";
$mySQLDatabasePassword = "database user password created in mySQL";
$mySQLDatabaseName = "name of your database";
$connDB = new PDO("mysql:host=localhost;dbname=$mySQLDatabaseName", $mySQLDatabaseuser, $mySQLDatabasePassword);
$connDB->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
try {
// Query
$statement = $connDB->prepare("UPDATE tablename SET columnA = :columnA, columnB = :columnB WHERE rowNumber = :rowNumber");
// Assign and execute query
$statement->bindParam(':rowNumber', $rowNumber, PDO::PARAM_INT);
$statement->bindParam(':columnA', $columnA, PDO::PARAM_INT);
$statement->bindParam(':columnB', $columnB, PDO::PARAM_STR);
$statement->execute();
}
catch(PDOException $e) { print $e->getMessage(); }
//Close the connection
$connDB = null;

Updating Data with the PDO and AES Encryption:

And, an update with AES encryption is straight-forward as you can see.

$rowNumber = 1;
$columnA = 1;
$columnB = "somestring";
$mySQLDatabaseuser = "database user name created in mySQL";
$mySQLDatabasePassword = "database user password created in mySQL";
$mySQLDatabaseName = "name of your database";
$encrypt_key = "4ldetn43t4aed0ho10smhd1l";
$connDB = new PDO("mysql:host=localhost;dbname=$mySQLDatabaseName", $mySQLDatabaseuser, $mySQLDatabasePassword);
$connDB->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
try {
// Query
$statement = $connDB->prepare("UPDATE tablename SET columnA = :columnA, columnB = AES_ENCRYPT(:columnB, '$encrypt_key') WHERE rowNumber = :rowNumber");
// Assign and execute query
$statement->bindParam(':rowNumber', $rowNumber, PDO::PARAM_INT);
$statement->bindParam(':columnA', $columnA, PDO::PARAM_INT);
$statement->bindParam(':columnB', $columnB, PDO::PARAM_STR);
$statement->execute();
}
catch(PDOException $e) { print $e->getMessage(); }
//Close the connection
$connDB = null;

Selecting Data with the PDO:

Getting data from mySQL and the PDO is slightly different than what we've covered so far. To keep things similar with traditional mySQL, I've set the fetch mode to "PDO::FETCH_ASSOC".

$columnA = 1;
$mySQLDatabaseuser = "database user name created in mySQL";
$mySQLDatabasePassword = "database user password created in mySQL";
$mySQLDatabaseName = "name of your database";
$connDB = new PDO("mysql:host=localhost;dbname=$mySQLDatabaseName", $mySQLDatabaseuser, $mySQLDatabasePassword);
$connDB->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
try {
// Query
$statement = $connDB->prepare("SELECT columnA, columnB FROM tablename WHERE columnA = :columnA ORDER BY columnA ASC");
// Assign and execute query
$statement->bindParam(':columnA', $columnA, PDO::PARAM_INT);
$statement->setFetchMode(PDO::FETCH_ASSOC);
$statement->execute();
// Get data
while($row = $statement->fetch()) {
$columnA_value = $row['columnA'];
$columnB_value = $row['columnB'];
}
}
catch(PDOException $e) { print $e->getMessage(); }
//Close the connection
$connDB = null;

Selecting Data with the PDO and AES Decryption:

Decrypting the encrypted data in mySQL can be done simply by using the decrypt function. Is that easy or what?

$columnA = 1;
$mySQLDatabaseuser = "database user name created in mySQL";
$mySQLDatabasePassword = "database user password created in mySQL";
$mySQLDatabaseName = "name of your database";
$encrypt_key = "4ldetn43t4aed0ho10smhd1l";
$connDB = new PDO("mysql:host=localhost;dbname=$mySQLDatabaseName", $mySQLDatabaseuser, $mySQLDatabasePassword);
$connDB->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
try {
// Query
$statement = $connDB->prepare("SELECT columnA, AES_DECRYPT(columnB, '$encrypt_key') AS columnB FROM tablename WHERE columnA = :columnA ORDER BY columnA ASC");
// Assign and execute query
$statement->bindParam(':columnA', $columnA, PDO::PARAM_INT);
$statement->setFetchMode(PDO::FETCH_ASSOC);
$statement->execute();
// Get data
while($row = $statement->fetch()) {
$columnA_value = $row['columnA'];
$columnB_value = $row['columnB'];
}
}
catch(PDOException $e) { print $e->getMessage(); }
//Close the connection
$connDB = null;