Hi all,
In core we want to promote the usage of the DBAL SQL Query Builder in core
instead of handwritten SQL Queries. The advantage of this is to have a more
standardized syntax for queries as well as being able to unit-test these
easily.
However, I have seen some dangerous misuse of the Query Builder in recent Pull
Requests and thus want to make you all aware of the fact that all passed
parameters should to get parameterized.
So, one of the previous ways to generate queries in core was like the following:
$query = OC_DB::prepare('SELECT * FROM `*PREFIX*users` WHERE `uid` =
?');
$query->bindValue(1, $user');
$result = $query->execute();
With the Query Builder this can be changed to something like the following:
/** @var \Doctrine\DBAL\Connection $dbConnection */
$dbConnection = \OC::$server->getDatabaseConnection();
$qb = $dbConnection->createQueryBuilder();
$qb->select('*')
->from('`*PREFIX*users`')
->where('`uid` = “$user"');
$result = $qb->execute()->fetchAll();
However, this is **NOT** the right way to use the query builder, let’s just
assume $user would be a value controlled by an user. In this case this would
result in a SQL injection. Please always parameterize anything user controlled,
this can be achieved using the setParameter function:
/** @var \Doctrine\DBAL\Connection $dbConnection */
$dbConnection = \OC::$server->getDatabaseConnection();
$qb = $dbConnection->createQueryBuilder();
$qb->select('*')
->from('`*PREFIX*users`')
->where('`uid` = :user');
$qb->setParameter(':user', $user);
$result = $qb->execute()->fetchAll();
As you can see this is still quite similar to what was done before. Always
remember: The Query Builder is not some magic that prevents all security
pitfalls, it will just give you an easier and more testable way to write SQL
queries.
That said, at the moment we are not yet exposing the Query Builder to app
developers in the public interface. But that is something that might change
once we’re happy with it in core.
As always: If you have any questions or need help just drop by in #owncloud-dev
on IRC or send a mail to our friendly devel mailinglist.
Thanks!
Lukas_______________________________________________
Devel mailing list
[email protected]
http://mailman.owncloud.org/mailman/listinfo/devel
