CODEWITHSUNDEEP
phpmysqlsecuritypdoparameter-passing
Martin AJ
Martin AJ

Reputation: 6697

When should I use prepared statements exactly?

I want to know, can I just use prepared statements one time?

Here is my script:

$stm = $db->prepare("UPDATE
                     qanda AS ans1
                     JOIN qanda AS ans2 ON ans2.related = ans1.related
                     JOIN qanda AS ques ON ans2.related = ques.id
                     SET ans1.acceptedanswer = 1,
                         ans1.aadate = IF( ans1.id <> ?, ans1.aadate, ?)
                     WHERE ques.author_id = ? AND ans2.author_id = ? 
                                        ");
$stm->execute(array($answer_id, time(), $_SESSION["Id"], $author_id));
$done = $stm->rowCount();

if ($done){
    /* I don't use prepared statement here */
    $stm1 = $db->prepare("UPDATE user SET rep = rep + 15 WHERE id = $author_id");
    $stm1->execute();
}

As you see I didn't use prepared statement for second query. Because I did it for first query and if first query is working then I'm sure arguments are valid and don't need to bind them by prepared statement.

Please don't ask me why you don't want to use prepared statemen't for second query, because the reason is too long.

So what I'm doing is correct? Isn't there any security problem?

Upvotes: 0

Views: 49

Answers (1)

Igor S Om
Igor S Om

Reputation: 745

The straight answer is: yes, you can.

The reason why is actually up to you, since it's anyway good practice to use prepared statement whenever you pass values.

Also consider that, if you are not binding any parameter, it makes more sense to use the query() method, just to be explicit on the fact that you are not going to bind anything. So your second query would be

$stm1 = $db->query("UPDATE user SET rep = rep + 15 WHERE id = $author_id");

(see http://php.net/manual/en/pdo.query.php)

instead of

$stm1 = $db->prepare("UPDATE user SET rep = rep + 15 WHERE id = $author_id");
$stm1->execute();

Moreover you mentioned a dynamic query, but this is not the case of your sample code. Anyway I will give you an example of how two use prepared statement also on queries dynamically generated.

It's a silly example, but should be enough to give you an idea.

Assume we have some values to update 'email', 'date_of_birth' and 'website'. Let's say we want to do some check on this data before inserting them. I'll pretend we have a valid() function already in place.

    $dynamic_sql = array();
    $parameters[':date_of_bird'] = $date_of_birth;

    if(valid($email)) {
        $dynamic_sql['email_sql'] = "email = :email";
        $parameters[':email'] = $email;

    }
    if(valid($website)) {
        $dynamic_sql['website_sql'] = "website = :website";
        $parameters[':website'] = $website;
    }

    if(count($dynamic_sql)>0) {
        $dynamic_sql = ','.implode($dynamic_sql);
    }

    $query = "UPDATE user
    SET date_of_birth = :date_of_birth $dynamic_sql
    WHERE
    user_id = :user_id";

    $stm = $db->prepare($query);
    $stm->execute($parameters);

This kind of approach will allow you to keep using prepared statement also with dynamically generated SQL.

Upvotes: 3

Related Questions