CODEWITHSUNDEEP
phpmysqlpdo
Tyler Collins
Tyler Collins

Reputation: 127

How can I rewrite this dynamic SQL loop to include PDO sanitization?

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

Upvotes: 0

Views: 115

Answers (3)

user2560539
user2560539

Reputation:

Since there are too many parameters and too many forms and databases then it might be best to create some stored procedures and call each one depending the case/form being submitted. As for the above example i would go for a not dynamic solution in this case. Only because a field in your whitelist array could be a MySQL reserved keyword something that it would cause an error when the MySQL server executes your query.

  if (($_GET['mode'] == "update") and isset($_GET['id']) and isset($_POST['who'])) {
    include "connect.php";
    $stmt = dbh->prepare("UPDATE `subcontractors` SET `firstname`=:firstname,`lastname`=:lastname,`address`=:address,`city`=:city,`state`=:state,`zip`=:zip,`phone1`=:phone1,`phone2`=:phone2,`phone3`=:phone3,`email`=:email,`dob`=:dob,`ssn`=:ssn,`website`=:website,`checks`=:checks WHERE `id`=:id");
    $stmt->bindParam(':firstname', $_POST['firstname']);
    $stmt->bindParam(':lastname', $_POST['lastname']);
    $stmt->bindParam(':address', $_POST['address']);
    $stmt->bindParam(':city', $_POST['city']);
    $stmt->bindParam(':state', $_POST['state']);
    $stmt->bindParam(':zip', $_POST['zip']);
    $stmt->bindParam(':phone1', $_POST['phone1']);
    $stmt->bindParam(':phone2', $_POST['phone2']);
    $stmt->bindParam(':phone3', $_POST['phone3']);
    $stmt->bindParam(':email', $_POST['email']);
    $stmt->bindParam(':dob', $_POST['dob']);
    $stmt->bindParam(':ssn', $_POST['ssn']);
    $stmt->bindParam(':website', $_POST['website']);
    $stmt->bindParam(':checks', $_POST['checks']);
    $stmt->bindParam(':id', $_POST['who']);
    $stmt->execute();
    } #endif UPDATE SECTION

Upvotes: 1

dlporter98
dlporter98

Reputation: 1630

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

Upvotes: 2

gommb
gommb

Reputation: 1117

$query .= $comma . $key . "=" . $db->quote($val); this escapes the string and the single quotes are added automatically

Upvotes: 0

Related Questions