The safety of direct SQL-requests in Joomla templates/views

Question

I need to change the ordering of which the products are displayed on the Virtuemart category page. The files models/product.php and tables/products.php are too complicated for me to put the changes in them. So I decided to use direct SQL-requests in views/category/default.php (if more precisely in templates/my_template/html/com_virtuemart/category/default.php) like:

$db = JFactory::getDBO();
$query = $db->getQuery(true);
$query->select('a.virtuemart_product_id');
$query->from('#__virtuemart_product_categories AS a');
$query->where($db->quoteName('virtuemart_category_id')." = ".$db->quote($this->category->virtuemart_category_id));
$query->select('b.product_in_stock, c.product_price');
$query->join('LEFT', '#__virtuemart_products AS b ON b.virtuemart_product_id = a.virtuemart_product_id');
$query->join('LEFT', '#__virtuemart_product_prices AS c ON c.virtuemart_product_id = a.virtuemart_product_id');
$query->order('b.product_in_stock DESC, c.product_price DESC');
$db->setQuery($query);
$sortedProducts = $db->loadObjectList();
...

It's not the only case when I needed to use direct SQL-requests in some view(s) or in template(s).

But to what extent is such direct way safe and how to protect it to the maximum extent possible?

Answer 1

Joomla is built on the MVC design pattern, so querying the db from a view is bad practice. But with your scenario, let's consider a few alteratives:

• changing the model: this makes no sense: you can't change a model you don't own, or you'll be tied to applying your changes with every new release; additionally, you mention it's overly complicated;
• query from the template: your current solution, not scalable, doesn't leverage Joomla cache properly.
• create a model: (or a helper), put it inside the appropriate VM folder, and invoke it from your view: definitely the most attractive solution: no overhead, simpler code, additionally you leverage Joomla features including: cache and proper design pattern use with separation of concerns, making maintenance easier for your future self.

To consider your current solution:

• You are not introducing any vulnerability: your code properly escapes the query parameters so no unexpected results can come from there;
• You are not following best practices; should your queries become more complex, your code will be harder to maintain; other developers may find it slightly harder to find your work;
• The code cannot use the Joomla cache properly (only page cache will work); with a small shop, no problems; but with a larger number of products performances will be affected

Conclusions

If your work is a proof of concept, go with your current solution; if this is a live website, invest a bit of your time to create a helper or model where you'll code your queries. You'll gain flexibility, re-usability (if two views require the same data, you don't need to duplicate code), and save some time in the future.