[owncloud-devel] DBAL SQL Query Builder - Ensure to parameterize your data

Lukas Reschke lukas at statuscode.ch
Tue Mar 31 14:33:54 GMT 2015


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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mailman.owncloud.org/pipermail/devel/attachments/20150331/0c599abb/attachment.html>


More information about the Devel mailing list