CODEWITHSUNDEEP
phpsqlpdosql-update
AndrewB
AndrewB

Reputation: 836

Using PDO to UPDATE a value based on its old value within the database

So say I have a query like this:

$columnstr = "sum";
$valsstr = "sum + 5";

$query = "UPDATE mytable SET $columnstr = ?";
$updatenum = $db->prepare($query);
$updatenum->bindParam(1, $valsstr);
$updatenum->execute();

The problem is I don't think this will work and I can't exactly test it for various reasons. Sum is an integer column but I'm binding a string to it, hoping it will know to add 5 to "sum".

Also it needs to be sent in like this as it won't always be based off a columns current value.

The query I want to have sent in this case is: 

"UPDATE mytable SET sum=sum+5"

Any input would be appreciated, thanks!

Upvotes: 1

Views: 1992

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562348

You can't use query parameters for expressions. A parameter takes the place only of a single scalar value. So it's not that PDO can't bind string variables, it's that your string "sum+5" will be evaluated as an integer, not as an SQL expression. MySQL's idea of the integer value of "sum+5" is 0.

This is not a limitation of PDO, it's how SQL is defined. It would work the same way regardless of client programming language or brand of RDBMS. This is common hurdle for developers learning SQL. Parameters are not string interpolation. String interpolation happens before the SQL is parsed during prepare(). Parameters are sent after the SQL is parsed, during execute(). The parameter value cannot force the SQL to be re-parsed, and therefore it can't introduce extra expression syntax to the query. This is how it protects against SQL injection.

If you have input strings that might be expressions sometimes and other times are constant values, you have a complex case. The best you can do is to write code to test your input string and do either string interpolation or parameterization.

You can pass an object to a database query method instead of a string (PHP being a loosely typed language makes this easy). If the value is an object, then interpolate it into the query string. If the method argument is a scalar, then use a parameter.

function my_update_method($args) {
  $params = array();
  $query = "UPDATE mytable SET ";
  foreach ($args as $column => $value) {
    if ($value instanceof SQLTerm)
    {
      $query .= "`" . $column . "` = " . $value; /* calls object's __toString() method */
    } else {
      $query .= "`" . $column . "` = ?";
      $params[] = $value;
    }
  }

  $updatenum = $db->prepare($query);
  $updatenum->execute($params);
}

The SQLTerm class is just a wrapper for a string, its only purpose is to be an object instead of a string, so we can tell when a value is a string scalar to be parameterized, versus an expression that can't be parameterized.

class SQLTerm
{
  protected $string;
  public function __construct($string) {
    $this->string = $string;
  }
  public function __toString() {
    return $this->string;
  }
}

This means the caller is responsible for creating a SQLTerm objects in a way that avoids SQL injection risks.

Upvotes: 1

Joel Penner
Joel Penner

Reputation: 13

It should work. To the best of my knowledge, only columns where you directly specify the value will be able to be parameterized.

The following is valid SQL.

UPDATE mytable SET col1=col1+5, col2=?, col3=? WHERE mykey=?

I just tested it out in IBM DB2 9.7, but it's been valid syntax for years.

Upvotes: 1

Related Questions