pdo database abstraction

Question

Hello,

Can someone help me to see what is going wrong with this setup

I build the @sql query in the function below like this. The extra quotes are setup in the conditions array.

     $sql .= " WHERE $field = \"$value\"";

The pdo update function loops the conditions array like this.

if (!is_null($conditions))
{
$cond = ' WHERE';
$obj = new CachingIterator(new ArrayIterator($conditions));
foreach($obj as $k=>$v)
{
    $cond .= " $k=$v";
    $cond .= $obj->hasNext() ? ' AND' : '';
}
}

My point to make is that I can not build arrays with values without adding slashes for quotation marks around the values. Otherwise the sql error that is being thrown is that it is an unknown column.

Is there something other that I can do?

Could someone give me some input on this please.

edit: the rest off the update function

Where could I bind the values of the conditions array and have them executed also? As I am seeing it now, only the values array is executed? Do I need to loop both arrays and then merge both arrays?

$obj = new CachingIterator(new ArrayIterator($values));

      $db = db::getInstance();
      $sql = "UPDATE $table SET \n";
      foreach( $obj as $field=>$val)
      {
       $sql .= "$field= :$field";
       $sql .= $obj->hasNext() ? ',' : '';
       $sql .= "\n";
      }

      $sql .= $cond ; 
      $stmt = $db->prepare($sql);

      // bind de params
      foreach($values as $k=>$v)
      {
       $stmt->bindParam(':'.$k, $v);
      }


      $stmt->execute($values );

thanks, Richard

Answer 1

If you're using PDO, why not use the bindParam() and bindValue() methods demonstated here?

Answer 2

Don't use addslashes(). It's an inadequate way to escape values, and has known security bugs.

Double-quotes in standard SQL are for delimited identifiers. Use single-quotes for string literals.

MySQL's default mode allows you to use single-quotes and double-quotes interchangeably, and back-quotes for delimited identifiers. But I recommend getting into the habit of using only single-quotes for strings, because it makes your SQL code more portable to other RDBMS vendors, and also more clear to anyone reading your code.

You should use query parameters, as @Mike B suggests. This is easy and it's far more secure than interpolating variables into SQL expressions.

---

You can use bindParam() or you can supply a $values associative array to the execute() function. Doing both is redundant.

Note that the array you give to the execute() method doesn't have to have the : character prepending the placeholder name:

$stmt = $pdo->prepare("SELECT * FROM MyTable WHERE myfield = :myfield");
// both of the following would work:
$stmt->execute( array(":myfield" => $value ) );
$stmt->execute( array("myfield" => $value ) );

Also to support parameters in both the SET clause and the WHERE clause, I'd suggest that you distinguish the fields when you specify the parameter placeholder names. That way if you reference the same field in both clauses (one to search for an old value, and the other to set a new value), you won't conflict.

Perhaps ":set$field" in the SET clause, and ":where$field" in the WHERE clause.

---

update: I have tested the following code. First, I use plain arrays, instead of the CachingIterator you used. I don't need to use the hasNext() method since I'm using join().

$settings = array("myfield" => "value");
$conditions = array("id" => 1);

$sql = "UPDATE $table SET \n";

Next is a demo of using array_map() and join() instead of loops. I'm using PHP 5.3.0 so I can use inline closure functions. If you use an earlier version of PHP, you'll have to declare the functions earlier and use them as callbacks.

$sql .= join(",",
    array_map(
        function($field) { return "$field = :set$field"; },
        array_keys($settings)
    )
);

if ($conditions)
{
    $sql .= " WHERE "
    . join(" AND ",
        array_map(
            function($field) { return "$field = :where$field"; },
            array_keys($conditions)
        )
    );
}

$stmt = $db->prepare($sql);

I couldn't get bindParam() to work, it always adds the value "1" instead of the actual values in my array. So here's code to prepare an associative array and pass it to execute():

$params = array();
foreach ($settings as $field=>$value) {
    $params[":set$field"] = $value;
}
foreach ($conditions as $field=>$value) {
    $params[":where$field"] = $value;
}

$stmt->execute($params);