Changing to PDO correctly

Trying to learn PDO and how to implement it correctly

[drymetal]

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.";
  redirect_to("login.php");
 } 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 {
getData($connection);
} 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.

Thanks!

[IamJibe]

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.

[php.net...]