Search code examples
mysqlcakephpfull-text-searchsql-injectioncakephp-3.x

Preventing SQL injections in CakePHP3 full text index match

I need to do a fulltext search on a table in CakePHP3. I do the search like this:

$ids = $this->I18n->find('list', [
    'valueField' => 'foreign_key',
    'conditions' => [
        'field IN' => ['name', 'description_search', 'description_short_search'],
        'model' => 'Products',
        'locale' => $lang,
        'MATCH (content) AGAINST ("'.$filteredValue.'")',
    ],
])->toArray();

This works, but is unsafe - this is a perfect place for an SQL injection. I tried replacing it with a parameter (MATCH (content) AGAINST (?)' => $filteredValue), but that generates an error Invalid parameter number: mixed named and positional parameters.

How can I safeguard against this?

(Yes, this is a match against the standard i18n table. A bit of a hack, but irrelevant to the question.)

Solution

  • Using bindings

    That's not how bindings work anymore, in CakePHP 3.x you have to use the Query::bind() method (or StatementInterface::bindValue() when using custom statements).

    $ids = $this->I18n
    ->find('list', [
        'valueField' => 'foreign_key',
        'conditions' => [
            'field IN' => ['name', 'description_search', 'description_short_search'],
            'model' => 'Products',
            'locale' => $lang,
            'MATCH (content) AGAINST (:against)',
        ],
    ])
    ->bind(':against', $filteredValue, 'string')
    ->toArray();

    See also