Filter option "case_sensitive" is not being respected with MySQL

[phansys]

Environment

Sonata packages

show

``` $ composer show --latest 'sonata-project/*' sonata-project/admin-bundle 3.95.0 3.95.0 The missing Symfony Admin Generator sonata-project/block-bundle 3.22.0 4.5.3 Symfony SonataBlockBundle sonata-project/cache 2.0.1 2.1.1 Cache library sonata-project/datagrid-bundle 3.3.0 3.3.0 Symfony SonataDatagridBundle sonata-project/doctrine-extensions 1.11.0 1.12.0 Doctrine2 behavioral extensions sonata-project/doctrine-orm-admin-bundle 3.32.0 3.32.0 Integrate Doctrine ORM into the SonataAdminBundle sonata-project/entity-audit-bundle 1.1.0 1.2.0 Audit for Doctrine Entities sonata-project/exporter 2.5.0 2.6.1 Lightweight Exporter library sonata-project/form-extensions 1.8.1 1.9.0 Symfony form extensions sonata-project/intl-bundle 2.10.0 2.10.1 Symfony SonataIntlBundle sonata-project/media-bundle 3.29.0 3.31.0 Symfony SonataMediaBundle sonata-project/twig-extensions 1.5.0 1.5.1 Sonata twig extensions sonata-project/user-bundle 4.10.1 4.11.1 Symfony SonataUserBundle ```

Symfony packages

show

