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.


Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members