Can this be SQL injected?

Question

I'm wondering if my code is actually protected from SQL injection. My sites have been injected before and I never really understood how to prevent it. Here is my code for inserting a comment:

if ($_POST['comment']) {
    $comment = strip_tags(nl2br(mysql_real_escape_string($_POST['comment'])));
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = "INSERT INTO posts (comment, authorid)
    VALUES ('$comment', '$uid')";
    // use exec() because no results are returned
    $conn->exec($sql);
    echo '<div style="width: 98%; max-width: 98%; border: 1px solid white; background-color: green; color: white; vertical-align: text-top; text-align: center;">Your comment was added to the wall!</div><br>';
}

Answer 1

Yes, it can probably be injected: you don't appear to be protecting your $uid variable. Also, stacking nl2br and strip_tags after escaping is a bad idea - you want to leave mysql_real_escape_string as the last operation to avoid any filter interaction effects.

More generally, you should use prepared statements, not string interpolation, to build SQL queries. It's simpler, more efficient, more secure and requires less code. You can create a prepared statement using $conn->prepare and execute it with arbitrary arguments:

$stmt = $conn->prepare("INSERT INTO posts (comment, authorid) VALUES (?, ?)");
$stmt->execute(array($comment, $uid));

No escaping required.