没有mysql_real_escape_string和bindValue的PDO

Question

I'm relatively new to PDO and i have written the following block of code:

$id = $_GET['id'];

$db = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8', 'username', 'password');

foreach($db->query("SELECT id,name FROM names where id = '$id' ") as $row) {
    echo "<p>", ($row['name']), "<br>";
}

My uncertainties are:

1. is it safe to OMIT mysql_real_escape_string in the first line since i'm using PDO
2. is it safe to run the query as above without using bind values.

Thanks

Answer 1

No, this is not safe. PDO doesn't magically escape your queries for you. Your code, as shown, is wide open to SQL injection.

If you are using variables in your query, don't use ->query. Do not try to escape them yourself. You should be using prepared statements. That's the way to be safe.

$stmt = $db->prepare('SELECT id,name FROM names where id = ?');
if($stmt->execute(array($id))){
    while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
        echo "<p>", ($row['name']), "<br>";
    }
}

So, yes, you need to use bindParam, or execute, as shown.

P.S. mysql_real_escape_string is only for the (deprecated) mysql_ extension. It doesn't work with PDO.