Issue19576

The "contains" operator does not work as expected for text fields (19576)

Issue Description

Inside the Strapi Application, it is possible to filter items by their attributes. For example, if there is a collection of ‘Dog’ and each of the dogs has an attribute “name”, it is possible to filter dogs by their name, in many diferent ways.

One of those filters uses a string and searches if any of the ‘Dog’ entries contain the given input. For example:

1. Input search string: ‘hunting’;
2. Entries of Dog’s whose name contains ‘hunting’ are returned.

There is an issue with this feature, when an underscore character is added at the end of the search string, it is expected that only results that contain an underscore are returned. However, some return values that do not contain an underscore are also present.

                                              Figure 1: Defective Search Results.

Root Cause

If this happens when a user saves dogs entities following the format ‘hunting_dog_n’, then the user won’t just get these results, instead he will also get any result that has the ‘hunting’ word on it, even without the underscore.

Version

This issue happened using:

• Node.js version: 20.9.0;
• NPM version: 9.8.1;
• Strapi version: 4.16.2;
• Database: PostgreSQL.

Requirement

As a user I want to be able to filter my collection by an attribute containing the given search criteria.

                                              Figure 2: Sequence Diagram for the use case.

Investigation

A thorough research was made on the project’s source code. This operation was shown to be very complex owing to:

• The project’s folder structure was very misleading.
• There were no clear indications (README) on how to contribute for the desired fix of the issues.
• The variables used in the code were not explicit.

However, after an immense routing on the application’s source code, the target class was found. Strapi uses the browser’s URL to pass parameters to form search queries. This specific ‘contains’ filter is present in the ´FilterPopoverURLQuery.tsx´class. It uses a React Custom Hook Search Query to push parameters into the URL and trigger the server to respond to the new request.

In Figure 3 it is possible to see how the URL looks like if the ‘contains’ filter is used along with the keyword of ‘hunting_’ with the purpose of searching for dogs that match these criteria.

                                              Figure 3: Strapi URL Query

The issue’s author already provided that the database involved uses SQL (Postgre); the following code is present on the server’s route responsible for parsing the request with the ‘contains’ filter:

                                              Figure 4: Route Request Parser for 'contains' filter.

Solution

Therefore, the final query to be sent to the database would look like:

SELECT * from Dogs d WHERE d.name LIKE ´%hunting_%´

The % character is a wildcard used in SQL; it serves the purpose of matching any character. Thus, this query would look for any entry where the name contains ‘hunting_’ with any prefix or suffix.

The problem here is present in the underscore character. As a matter of fact, this ‘ _ ’ character is also used as a wildcard in SQL. It is used to match any individual character.When the underscore is used followed up by a ‘%’ character, then the query will ignore the underscore as an actual character and proceed to use it as a wildcard. This is where the issue lies, it is impossible to objectively fetch only the entries which contain ‘hunting_’. The return values will match any word that integrates ‘hunting’ ignoring the underscore, and therefore leading to the results present in Figure 1.

Design

The fix for this issue is as straightforward as escaping the underscore with a backwards slash (adding a backwards slash before the underscore), so it is interpreted as a normal character by SQL.

Moreover, the SQL query also must be changed to indicate that the escape is being doneon a backwards slash.

                                              Figure 5: Fix changes on file where.ts

Result Testing and validation

After applying the fix, the same query can be tested. This time, the desired result is grasped successfully (Figure 6), only entrances that contain an underscore are returned, solving the issue.

                                              Figure 5: Testing Results with the fix.

Conclusion

The fix was published as a Pull request on Strapi’s GitHub thread and is currently being evaluated by the contributors of the project.