ZF-278: DB Adapter / PDO: quoting PHP's null value, working with empty/null values in columns
Description
Summary tells it all.
echo $db->quoteInto('some_field is ?', null);
Produces: "some_field is '0'" which is invalid SQL.
I'm using the current function to quote values that can be null:
function sqlFor( $value )
{
$db = Zend::registry('db');
if ( $value === null ) {
return 'is NULL';
}
elseif ( is_array($value) ) {
$values = array();
foreach ( $value as $v ) {
$values[] = $db->quoteInto('?', $v);
}
return 'in (' . explode(', ', $values) . ')';
}
else {
return $db->quoteInto('= ?', $value);
}
}
Maybe something similar could be used to quote null values properly. I've only used this with MySQL and have no clue if it's portable to other DBs.
Comments
Posted by Gavin (gavin) on 2006-07-24T21:47:37.000+0000
Please suggest some practical use cases, including why quoteInto() is needed instead of quote().
For example, what if the SQL needed is "not null" instead of "is null"?
What if the developer's code needs "foo <> 3" or "foo is null", depending on some extraneous condition?
Posted by Sami Samhuri (sjs) on 2006-07-25T03:19:39.000+0000
You're absolutely right. I hadn't thought past my limited use of SQL here.
quoteInto() is not needed, but it is a useful convenience. If you put it that way quoteInto() is never really needed. The db I'm using has integer fields that default to null, which is why I need to check for null and return different SQL if it's null or not.
quote() turns null into '' (empty single quotes in a string). Fixing that is easier, it could check for null and return 'NULL' or the quoted value.
The only way I can think of to fix quoteInto() to do the right thing, would be to check for null values and then mangle the preceding operator, changing = to IS and <> or != to IS NOT.
For <, >, <=, >= I'm not sure what would be appropriate.
If you're interested in this behaviour at all I'll post a patch. If this is beyond the scope of what quoteInto() should do then I'll let it rest.
Posted by Gavin (gavin) on 2006-07-25T13:59:57.000+0000
I'll post a lengthy analysis of quoting issues throughout the Zend_Db* classes to the mail list soon. Your use case (working with nulls) had not been raised earlier, yet remains an important and common one that ZF needs to intuitively support. Your participation in helping arrive at a broad solution for quoting issues, not just quoteInto(), would be most welcome.
Posted by Bill Karwin (bkarwin) on 2006-11-13T15:14:32.000+0000
Changing fix version to 0.8.0.
Posted by Bill Karwin (bkarwin) on 2007-02-16T17:39:17.000+0000
Linking to ZF-900.
Posted by Bill Karwin (bkarwin) on 2007-04-04T14:45:11.000+0000
SQL parameters are intended to be used as placeholders for values. Values are expressions that can be used in equality/inequality comparisons. The NULL keyword in SQL does not fit this definition.
If you really need to parameterize the string 'NULL' and interpolate it into an expression, use the Zend_Db_Expr class: