Re: [fw-db] zend_db_table_row and quoting

tony stamp wrote:
>
> Hello
>
> Just a quick question - when updating a row in a database, i usually do:
>
> $query = "update foo set bar = ?, baz = ?, goon = ?";
> $sth->execute($query, array('a', 'b', 'c');
>
> where i know that the db abstraction layer will apply quoting to make the
> values safe.
>

You have misunderstood. The code you show does not do quoting, it treats
the values as query parameters. Parameters are not interpolated into the
SQL string, so parameters don't require quoting.

It's a bit confusing, because methods like quoteInto() use the ? character
as an interpolation placeholder, while SQL also uses ? as a parameter
placeholder.

tony stamp wrote:
>
> ... will quoting also be applied to the row before saving, or is that the
> responsibility of the row to implement ie subclassing and performing
> validation on the properties before an update?
>

The $row->save() method uses $db->update() internally, so it gains the same
benefits of query parameters. It does use quoting for the WHERE clause of
the update, but it performs quoting for you in that case.

One exception: if you have set one of the row fields to a Zend_Db_Expr, you
are responsible for quoting. For example, suppose you want to update the
row but make sure a very long string is shortened instead of exceeding the
field length in the database, you can use a SQL expression like this:

$row->name = $name;
$row->surname = new Zend_Db_Expr($db->quoteInto('SUBSTRING(?, 0, 10)',
$surname));
$row->save();

You have to use Zend_Db_Expr because otherwise the entire expression
starting with 'SUBSTRING(...' will be treated as the new surname. But once
you use Zend_Db_Expr, the update will interpolate it into the SQL statement
instead of using a query parameter. Thus you are responsible for quoting
any strings you interpolate into the expression.

The result will be SQL:

UPDATE tablename
SET name = ?, -- note query parameter placeholder
surname = SUBSTRING('O\'Reilly', 0, 10) -- note quoting and escaping has
been done
WHERE primarykey = 1234;

Then it executes the query, passing $name as the sole query parameter to
execute().

Regards,
Bill Karwin
--
View this message in context: http://www.nabble.com/zend_db_table_row-and-quoting-tp19177390p19180753.html
Sent from the Zend DB mailing list archive at Nabble.com.