Welcome to WebmasterWorld Guest from

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Changing to PDO correctly

Trying to learn PDO and how to implement it correctly

8:13 am on May 15, 2014 (gmt 0)

New User

joined:May 15, 2014
posts: 1
votes: 0

I paid for a security audit recently, and one of the comments was I needed to learn Parameterized Queries and that PDO was a way to accomplish this.

So, I have two questions. The first is a comparison of my old code and new code to make sure I'm doing this correctly. And the second is on how I finish the statement I am working on.

My localhost.php file had this:

define("DB_SERVER", "localhost");
define("DB_USER", "user");
define("DB_PASS", "pass");
define("DB_NAME", "dbname");

// Create a connection
$connection = mysqli_connect(DB_SERVER, DB_USER, DB_PASS, DB_NAME);
// Test if succeeded
if(mysqli_connect_errno()) {
die("Database connection failed: " .
mysqli_connect_error() .
" (" . mysqli_connect_errno() . ")"

I changed that to this:

$connection = new PDO('mysql:host=localhost;dbname=dbname;charset=utf8', 'user', 'pass');
$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$connection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

One problem here, I'm not sure how to check for errors on this statement like I did before using mysqli statements. Any suggestions?

On my registration page, let's just pretend I have one field for people to fill in. The code looks like this (stripping out all other stuff to keep this post short):

$username = mysql_prep($_POST["username"]);
$parameters = array('username')
$query = "INSERT INTO users (";
$query .= " username";
$query .= ") VALUES (";
$query .= " '{$username}'";
$query .= ")";

Directly after the code above, it goes on to check for errors, and set session messages, etc:

$result = mysqli_query($connection, $query);
if ($result) {
// Success
$_SESSION["message"] = "You've successfully registered.";
} else {
// Failure
$_SESSION["message"] = "Registration failed.";

} else {
// This is probably a GET request
}// end: if (isset($_POST['submit']))
} else {
$_SESSION["message"] = "";

So, what I've done here is changed it to PDO:

$username = mysql_prep($_POST["username"]);
$stmt = $connection->prepare("INSERT INTO users(username) VALUES (:username)");
$stmt->execute(array(':username' => $username));

So, notice that I've kept the cleaning using mysql_prep. I don't see any reason to change that for all the form fields?

Also, on that other second part, where I'm checking the $result and setting the message,etc. How do I carry that over to PDO?

I think I can use something like this:

try {
} catch(PDOException: $ex) {

But, I don't know if that applies or how to utilize that in this case. Any suggestions on all of this? I want my code to be absolutely secure - but this is some deep stuff to wrap my head around and these are the hiccups I've been having.

10:54 pm on June 29, 2014 (gmt 0)

New User

joined:June 29, 2014
posts: 1
votes: 0

I'd ask for a refund for that security audit...

mysqli, the mysql extension you are using support parametrized queries out of the box. In fact mysqli spoort more MySQL advanced functionnalities than PDO.