From fabb30b2224198809a3fbe7967a787c429a91d0f Mon Sep 17 00:00:00 2001
From: Lari Taskula <lari.taskula@hypernova.fi>
Date: Wed, 14 Sep 2022 14:34:48 +0000
Subject: [PATCH] Bug 31565: [21.11.x] Fix datatables searching escape for
 exact search

To test:
1. Add a new patron category with categorycode 'TEST_CAT'
2. Add a patron to category 'TEST_CAT'
3. Go to Patrons search view
4. Under "Search for patron" filters, filter by category you created at step 1
5. Click Search
6. Observe no results
7. Apply this patch and reload
8. Repeat 3-5
9. Observe expected result

Double check tests from Bug 30393 still work as expected:

10. Go to the cities page
11. Add two cities:
   - 'Cordoba %'
   - 'Buenos Aires _'
   - 'London \'
12. Use the column search on the name, alternating _, \ and % as the query
=> SUCCESS: Filtering works correctly!

Signed-off-by: David Nind <david@davidnind.com>
Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>

Bug 31565: Escape datatables MySQL special characters only in LIKE queries

Escaping \ yields no results when performing exact search in datatables.
This patch adds MySQL special character escaping only to LIKE searches.

To test:
1. Add following categorycode 'TEST\CAT' by SQL
insert into categories (categorycode,description) values ('TEST\\CAT', 'TEST\\CAT');
2. Add a patron into TEST\CAT category
3. Go to patron search
4. Limit search by category TEST\CAT
5. Observe no results
6. Apply patch
7. Refresh patron search page
8. Limit search by category TEST\CAT
9. Observe Koha redirecting you to patron you chose in step 2

Signed-off-by: David Nind <david@davidnind.com>
Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>

Bug 31565: (QA follow-up) Remove support for _ as wildcard in -like queries

Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>

Signed-off-by: Lucas Gass <lucas@bywatersolutions.com>
(cherry picked from commit 8fead08cedd8eb56601f5be66c8f9056966759bd)
Signed-off-by: Arthur Suzuki <arthur.suzuki@biblibre.com>
---
 koha-tmpl/intranet-tmpl/prog/js/datatables.js | 14 ++++++++++----
 1 file changed, 10 insertions(+), 4 deletions(-)

diff --git a/koha-tmpl/intranet-tmpl/prog/js/datatables.js b/koha-tmpl/intranet-tmpl/prog/js/datatables.js
index d22e5d7f5b..be9b467ff7 100644
--- a/koha-tmpl/intranet-tmpl/prog/js/datatables.js
+++ b/koha-tmpl/intranet-tmpl/prog/js/datatables.js
@@ -591,15 +591,21 @@ jQuery.fn.dataTable.ext.errMode = function(settings, note, message) {
 
                                 function build_query(col, value){
 
-                                    // escape SQL special characters
-                                    value = value.replace(/(\%|\_|\\)/g, "\\$1" );
-
                                     var parts = [];
                                     var attributes = col.data.split(':');
                                     for (var i=0;i<attributes.length;i++){
                                         var part = {};
                                         var attr = attributes[i];
-                                        part[!attr.includes('.')?'me.'+attr:attr] = options.criteria === 'exact'
+                                        let criteria = options.criteria;
+                                        if ( value.match(/^\^(.*)\$$/) ) {
+                                            value = value.replace(/^\^/, '').replace(/\$$/, '');
+                                            criteria = "exact";
+                                        } else {
+                                           // escape SQL LIKE special characters % and _
+                                           value = value.replace(/(\%|\\)/g, "\\$1");
+                                        }
+
+                                        part[!attr.includes('.')?'me.'+attr:attr] = criteria === 'exact'
                                             ? value
                                             : {like: (['contains', 'ends_with'].indexOf(options.criteria) !== -1?'%':'') + value + (['contains', 'starts_with'].indexOf(options.criteria) !== -1?'%':'')};
                                         parts.push(part);
-- 
2.39.5