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

Question

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.

• JDatabase for Joomla 3.x there is no bind method, and the information is scant, seems like is not completed.
• JDatabase for Joomla 2.5 has more information, but obviously is not my version. there is no bind method.
• JDatabaseQuery for Joomla 3.x there is no bind method
• JDatabaseQuerySqlite for Joomla 3.x has bind method
• JDatabaseQueryPdo for Joomla 3.x there is no bind method
• JTable for Joomla 3.x has bind method

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.

Madelainemadeleine

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... . – Roid

I Was referring not only to bind values but also to avoid SQL Injection in general :-) – Madelainemadeleine

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. – Roid

Answer 1

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.

Erleena

Does that mean that Joomla sucks, or...? – Seif

@OlleHärstedt That means that Joomla would be more secure if it used something like PDO and prepared statements instead of using mysqli like it was the old mysql extension. I supose they choose to work this way as a compatibility layer with older Joomla versions... – Erleena

Answer 2

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.

Tova

Hi, and how can I use that methods? Because I go to joomla 3.3 API documentation and both JDatabaseQueryPreparable and JDatabaseQueryPreparable only have two methods and there are no examples. I mean I am using JFactory::getDbo() to build the query, I don't know what I have to do to use JTable or other two. – Madelainemadeleine

I'm really not sure what exactly you are doing, given that you are doing a SELECT. – Tova

This will work: $db = JFactory::getDbo(); $query = $db->getQuery(true); $query ->select("*") ->from($db->quoteName("food")) ->where("taste = " . $db->quote("sweet")); $db->setQuery($query); $rows = $db->loadAssocList(); But there is a way to bind() a not to quote()? – Madelainemadeleine

It looks like only the Oracle, PDO and sqlite implement it. So you can't use the bind method in mysqli using the api the way you have. You probably need to write some straight sql. – Tova

Answer 3

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.

• Early reference in Joomla Docs: https://docs.joomla.org/J4.x:Moving_Joomla_To_Prepared_Statements
• Proper Joomla Documenation: https://docs.joomla.org/J4.x:Selecting_data_using_JDatabase
• Here's a good tutorial: https://www.techfry.com/joomla/prepared-statements-in-joomla-4

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();

Megavolt