6
$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?

  • Where did you find that ->like() method? – mickmackusa Apr 19 '18 at 3:20
7

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.

  • My understanding is that using string escaping will just prevent php from being executed (because it is just making sure that the user input is treated as a string and is properly escaped). If I pass a string with a semi colon and add another sql command after that, wouldn't the query I pass to mysql execute that either way? Could you briefly explain how string escaping would prevent injection? – Jay Shri Apr 19 '18 at 17:28
  • @JayShri I've extended my answer to address your comment. String escaping protects against injection by ensuring any malicious / single-quote carrying string is merely treated as a string value that is wrapped in single quotes. This is important for non-malicious strings as well, like the name Padraic O'Neil which can compromise a query if the the mid-string ' is not escaped. – mickmackusa Apr 19 '18 at 23:40
  • 1
    This is good, but I realized another important aspect is making sure your input is good. Before checking to escape strings. $jinput = JFactory::getApplication()->input; $q = $jinput->get('search', '', 'WORD'); This will remove characters other than characters z-a and underscore. – Jay Shri Apr 20 '18 at 17:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.