SQL Injections using Jfactory::getDBO and setQuery() with LIKE condition

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.

---

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.

---

From Joomla4, bind() can be used in conjunction with a placeholder. The previously scripted query can simplified to:

$searchLike = "%$searchP%"; // declare search value wrapped in wildcard characters

$query = $db->getQuery(true)
            ->select($db->qn('name'))
            ->from($db->qn('people'))
            ->where($db->qn('name') . ' LIKE :search')
            ->bind(':search', $searchLike);
$db->setQuery($query);

Note that the bound value must be declared as a variable in advance.