``` $ composer show --latest 'symfony/*' symfony/amazon-sqs-messenger v5.2.2 v5.2.4 Symfony Amazon SQS extension Messenger Bridge symfony/amqp-messenger v5.2.2 v5.2.4 Symfony AMQP extension Messenger Bridge symfony/asset v4.4.19 v5.2.4 Manages URL generation and versioning of web assets such as CSS stylesheets, JavaScript files and image files symfony/browser-kit v5.2.2 v5.2.4 Simulates the behavior of a web browser, allowing you to make requests, click on links and submit forms programmatically symfony/cache v5.2.2 v5.2.6 Provides an extended PSR-6, PSR-16 (and tags) implementation symfony/cache-contracts v2.2.0 v2.2.0 Generic abstractions related to caching symfony/config v4.4.19 v5.2.4 Helps you find, load, combine, autofill and validate configuration values of any kind symfony/console v4.4.19 v5.2.6 Eases the creation of beautiful and testable command line interfaces symfony/css-selector v5.2.2 v5.2.4 Converts CSS selectors to XPath expressions symfony/debug v4.4.19 v4.4.20 Provides tools to ease debugging PHP code symfony/debug-bundle v5.1.11 v5.2.4 Provides a tight integration of the Symfony Debug component into the Symfony full-stack framework symfony/debug-pack v1.0.9 v1.0.9 A debug pack for Symfony projects symfony/dependency-injection v4.4.19 v5.2.6 Allows you to standardize and centralize the way objects are constructed in your application symfony/deprecation-contracts v2.2.0 v2.2.0 A generic function and convention to trigger deprecation notices symfony/doctrine-bridge v4.4.19 v5.2.6 Provides integration for Doctrine with various Symfony components symfony/doctrine-messenger v5.2.2 v5.2.5 Symfony Doctrine Messenger Bridge symfony/dom-crawler v5.2.2 v5.2.4 Eases DOM navigation for HTML and XML documents symfony/dotenv v5.2.2 v5.2.4 Registers environment variables from a .env file symfony/error-handler v4.4.19 v5.2.6 Provides tools to manage errors and ease debugging PHP code symfony/event-dispatcher v4.4.19 v5.2.4 Provides tools that allow your application components to communicate with each other by dispatching events and listening to them symfony/event-dispatcher-contracts v1.1.9 v2.2.0 Generic abstractions related to dispatching event symfony/expression-language v5.2.2 v5.2.4 Provides an engine that can compile and evaluate expressions symfony/filesystem v4.4.19 v5.2.6 Provides basic utilities for the filesystem symfony/finder v4.4.19 v5.2.4 Finds files and directories via an intuitive fluent interface symfony/flex v1.11.0 v1.12.2 Composer plugin for Symfony symfony/form v4.4.19 v5.2.6 Allows to easily create, process and reuse HTML forms symfony/framework-bundle v4.4.19 v5.2.6 Provides a tight integration between Symfony components and the Symfony full-stack framework symfony/http-client v5.2.2 v5.2.6 Provides powerful methods to fetch HTTP resources synchronously or asynchronously symfony/http-client-contracts v2.3.1 v2.3.1 Generic abstractions related to HTTP clients symfony/http-foundation v4.4.19 v5.2.4 Defines an object-oriented layer for the HTTP specification symfony/http-kernel v4.4.19 v5.2.6 Provides a structured process for converting a Request into a Response symfony/intl v5.2.2 v5.2.4 Provides a PHP replacement layer for the C intl extension that includes additional data from the ICU library symfony/mailer v5.2.2 v5.2.6 Helps sending emails symfony/maker-bundle v1.28.0 v1.30.2 Symfony Maker helps you create empty commands, controllers, form classes, tests and more so you can forget about writing boilerplate code. symfony/messenger v5.2.2 v5.2.5 Helps applications send and receive messages to/from other applications or via message queues symfony/mime v5.2.2 v5.2.6 Allows manipulating MIME messages symfony/monolog-bridge v5.2.2 v5.2.5 Provides integration for Monolog with various Symfony components symfony/monolog-bundle v3.6.0 v3.7.0 Symfony MonologBundle symfony/options-resolver v5.2.2 v5.2.4 Provides an improved replacement for the array_replace PHP function symfony/phpunit-bridge v5.2.2 v5.2.6 Provides utilities for PHPUnit, especially user deprecation notices management symfony/polyfill-php80 v1.22.0 v1.22.1 Symfony polyfill backporting some PHP 8.0+ features to lower PHP versions symfony/process v4.4.19 v5.2.4 Executes commands in sub-processes symfony/profiler-pack v1.0.5 v1.0.5 A pack for the Symfony web profiler symfony/property-access v5.2.2 v5.2.4 Provides functions to read and write from/to an object or array using a simple string notation symfony/property-info v5.2.2 v5.2.4 Extracts information about PHP class' properties using metadata of popular sources symfony/proxy-manager-bridge v4.4.18 v5.2.4 Symfony ProxyManager Bridge symfony/redis-messenger v5.2.2 v5.2.4 Symfony Redis extension Messenger Bridge symfony/requirements-checker v1.1.8 v2.0.0 Check Symfony requirements and give recommendations symfony/routing v4.4.19 v5.2.6 Maps an HTTP request to a set of configuration variables symfony/security v4.4.19 v4.4.21 Provides a complete security system for your web application symfony/security-acl v3.1.1 v3.1.1 Symfony Security Component - ACL (Access Control List) symfony/security-bundle v4.4.19 v5.2.6 Provides a tight integration of the Security component into the Symfony full-stack framework symfony/serializer v5.2.2 v5.2.4 Handles serializing and deserializing data structures, including object graphs, into array structures or other formats like XML and JSON. symfony/service-contracts v2.2.0 v2.2.0 Generic abstractions related to writing services symfony/stopwatch v5.2.2 v5.2.4 Provides a way to profile code symfony/string v5.2.2 v5.2.6 Provides an object-oriented API to strings and deals with bytes, UTF-8 code points and grapheme clusters in a unified way symfony/swiftmailer-bundle v3.5.2 v3.5.2 Symfony SwiftmailerBundle symfony/templating v4.4.19 v5.2.4 Provides all the tools needed to build any kind of template system symfony/translation v4.4.19 v5.2.6 Provides tools to internationalize your application symfony/translation-contracts v2.3.0 v2.3.0 Generic abstractions related to translation symfony/twig-bridge v4.4.19 v5.2.6 Provides integration for Twig with various Symfony components symfony/twig-bundle v4.4.19 v5.2.4 Provides a tight integration of Twig into the Symfony full-stack framework symfony/validator v4.4.19 v5.2.6 Provides tools to validate values symfony/var-dumper v5.2.2 v5.2.6 Provides mechanisms for walking through any arbitrary PHP variable symfony/var-exporter v5.2.2 v5.2.4 Allows exporting any serializable PHP data structure to plain PHP code symfony/web-profiler-bundle v5.0.11 v5.2.6 Symfony WebProfilerBundle symfony/web-server-bundle v4.4.19 v4.4.21 Provides commands for running applications using the PHP built-in web server symfony/webpack-encore-bundle v1.9.0 v1.11.1 Integration with your Symfony app & Webpack Encore! symfony/workflow v5.2.2 v5.2.4 Provides tools for managing a workflow or finite state machine symfony/yaml v4.4.19 v5.2.5 Loads and dumps YAML files ```

