PDO SQL injection (fetching variable from url)

Question

I have an id that is passed in the URL (e.g. id=33). It is always a number.

I used to use the standard mysql to connect and use escape string and int to make secure etc.

I'm trying to move onto PDO and I think the code below is secure (is working fine) but would really appreciate it is anyone on here could take a look and let me know if I've missed anything/any suggestions on improving the security.

$pdo    = new PDO($dsn, '*****','*****');

$id = $_GET["id"];
$id = filter_var($id, FILTER_SANITIZE_NUMBER_INT);  
if (filter_var($id, FILTER_VALIDATE_INT)) {  
$sql    = $pdo->prepare("SELECT * FROM table WHERE id = :id LIMIT 1");
$sql->bindParam(":id",$id);
$row    = $sql->execute();
$result = $sql->fetch(PDO::FETCH_ASSOC);
}

Am I correct in thinking the above is secure (using the below to echo the data):

<?php echo ($result['colour']);?>

Again, thanks in advance for any help :)

p.s. on a side note, I'm assuming if I used:

<?php echo ($result['id']);?>

this would pull the sanitized ID not the one from the get statement?

EDIT just to clarify, no users will have access to submit data (this will all be done by myself via phpmyadmin. The only thing they will see is a list of names and when clicked it takes them to a page (with the ID in the URL) showing the colours associated to there name.

Answer 1

If you're disciplined about using placeholders you shouldn't have any problems with your database security. PDO will take care of properly escaping things. Note this doesn't mean your query will be valid or that the data will be saved as you expect, you may have problems with truncation or conversion.

Keep in mind you'll need to be just as vigilant about presenting data stored in your database to avoid HTML issues and XSS problems. This requires escaping data for presentation, for example the htmlspecialchars method.