Prepared statements in PDO; benefit over other forms of SQL insertion?

Question

I learned in school to do something like the below to insert data into SQL via $_POSTed form data.

$title = mysql_escape_string($_POST["newstitle"]);
$body = mysql_escape_string($_POST["newsbody"]);
$addnews = $db->query("
    INSERT INTO news
        VALUES (CURRENT_DATE, '$body', '$title', '')
");

However, it was fairly recently I was told I should be using the below instead:

$addnews = $db->prepare("
    INSERT INTO news
        VALUES (CURRENT_DATE, :body, :title, '')
");
$addnews->execute(array(
    ':body' => $_POST["newsbody"],
    ':title' => $_POST["newstitle"]));

What benefit does the second snippet of code offer? My professor in the aforementioned course was very traditional and I imagine was teaching an archaic way of doing things. He did use a lot of PDO, but never for the above example. And yes, I know mysql_escape_string() is deprecated, but that is how I was taught. I'm trying to make an effort to change my method to be more appropriate for current trends.

Prepared statements. Other one is vulnerable to sql injection. — Davit, Sep 01 '13 at 00:33
Refer to http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — , Sep 01 '13 at 00:43
What you ought to have been using before was `mysql_REAL_escape_string()`. Prepared statements (with bound parameters!) are just more convenient. The added security is just a by-product over the easier-to-forget escaping (or using the wrong function). — mario, Sep 01 '13 at 00:59
possible duplicate of [What's the advantage and use of prepared statements in PHP](http://stackoverflow.com/questions/7568728/whats-the-advantage-and-use-of-prepared-statements-in-php) — mario, Sep 01 '13 at 01:01
@mario : Thankyou! The first level headed answer to this question i've heard in a while. — Lee, Sep 01 '13 at 01:01
@mario, thank you, but that other question doesn't particularly answer my own. It can be more efficient for multiple insertions, but in the case of only having one, is there really a point if not just to keep things consistent? @Dachi, even if I use `mysql_real_escape_string()`? — gator, Sep 01 '13 at 01:24
Well, `mysql_*` has been deprecated like you know, and `mysqli`, its replacement, has prepared statments. So there's no reason not to use them. — elclanrs, Sep 01 '13 at 01:35
@riista - I use prepared statements even for a single statement. I think it makes my code more consistent and therefore easier to maintain. I know there's a loss of efficiency, but I think it's worth it. — andrewsi, Sep 01 '13 at 16:10

score 0 · Accepted Answer · answered Sep 01 '13 at 04:47

Your question can be answered easily.

I hope you understand that whatever value have to be properly formatted to be put into SQL query. So prepared statement does. Unlike whatever *_escape_string, which does only partial formatting, prepared statement intended to do the full one. And right where it have to be done - not sooner, not later - so it makes it impossible to forget. That's the point.

You only need to understand the difference between formatting and escaping, which no professor ever understands.

Prepared statements in PDO; benefit over other forms of SQL insertion?

1 Answers1