PHP version

show

``` $ php -v PHP 7.3.24 (cli) (built: Nov 18 2020 09:59:35) ( NTS ) Copyright (c) 1997-2018 The PHP Group Zend Engine v3.3.24, Copyright (c) 1998-2018 Zend Technologies with Zend OPcache v7.3.24, Copyright (c) 1999-2018, by Zend Technologies with blackfire v1.46.4~linux-x64-non_zts73, https://blackfire.io, by Blackfire ```

Subject

Filter option "case_sensitive" is not being respected with MySQL. IMO, we should find a way to detect the connection type and provide the required modifications to the query in order to get this option honored when its value is true.

See:

• https://dev.mysql.com/doc/refman/8.0/en/case-sensitivity.html
• https://stackoverflow.com/questions/8083455/like-case-sensitive-in-mysql

Related to #870.

Steps to reproduce

Use the option 'case_sensitive' => true in a string filter within a MySQL connection.

Expected results

No results when a string in the database is not using the same casing that the filter term.

Actual results

The filter matches with terms using different casing. IE "Term" matches "term".

[VincentLanglet]

@phansys I think we can fix it by using the BINARY method. Look at https://github.com/sonata-project/SonataDoctrineORMAdminBundle/pull/978#pullrequestreview-366854764

Fixing this bug could change the behavior for a lot of person. For instance, my filter are not case_sensitive because of my database, but I never thought to pass the option case_sensitive => false. So I'll recommend that we introduce a global option for case_sensitive in order to easily select the behavior we want.

[phansys]

Maybe changing the name of this option (deprecating the current one) to make clearer its real behavior would be simpler. I think something like force_case_insensitivity reflects its purpose in a better way. With this approach I think we could keep the current logic for all the RDBMSs. WDYT?

[VincentLanglet]

Maybe changing the name of this option (deprecating the current one) to make clearer its real behavior would be simpler. I think something like force_case_insensitivity reflects its purpose in a better way. With this approach I think we could keep the current logic for all the RDBMSs. WDYT?

Not sure to understand. You want to keep the behavior like this but changing the name to avoid the confusing about the fact it doesn't work on some RDBMS ?

I was thinking we could

• Add a global option in the configuration of doctrine_orm for the stringFilter (case_sentivite, default true)
• Fix the bug that the option doesn't for some Database, by using the binary method of https://github.com/beberlei/DoctrineExtensions
• Add a message in the changelog that if the developer wants to keep the previous behavior, he can globally set case_sentive: false

Another idea, proposed here https://github.com/sonata-project/SonataDoctrineORMAdminBundle/pull/978#issuecomment-593100367 would be to make the case_sensitive option nullable.

• null rely on the database (it's the current true)
• true force the case sensitivity
• false force the non-case sensitivity

With this we could change the default value to null, the global option is not needed then.

[phansys]

Not sure to understand. You want to keep the behavior like this but changing the name to avoid the confusing about the fact it doesn't work on some RDBMS ?

Yes, that's what I proposed. About the possible solutions you share, I'm with the nullable option.

[VincentLanglet]

Not sure to understand. You want to keep the behavior like this but changing the name to avoid the confusing about the fact it doesn't work on some RDBMS ?

Yes, that's what I proposed. About the possible solutions you share, I'm with the nullable option.

I would prefer the nullable option rather than accepting this option won't work sometimes ^^'. Do you want to try the PR ? :) This https://github.com/sonata-project/SonataDoctrineORMAdminBundle/pull/1391 will be useful for the tests.