How to use prepare statements / bind values in a query in Joomla 3?

3
votes

I'd like to know how to bind values in where clause. I have understood that is something that MUST be done for security reasons.

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query
    ->select("*")
    ->from($db->quoteName("food"))
    ->where("taste = :taste")
    ->bind(':taste', 'sweet');
$db->setQuery($query);
$rows = $db->loadAssocList();

I'm getting this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':taste' at line 3 SQL=SELECT * FROM food WHERE taste = :taste

My code is based on this post. It said that in Joomla 3.1 only "PDO/Sqlite and PDO/Oracle are supporting prepared statements", I am using Joomla 3.2.1 and MySQL, and in my Joomla configuration MySQLi. Could be that the problem?

I am quite confused because I dont know what API / Class have to follow.

Even I'm starting to doubt if I have to use JFactory::getDbo() to Select/Insert/Update/Delete data in Joomla DB.

Thanks in advance.

3
phpjoomlamysqliprepared-statementjoomla3.0
I have understood that is something that MUST be done for security reasons. - Well, if JOOMLA would offer bind values, then you would perhaps benefit security wise. However if not, then you just can't do it. Security wise you need to do then "that all other" as a MUST to be done security wise what needs to be done security wise. This is why it is important to understand why this or that is a MUST security wise, which often needs understanding how things work and what things do which well, often is necessary when talking about security... . – hakre
I Was referring not only to bind values but also to avoid SQL Injection in general :-) – Mikel
Well, you are pretty much after bind in the wording of your question :) But I'm not so well with Joomla, maybe you can access the underlying adapter via their DB API. – hakre

3 Answers

6
votes

As far as I know, you can't use prepared statements nor bind values with Joomla.

If you read the Secure Coding Guideliness from the Joomla documentation (http://docs.joomla.org/Secure_coding_guidelines#Constructing_SQL_queries), they don't talk about prepared statements, only about using casting or quoting to avoid SQL injection.

1
votes

In Joomla there is normally the check(), bind(), store() triple from JTable that prevents injection.

JDatabaseQueryPreparable has a bind method that you may want to look at. You may also want to look at the docblocks for JDatabaseQueryLimitable.

One thing I would suggest is that when you get that error, usually it is really because you do have a problem in your query (often wrong quoting or something being empty that needs not to be empty. To see your generated query you an use

echo $query->dump();

and then try running it directly in sql.

Also in general it's wise to use $db->quote() and $db->quoteName() if you are using the API that way you won't run into quoting problems. I think you may have a quoting problem but it's hard to know without knowing your field names.

0
votes

From Joomla4, binding data to named parameters is possible with the bind() method. This has been asked for for many years and finally it has come to the CMS.

The syntax is precisely as prophecized in the snippet in the post

$taste = "sweet";

$db = JFactory::getDbo();
$query = $db->getQuery(true)
    ->select("*")
    ->from($db->quoteName("food"))
    ->where($db->quoteName("taste") . " = :taste")
    ->bind(":taste", $taste);
$db->setQuery($query);
$rows = $db->loadAssocList();