SQL Injections using Jfactory::getDBO and setQuery()

Question

$db = JFactory::getDBO();
$searchP = JRequest::getVar('key');
$sql = "SELECT name FROM people LIKE " . "'%" . $searchP . "%'";

$db->setQuery($sql);
$fileR = $db->loadObjectList();

Will setQuery() throw an error for having two SQL statements in the same string?

For example, according to my code, is it possible to execute a drop table command by passing this query parameter or something similar:

key=a'; DROP TABLE people; # 

Furthermore will using something like

$db = JFactory::getDBO();
$query = $db->getQuery(true); 
$query ->setLimit('1');
$query ->select($db->quoteName('name'));
$query ->from($db->quoteName('people'));
$query ->like($db->quoteName($searchP));
$db->setQuery($query);

prevent injection since the query object's query limit is being set to 1?

Answer 1

You won't need to worry about sql injection attacks if you use the quote() ( or q()) and quoteName() (or qn()) methods appropriately.

From https://docs.joomla.org/Secure_coding_guidelines#Secure_on_search

Secure on search

Special attention should be paid to LIKE clauses which contain the % wildcard character as these require special escaping in order to avoid possible denial of service attacks. LIKE clauses can be handled like this:

// Construct the search term by escaping the user-supplied string and, if required, adding the % wildcard characters manually.

$search = '%' . $db->escape( $search, true ) . '%' );

// Construct the SQL query, being careful to suppress the default behaviour of Quote so as to prevent double-escaping.

$query = 'SELECT  * FROM #__table WHERE `field` LIKE ' . $db->quote( $search, false );

->like(), as far as I have researched, doesn't exist. So, based on the above documentation recommendation and assuming you want to search the name column, I'll suggest the following code block:

$db = JFactory::getDBO();
$query = $db->getQuery(true)
            ->select($db->qn('name'))
            ->from($db->qn('people'))
            ->where($db->qn('name') . ' LIKE ' . $db->q('%' . $db->escape($searchP, true) . '%', false));
$db->setQuery($query);

When performing a LIKE query it is often logical to receive multiple rows of data in the resultset. Since I believe you were merely using the setLimit() method as a security precaution, I'll remove it for the sake of improved querying.

---

Despite what inf3rno's comment states, there is no bind() method available according to the latest Documentation. (I tested the snippet anyhow using both :needle and ? as placeholders and received: Fatal error: Call to a member function bind() on null) I wish it wasn't this way because prior to entering Joomla-land, I would use prepared statements with placeholders for ALL external / untrustworthy data fed to my queries.

A relevant post from Lodder from 2014 regarding prepared statements in Joomla.

The intent/effect of quote(escape()) method in my code block above is to ensure that no matter what string is passed to the query, the string is always treated as a single value in the expression. This seems like the equivalent of mysqli_real_escape() (but I didn't check under the Joomla hood). Unfortunately, many StackOverflow developers flag mysqli_real_escape() as sub-optimal and recommend prepared statements with placeholders instead. I will be updating all of my Joomla projects as soon as the bind() method is available.

---

I ran the following test on my Joomla 3.8.6:

$needle = "key=a'; INSERT INTO [... redacted query that would be successful on its own ...]; #";
try {
    $db = JFactory::getDbo();
    $sql = "SELECT [redacted] FROM [redacted] WHERE [redacted] LIKE " . "'%" . $needle . "%'";
    $db->setQuery($sql);
    var_export($db->loadAssocList());
} catch (Exception $e) {
    echo $e->getMessage();
}

and received:

1064 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 'INSERT INTO

This indicates that Joomla's setQuery() method will choke on multiple queries.