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.
->like()
method? – mickmackusa♦ Apr 19 '18 at 3:20