如何重写此动态SQL循环以包含PDO清理？

Question

I have this awesome loop from a question I asked yesterday. Previously, I could use mysql_real_escape_string($val) and that would handle protection against injection attacks and such. With PDO, however, there is not quite as simple of a function.

What can I do?

if (($_GET['mode'] == "update") and isset($_GET['id']) and isset($_POST['who'])) {

    $query = "update subcontractors set";
    $comma = " ";

    $whitelist = array("firstname","lastname","address","city","state","zip","phone1","phone2","phone3","email","dob","ssn","website","checks");

    foreach($_POST as $key => $val) {
        if ( !empty($val) && in_array($key, $whitelist)) {
            $query .= $comma . $key . "='" . $val . "'";
            $comma = ", ";
        }
    }
    $query .= " where id=" . $_POST['who'];

    include "connect.php";
    $db->query($query); 
} #endif UPDATE SECTION

Answer 1

I changed the code in several small ways:

• In the loop, it is now building a prepared statement instead of a full query. I replaced the $val variable that was being inserted into the sql statement with a "?" placeholder.

  $query .= $comma . $key . "= ?";

• In the loop, I place the $val into an array that will later be used to bind to the placeholders when the statement is executed.

  $params[] = $val;

• I call the prepare method of the PDO object and pass it the $query variable as an argument:

  $sth = $db->prepare($query);

• I call the execute method on the $sth (which is an object of the PDOStatement class) and pass it the $param array as an arguement. It will bind the array values to the placeholders in order:

  $sth->execute($params);

This will protect you from injection.

 if (($_GET['mode'] == "update") and isset($_GET['id']) and isset($_POST['who'])) {

        $query = "update subcontractors set";
        $comma = " ";
        $params = array();

        $whitelist = array("firstname","lastname","address","city","state","zip","phone1","phone2","phone3","email","dob","ssn","website","checks");

        foreach($_POST as $key => $val) {
            if ( !empty($val) && in_array($key, $whitelist)) {
                $query .= $comma . $key . "= ?";
                $params[] = $val;
                $comma = ", ";
            }
        }
        $query .= " where id=?";
        $params[] = $_POST['who'];
        include "connect.php";
        $sth = $db->prepare($query);
        $sth->execute($params); 
    } #endif UPDATE SECTION

For more info on prepared statements with PDO please read the following:

http://php.net/manual/en/pdo.prepare.php