使用命名占位符时PHP / SQL插入错误

Question

I have the following PHP PDO statement:

$STH = $this->_db->prepare("INSERT INTO UserDetails (FirstName, LastName, 
            Address, City, County, PostCode, Phone, Mobile, Sex, DOB, 
            FundraisingAim, WeeksAim, LengthsAim, HearAboutID,
            MotivationID, WelcomePackID, ContactPrefID, TitleID) 
            VALUES
            (:firstName, :lastName, :address, :city, :county, :postCode, 
            :phone, :mobile, :sex, :DOB, :fundraisingAim, :weeksAim,
            :lengthsAim, :hearAbout, :motivation,
            :welcomePackPref, :contactPref, :title)");

$STH->execute($userData);

Where $userData is an associative array. I've double checked the names and I don't understand why I'm getting the following error:

SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

What silly mistake have I made?

Answer 1

Your $userData must have exactly the same placeholders bound by your statement, no more and no fewer. See PDOStatement::execute documentation, the part that says "You cannot bind more values than specified".

You need to prepare your argument to execute() to match your binds exactly. This is easy with array_intersect_key() if you arrange your arrays correctly. I usually wrap this in a function which will also take care of prefixing, like below:

// Adds a prefix to a name for a named bind placeholder
function prefix($name) {
    return ':'.$name;
}

// like 'prefix()', but for array keys
function prefix_keys($assoc) {
    // prefix STRING keys
    // Numeric keys not included
    $newassoc = array();
    foreach ($assoc as $k=>$v) {
        if (is_string($k)) {
            $newassoc[prefix($k)] = $v;
        }
    }
    return $newassoc;
}

// given a map of datakeyname=>columnname, and a table name, returns an
// sql insert string with named bind placeholder parameters.
function makeInsertStmt($tablename, $namemap) {
    $binds = array_map('prefix', array_keys($namemap));
    return 'INSERT INTO '.$tablename.' ('.implode(',',$namemap).') VALUES ('
    .implode(',',$binds).')';
}

// returns an array formatted for an `execute()`
function makeBindData($data, $namemap) {
    // $data assoc array, $namemap name->column mapping
    return prefix_keys(array_intersect_key($data, $namemap));
}

// example to demonstrate how these pieces fit together
function RunTestInsert(PDO $pdo, $userData) {
    $tablename = 'UserDetails';
    // map "key in $userData" => "column name"
    // do not include ':' prefix in $userData
    $namemap = array(
      'firstName'       => "FirstName",
      'lastName'        => "LastName",
      'address'         => "Address",
      'city'            => "City",
      'county'          => "County",
      'postCode'        => "PostCode",
      'phone'           => "Phone",
      'mobile'          => "Mobile",
      'sex'             => "Sex",
      'DOB'             => "DOB",
      'fundraisingAim'  => "FundraisingAim",
      'weeksAim'        => "WeeksAim",
      'lengthsAim'      => "LengthsAim",
      'hearAbout'       => "HearAboutID",
      'motivation'      => "MotivationID",
      'welcomePackPref' => "WelcomePackID",
      'contactPref'     => "ContactPrefID",
      'title'           => "TitleID",
    );
    $sql = makeInsertStmt($tablename, $namemap);
    $binddata = makeBindData($userData, $namemap);

    $pstmt = $pdo->prepare($sql);
    $pstmt->execute($binddata);
}

The benefit of an abstraction like this is you don't need to worry about the bind parameters themselves.