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

1
  • Where did you find that ->like() method? Apr 19, 2018 at 3:20

1 Answer 1

8

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.

5
  • 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, 2018 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. Apr 19, 2018 at 23:40
  • 2
    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, 2018 at 17:05
  • 1
    @mickmackusa I get the following error :bind(): Argument #2 ($value) cannot be passed by reference. Solved If wildcards are included in a line before: $searchP = "%$searchP%"; and ->bind(':search', $searchP);
    – Peli
    Mar 23, 2023 at 10:26
  • @Peli whoops, thanks. Fixed. Mar 23, 2023 at 11:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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