Is the following everything one should do to prevent SQL injection?

Question

In order to sanitize the user input in an article insertion form (title and HTML content), here are the bits that I've included:

1. Use of JInput:

   $jinput = JFactory::getApplication()->input;
   

2. get method for a string input:

   $jinput->get('stringVar', '', 'string');
   

3. JComponentHelper for HTML input:

   $html = JComponentHelper::filterText($jinput->post->get('inputName', '', 'raw'));
   

4. The quote method of JDatabaseQuery to escape the string before including it in the DB query:

   $db->quote($stringVar)
   

5. Casting an integer non-input variable to int (being non-input I guess it'd be no problem, but still) before including it in the DB query:

   (int) $intVar
   

Are there inaccuracies in the above? And is this sufficient?

Answer 1

First, when reading user input you should make sure it's the correct type. The Input/Filter APIs don't handle array/string conversion. So if you're expecting STRING filter to return a string be aware it could return an array of strings instead. Same goes for other filters.

Using STRING filter is generally too restrictive for fields like articles titles. Where HTML is not intended to be used, it should be escaped on output rather than being stripped out before storing in database. The misuse of this filter is a really big usability issue in Joomla that can't be solved in B/C safe manner. You should reconsider using it. Also note that STRING filter does not strip out quotes so you MUST escape it when using inside attributes.

In J4 Database API uses only prepared statements. You can and should use parameters for user input. Joomla supports named parameter syntax. Be aware that bind() method only supports binding parameters and not their values, i.e. the variables are passed by reference. Take care when building queries using loops or when reusing variables. On the other hand, this allows running repeated statements without having to prepare the query again.

To bind a parameter, use query's bind() method:

use Joomla\CMS\Factory;

// Get database instance
$db = Factory::getDbo();

// Get a new query
$query = $db->getQuery(true)
    ->select('*')
    ->from('#__mytable')
    ->where($db->quoteName('myColumn') . ' = :myParam')
    ->bind(':myParam', $myValue);

// Prepare the query
$db->setQuery($query);

// Set the parameter value
$myValue = $this->getSomeUserInputValue();

// Execute the query and load the results
$result = $db->loadObject();

// We can update the parameter value and run the same query again
$myValue = $this->getSomeOtherUserInputValue();
$result = $db->loadObject();

bind() method accepts several more arguments: parameter type, length and driver options. You can read about them in PHP documentation. Note that for parameter type you should use Joomla's defined constants which you can find in Joomla\Database\ParameterType class. It defaults to string type, but, for example, if the parameter is an integer you would use Joomla\Database\ParameterType::INTEGER:

$query->bind(':myParam', $myValue, ParameterType::INTEGER);

Although if you don't intend to use repeated statements, you can omit the parameter and just cast it to an integer as you are currently doing.

There is also a bindArray() method which accepts an array of values and creates parameters in place. It binds the value and not the variable so it's perfect for building multi-row insert/update queries:

$query = $db->getQuery(true)
    ->insert('#__mytable')
    ->columns(['id', 'title']);

    foreach ($rows as $row)
    {
        $query->values(
            implode(
                ',',
                $query->bindArray(
                    // $row = ['id' => 1, 'title' => 'my title']
                    $row,
                    [ParameterType::INTEGER, ParameterType::STRING]
                )
            )
        );
    }

There are also whereIn()/whereNotIn() methods for quickly building WHERE IN/WHERE NOT IN queries.

$query->select('*')->from('#__mytable')->whereNotIn('id', $arrayOfIds);

They internally use bindArray() and the above query results in this SQL string:

SELECT * FROM #__mytable WHERE id NOT IN (:preparedArray1,:preparedArray2,:preparedArray3)