SQL wildcard problem with all LIKE searches in Mautic and API

Mautic Series

7.2.x series

Mautic installed version

7.2.0

Way of installing

Other (please specify if relevant in the bug report)

PHP version

8.5

What browsers are you seeing the problem on?

Firefox

What happened?

There is a serious problem in all past versions including the current ones, related to wildcard chars in all search queries using ->like() (72 matches in 32 files).

Lets say we have two contacts:

1. [email protected]
2. [email protected]

Both email addresses are valid and belong to two different people.

The problem occurs, if we search for [email protected], which will return both users instead of only user 2.

Especially for API calls to synchronize contacts from/to a CRM this behavior is critical, because we would expect to only get the exact 1 contact back searching for email:[email protected] !is:anonymous, e.g. to update the contact in Mautic.

This happened to us in a production environment with hundreds of thousand contacts, where such name similarities for email-addresses occur more often than we had thought (Someone told me this behavior can even open up SQL wildcard attacks, but I'm not familiar with such). Because of this issue, contacts lost their CRM relation (we using the email-address as unique personal identifier).

How to fix

I'd suggest to escape all SQL wildcard chars using addcslashes() e.g. with a trait:

trait LikeQueryHelper
{
    protected function escapeLikeWildcards(string $value, string $escapeChar = '\\'): string
    {
        // escape the escape character itself first, then the wildcards
        $escaped = addcslashes($value, $escapeChar . '%_');
        
        return '%' . $escaped . '%';
    }
}

Edit: Probably the $escapeChar can be left away, just using addcslashes($value, '%_') will be enough.

Usage example

if (isset($options['search']) && $options['search']) {
    $query->andWhere(
        $query->expr()->or(
            $query->expr()->like('ec.subject', ':search'),
            $query->expr()->like('e.subject', ':search'),
            $query->expr()->like('e.name', ':search')
        )
    // before: ->setParameter('search', '%'.$options['search'].'%');
    )->setParameter('search', $this->escapeLikeWildcards($options['search']));
}

How can we reproduce this issue?

Step 1: Create two contacts

1. [email protected]
2. [email protected]

Step 2: In Mautic backend select "Contacts" and search for [email protected]: Both contacts showing up (Same for API searches email:[email protected] !is:anonymous).

Relevant log output

Code of Conduct

• I confirm that I have read and agree to follow this project's Code of Conduct