[support] db_select()->condition()

[Metzler, David]

It may generate a database compatibility error, but it is not a SQL
injection attack vector.  If you cannot trust the data passed to the
condition in the second parameter, then the function should never be
used. 

In truth data passed to condition is appropriately escaped, and should
not represent a SQL injection vector.  If I'm wrong about that, then we
need to file a security issue with DBTNG and get the condition method
fixed. Either you can trust the condition method to properly escape that
variable or you can't.   My bet is that it won't be different using LIKE
vs '=' and that using condition is safe if not optimal for The like
clause.   That doesn't mean it would function as well, but it is
ancillary to my point about how the condition method works.   You'd
still need to do the string concatenation of the wild card variables for
the like method prior to passing it. 

On an off note using the Like method currently can result compatibility
problems with postgres, as it converts it to use ILIKE which will fail
against certain data types.  I recently had to temporarily hack the
database.inc to get a core upgrade script to complete properly with
postgres... sigh.  




-----Original Message-----
From: support-bounces at drupal.org [mailto:support-bounces at drupal.org] On
Behalf Of Larry Garfield
Sent: Saturday, May 26, 2012 10:41 AM
To: support at drupal.org
Subject: Re: [support] db_select()->condition()

PHP's documentation is sometimes a little quirky when it comes to 
arrays, because there are arrays, then there are iteratable things (some

objects plus arrays), []-accessible things (some other objects plus 
arrays), etc.  It's kinda screwy. :-)

For the record, yes, both db_query() and db_select()->execute() give you

back an identical statement object.  db_select() is more flexible, but 
adds a dozen or two function calls to the process before you get to the 
actual query execution. Don't do that unless you need to.

Also, the previous code had a security hole.

db_select('mytable', 'mt')
  ->fields('mt', array('myvar'))
  ->condition('mystring', '%' . $somrvariable . '%', LIKE)
  ->execute();


You actually want db_like($somevariable), which handles DB-specific 
escaping in LIKE strings.  Forgetting to do so is almost the only 
possible SQL injection attack vector left in Drupal unless you bypass 
DBTNG entirely. :-)

--Larry Garfield, primary author, DBTNG


On 05/26/2012 09:47 AM, Earnie Boyd wrote:
> Please accept my apology.  I did try db_select()->execute()->fetch()
> and was returned an "Undefined method" error but now I try it and it
> worked.  Thanks for the explanations and push Michael.
>
> Also the php.net/foreach syntax diagram suggest array_expression as
> required which is why I was also thinking it must be an array.  But
> the sentence before the diagram states "array or object".  The do give
> a link to php.net/manual/en/language.oop5.iterations.php which gives
> an example of object iteration.
>
> Earnie
>
> On Fri, May 25, 2012 at 11:47 PM, Michael Prasuhn<mike at mikeyp.net>
wrote:
>> On May 25, 2012, at 2:16 PM, Earnie Boyd wrote:
>>
>>> Uh, no it does not return the same thing or I would be able to do
>>> db_select()->execute()->fetch().
>>>
>>> You must use foreach(db_select()->execute() as $row) to get the data
>>> and the data type of the first parameter of foreach is an array.
>> Nope, db_query() and SelectQuery::execute() *DO* return the same
thing, and it's NOT an array. Just because you can use foreach does not
make it an array.
>>
>> Both db_query() and SelectQuery::execute() return the exact same
thing: an object of class DatabaseStatmentBase (may be different
depending on the database being used) that implements
DatabaseStatementInterface. These classes both implement the iterator
interface which allows them to be, well, iterated upon as if they were
arrays.
>>
>> If you read the code at
http://api.drupal.org/api/drupal/includes%21database%21database.inc/func
tion/db_query/7 and
http://api.drupal.org/api/drupal/includes%21database%21select.inc/functi
on/SelectQuery%3A%3Aexecute/7 you can clearly see that they are both
calling the same function as their return value.
>>
>> Before you say that the code you suggested doesn't work please give
it a try and actually run this:
>>
>> <?php
>> $result = db_query("SELECT * FROM {node}");
>> print get_class($result) . "\n";
>>
>> $result_2 = db_select('node', 'n')->fields('n')->execute();
>> print get_class($result_2);
>>
>> Or this call to fetch() from the result of db_query() which you claim
doesn't work:
>>
>> <?php
>> $result = db_query("SELECT * FROM {node}");
>> var_dump($result->fetch());
>>
>> $result_2 = db_select('node', 'n')->fields('n')->execute();
>> var_dump($result_2->fetch());
>>
>> You'll notice that in the second example the results are identical.
>>
>> You can read more about the Iterator interface at:
http://www.php.net/manual/en/class.iterator.php.
>>
>> -Mike
>>
>> __________________
>> Michael Prasuhn
>> http://mikeyp.net
>>
>>
>> --
>> [ Drupal support list | http://lists.drupal.org/ ]
>
>

-- 
[ Drupal support list | http://lists.drupal.org/ ]