Replacing mysql_* functions with PDO and prepared statements

Question

I've always done the simple connection of mysql_connect, mysql_pconnect:

$db = mysql_pconnect('*host*', '*user*', '*pass*');

if (!$db) {
    echo("<strong>Error:</strong> Could not connect to the database!");
    exit;
}

mysql_select_db('*database*');

While using this I've always used the simple method to escape any data before making a query, whether that be INSERT, SELECT, UPDATE or DELETE by using mysql_real_escape_string

$name = $_POST['name'];

$name = mysql_real_escape_string($name);

$sql = mysql_query("SELECT * FROM `users` WHERE (`name` = '$name')") or die(mysql_error());

Now I understand this is safe, to an extent!

It escapes dangerous characters; however, it is still vulnerable to other attacks which can contain safe characters but may be harmful to either displaying data or in some cases, modifying or deleting data maliciously.

So, I searched a little bit and found out about PDO, MySQLi and prepared statements. Yes, I may be late to the game but I've read many, many tutorials (tizag, W3C, blogs, Google searches) out there and not a single one has mentioned these. It seems very strange as to why, as just escaping user input really isn't secure and not good practice to say the least. Yes, I'm aware you could use Regex to tackle it, but still, I'm pretty sure that's not enough?

It is to my understanding that using PDO/prepared statements is a much safer way to store and retrieve data from a database when the variables are given by user input. The only trouble is, the switch over (especially after being very stuck in my ways/habits of previous coding) is a little difficult.

Right now I understand that to connect to my database using PDO I would use

$hostname = '*host*';
$username = '*user*';
$password = '*pass*';
$database = '*database*'

$dbh = new PDO("mysql:host=$hostname;dbname=$database", $username, $password);

if ($dbh) {
    echo 'Connected to database';
} else {
    echo 'Could not connect to database';
}

Now, function names are different so no longer will my mysql_query, mysql_fetch_array, mysql_num_rows etc work. So I'm having to read/remember a load of new ones, but this is where I'm getting confused.

If I wanted to insert data from say a sign up/registration form, how would I go about doing this, but mainly how would I go about it securely? I assume this is where prepared statements come in, but by using them does this eliminate the need to use something like mysql_real_escape_string? I know that mysql_real_escape_string requires you to be connected to a database via mysql_connect/mysql_pconnect so now we aren't using either won't this function just produce an error?

I've seen different ways to approach the PDO method too, for example, I've seen :variable and ? as what I think are known as place holders (sorry if that is wrong).

But I think this is roughly the idea of what should be done to fetch a user from a database

$user_id = $_GET['id']; // For example from a URL query string

$stmt = $dbh->prepare("SELECT * FROM `users` WHERE `id` = :user_id");

$stmt->bindParam(':user_id', $user_id, PDO::PARAM_INT);

But then I'm stuck on a couple things, if the variable wasn't a number and was a string of text, you have to given a length after PDO:PARAM_STR if I'm not mistaken. But how can you give a set length if you're not sure on the value given from user in-putted data, it can vary each time? Either way, as far as I know to display the data you then do

$stmt->execute();

$result = $stmt->fetchAll();

// Either

foreach($result as $row) {
    echo $row['user_id'].'<br />';
    echo $row['user_name'].'<br />';
    echo $row['user_email'];
}

// Or

foreach($result as $row) {
    $user_id = $row['user_id'];
    $user_name = $row['user_name'];
    $user_email = $row['user_email'];
}

echo("".$user_id."<br />".$user_name."<br />".$user_email."");

Now, is this all safe?

If I am right, would inserting data be the same for example:

 $username = $_POST['username'];
 $email = $_POST['email'];

 $stmt = $dbh->prepare("INSERT INTO `users` (username, email)
                        VALUES (:username, :email)");

 $stmt->bindParam(':username, $username, PDO::PARAM_STR, ?_LENGTH_?);
 $stmt->bindParam(':email, $email, PDO::PARAM_STR, ?_LENGTH_?);

$stmt->execute();

Would that work, and is that safe too? If it is right what value would I put in for the ?_LENGTH_?? Have I got this all completely wrong?

UPDATE

The replies I've had so far have been extremely helpful, can't thank you guys enough! Everyone has got a +1 for opening my eyes up to something a little different. It's difficult to choose the top answer, but I think Col. Shrapnel deserves it as everything is pretty much covered, even going into other arrays with custom libraries which I wasn't aware of!

But thanks to all of you:)

Answer 1

To answer the length question, specifying it is optional unless the param you are binding is an OUT parameter from a stored procedure, so in most cases you can safely omit it.

As far as safety goes, escaping is done behind the scenes when you bind the parameters. This is possible because you had to create a database connection when you created the object. You are also protected from SQL injection attacks since by preparing the statement, you are telling your database the format of the statement before user input can get anywhere near to it. An example:

$id = '1; MALICIOUS second STATEMENT';

mysql_query("SELECT * FROM `users` WHERE `id` = $id"); /* selects user with id 1 
                                                          and the executes the 
                                                          malicious second statement */

$stmt = $pdo->prepare("SELECT * FROM `users` WHERE `id` = ?") /* Tells DB to expect a 
                                                                 single statement with 
                                                                 a single parameter */
$stmt->execute(array($id)); /* selects user with id '1; MALICIOUS second 
                               STATEMENT' i.e. returns empty set. */

Thus, in terms of safety, your examples above seem fine.

Finally, I agree that binding parameters individually is tedious and is just as effectively done with an array passed to PDOStatement->execute() (see http://www.php.net/manual/en/pdostatement.execute.php).