Bug 31565 - Patron search filter by category code with special character returns no results
Summary: Patron search filter by category code with special character returns no results
Status: RESOLVED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Staff interface (show other bugs)
Version: master
Hardware: All All
: P5 - low normal (vote)
Assignee: Lari Taskula
QA Contact: Tomás Cohen Arazi
URL:
Keywords: release-notes-needed
Depends on: 30393
Blocks: 30982
  Show dependency treegraph
 
Reported: 2022-09-14 13:53 UTC by Lari Taskula
Modified: 2022-11-26 23:34 UTC (History)
7 users (show)

See Also:
Change sponsored?: ---
Patch complexity: Trivial patch
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:
22.11.00, 22.05.07, 21.11.14


Attachments
Bug 31565: Fix datatables searching escape for exact search (3.33 KB, patch)
2022-09-14 14:38 UTC, Lari Taskula
Details | Diff | Splinter Review
Bug 31565: Fix datatables searching escape for exact search (2.47 KB, patch)
2022-09-14 15:04 UTC, Lari Taskula
Details | Diff | Splinter Review
Bug 31565: Escape datatables MySQL special characters only in LIKE queries (2.30 KB, patch)
2022-09-16 14:15 UTC, Lari Taskula
Details | Diff | Splinter Review
Bug 31565: Fix datatables searching escape for exact search (2.50 KB, patch)
2022-09-19 14:31 UTC, Lari Taskula
Details | Diff | Splinter Review
Bug 31565: Escape datatables MySQL special characters only in LIKE queries (2.30 KB, patch)
2022-09-19 14:31 UTC, Lari Taskula
Details | Diff | Splinter Review
Bug 31565: Fix datatables searching escape for exact search (2.54 KB, patch)
2022-09-19 17:56 UTC, David Nind
Details | Diff | Splinter Review
Bug 31565: Escape datatables MySQL special characters only in LIKE queries (2.35 KB, patch)
2022-09-19 17:56 UTC, David Nind
Details | Diff | Splinter Review
Bug 31565: Fix datatables searching escape for exact search (2.60 KB, patch)
2022-09-19 18:09 UTC, Tomás Cohen Arazi
Details | Diff | Splinter Review
Bug 31565: Escape datatables MySQL special characters only in LIKE queries (2.41 KB, patch)
2022-09-19 18:09 UTC, Tomás Cohen Arazi
Details | Diff | Splinter Review
Bug 31565: Escape datatables MySQL special characters [FIX TESTS] (5.27 KB, patch)
2022-09-26 12:48 UTC, Mason James
Details | Diff | Splinter Review
Bug 31565: (QA follow-up) Remove support for _ as wildcard in -like queries (1.31 KB, patch)
2022-09-26 13:34 UTC, Tomás Cohen Arazi
Details | Diff | Splinter Review
Bug 31565: [22.05.x] Fix datatables searching escape for exact search (3.38 KB, patch)
2022-11-01 11:36 UTC, Nick Clemens
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Lari Taskula 2022-09-14 13:53:54 UTC
To reproduce:
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

Something to start from:
1. Go to http://yourinstance/api/v1/patrons?_page=1&_per_page=20&q=[{"me.category_id":"TEST\\_CAT"}]&_match=contains&_order_by=+me.surname,+me.firstname,+me.middle_name,+me.othernames,+me.street_number,+me.address,+me.address2,+me.city,+me.state,+me.postal_code,+me.country
2. Observe no results
3. Go to http://yourinstance/api/v1/patrons?_page=1&_per_page=20&q=[{"me.category_id":"TEST_CAT"}]&_match=contains&_order_by=+me.surname,+me.firstname,+me.middle_name,+me.othernames,+me.street_number,+me.address,+me.address2,+me.city,+me.state,+me.postal_code,+me.country

(difference is that we've removed escaping characters \\ from q=[{"me.category_id":"TEST\\_CAT"}] to q=[{"me.category_id":"TEST_CAT"}]

4. Observe 1 result (the patron you added at step 2)

So it has something to do with escape not functioning as expected. If you try q=[{"me.category_id":"TES__CAT"}] or q=[{"me.category_id":"TEST%CAT"}] there are again no results so it also seems like these special characters are not being passed to the database query.

Related Bug 30393
Comment 1 Lari Taskula 2022-09-14 14:38:02 UTC Comment hidden (obsolete)
Comment 2 Lari Taskula 2022-09-14 14:43:00 UTC
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30393#c0 refers to MySQL documentation https://dev.mysql.com/doc/refman/8.0/en/string-literals.html

On this page it is also mentioned that
"The \% and \_ sequences are used to search for literal instances of % and _ in pattern-matching contexts where they would otherwise be interpreted as wildcard characters. See the description of the LIKE operator in Section 12.8.1, “String Comparison Functions and Operators”. If you use \% or \_ outside of pattern-matching contexts, they evaluate to the strings \% and \_, not to % and _. "

So as I understand it escaping % and _ characters should be escaped in LIKE queries, but not in exact searches because they already evaluate to strings \% and \_. I've attached a patch implementing this.
Comment 3 Lari Taskula 2022-09-14 15:04:17 UTC
Created attachment 140637 [details] [review]
Bug 31565: 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. Go to the cities page
8. Add two cities:
   - 'Cordoba %'
   - 'Buenos Aires _'
   - 'London \'
9. Use the column search on the name, alternating _, \ and % as the query
=> FAIL: Weird behavior
10. Apply this patch and reload
11. Repeat 9
=> SUCCESS: Filtering works correctly!
12. Repeat 3-5
13. Observe expected result
Comment 4 Lari Taskula 2022-09-14 15:31:47 UTC
I also noticed what seems to be a bit confusing behavior to me when I query cities endpoint with a "\" character as follows:

(My database holds a city named "TEST \")

1. LIKE, double escape \\\\
http://myinstance/api/v1/cities?=&_page=1&_per_page=20&q=[{"me.name":{"like":"%TEST \\\\%"}}]&_match=contains&_order_by=+me.city_id

Result: found

2. Exact, double escape \\\\
http://myinstance/api/v1/cities?_page=1&_per_page=20&q=[{"me.name":"TEST \\\\"}]&_match=contains&_order_by=+me.city_id

Result: not found, although same syntax as above?

3. Exact, single escape \\
http://myinstance/api/v1/cities?_page=1&_per_page=20&q=[{"me.name":"TEST \\"}]&_match=contains&_order_by=+me.city_id

Result: found

Should we also do the "\" escaping only for LIKE searches like _ and %? If yes then the current patch must be adjusted, as it escapes \ for both types of searches.
Comment 5 Katrin Fischer 2022-09-15 07:28:19 UTC
Hi Lari, be careful with the In Discussion as this makes it drop from the queues people look at the most. The status is normally only used when we have a disagreement to solve. Maybe reach out to people you want to have a look here.
Comment 6 Lari Taskula 2022-09-16 14:15:25 UTC
Created attachment 140709 [details] [review]
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
Comment 7 Lari Taskula 2022-09-16 14:15:57 UTC
(In reply to Katrin Fischer from comment #5)
> Hi Lari, be careful with the In Discussion as this makes it drop from the
> queues people look at the most. The status is normally only used when we
> have a disagreement to solve. Maybe reach out to people you want to have a
> look here.

Thanks! I proposed a fix and changed status to "Needs Signoff".
Comment 8 David Nind 2022-09-17 19:35:30 UTC
Notes from testing using koha-testing-docker.

For the first patch, Bug 31565: Fix datatables searching escape for exact search:
- Step 9: I didn't notice any weird behaviour when entering \, _, or % (note: I have no other locations in the table) - what should I be seeing?
- Search works as expected before and after the patches are applied.

For the second patch, Bug 31565: Escape datatables MySQL special characters only in LIKE queries:
- Step 1: Initially I tried to add this category using the user interface instead of SQL, and it came up with a warning: "Category code can only contain the following characters: letters, numbers, - and _.".
- I'm assuming there is a reason for this, although I have no idea why (I'm not a Koha developer).
- Everything works as per the test plan.

I haven't signed off yet because of the first patch.
Comment 9 Lari Taskula 2022-09-19 14:31:01 UTC
Created attachment 140753 [details] [review]
Bug 31565: 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!
Comment 10 Lari Taskula 2022-09-19 14:31:06 UTC
Created attachment 140754 [details] [review]
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
Comment 11 Lari Taskula 2022-09-19 14:41:12 UTC
(In reply to David Nind from comment #8)
> Notes from testing using koha-testing-docker.

Thanks for noticing the issue with the first patch. The test plan itself is wrong, I wasn't careful enough when copy-pasting necessary steps from Bug 30393 in order to make sure behavior introduced in dependent Bug hasn't been altered. The test plan in the first patch has now been adjusted.

As for the second patch, I noticed the same - it isn't possible adding a category with '\' character in categorycode. This is just for testing purposes in case some other Koha module using exact DataTable searches allows this character. I don't know if there currently are any, so I just decided to take this shortcut with the patron category search. With this test it should be now covered.
Comment 12 David Nind 2022-09-19 17:56:31 UTC
Created attachment 140755 [details] [review]
Bug 31565: 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>
Comment 13 David Nind 2022-09-19 17:56:36 UTC
Created attachment 140756 [details] [review]
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>
Comment 14 David Nind 2022-09-19 17:58:23 UTC
(In reply to Lari Taskula from comment #11)

Thanks for updating the patches and the explanations.

Now signed off.
Comment 15 Tomás Cohen Arazi 2022-09-19 18:06:08 UTC
Good job, I did (almost) the same on a different bug [1]. Will QA it.

[1] https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=140597
Comment 16 Tomás Cohen Arazi 2022-09-19 18:09:37 UTC
Created attachment 140757 [details] [review]
Bug 31565: 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>
Comment 17 Tomás Cohen Arazi 2022-09-19 18:09:42 UTC
Created attachment 140758 [details] [review]
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>
Comment 18 Tomás Cohen Arazi 2022-09-19 18:18:01 UTC
Pushed to master for 22.11.

Nice work everyone, thanks!
Comment 19 Mason James 2022-09-26 12:48:07 UTC
Created attachment 140978 [details] [review]
Bug 31565: Escape datatables MySQL special characters [FIX TESTS]

this patch removes _/& chars from test queries, as they are now removed via jscript

to test:

1/ push patch

2/ note jenkins tests now pass
Comment 20 Tomás Cohen Arazi 2022-09-26 13:34:58 UTC
Created attachment 140984 [details] [review]
Bug 31565: (QA follow-up) Remove support for _ as wildcard in -like queries

Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Comment 21 Katrin Fischer 2022-09-26 18:53:12 UTC
We just figured out that you can use _ in patron search, so a little sad to see that go. We might want to check the manual so it gets removed there or from other places.
Comment 22 Tomás Cohen Arazi 2022-09-26 19:17:12 UTC
(In reply to Katrin Fischer from comment #21)
> We just figured out that you can use _ in patron search, so a little sad to
> see that go. We might want to check the manual so it gets removed there or
> from other places.

You can search for underscore, it just won't be used as a wildcard on a 'like' query. If you want to use wildcards manually you can still use %, and I'm not sure it will work as you expected anyway.
Comment 23 Katrin Fischer 2022-09-26 19:38:56 UTC
Hm, we last tested this in 20.11 which still has the 'old' patron search and there it worked perfectly. Aren't all basic patron searches like searches because of the double-name treatment?
Comment 24 Katrin Fischer 2022-09-26 19:59:44 UTC
(In reply to Katrin Fischer from comment #23)
> Hm, we last tested this in 20.11 which still has the 'old' patron search and
> there it worked perfectly. Aren't all basic patron searches like searches
> because of the double-name treatment?

Examples that work in 20.11 and on master pre-patch:

Fischer
ischer
is__er
is%er (producing more results than __, example: Ausweisnummer

... but they still all work in master with the patch, so I might have misunderstood something? :)
Comment 25 Nick Clemens 2022-11-01 11:36:53 UTC
Created attachment 142861 [details] [review]
Bug 31565: [22.05.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>
Comment 26 Lucas Gass 2022-11-01 22:29:19 UTC
Backported to 22.05.x for upcoming 22.05.07 release
Comment 27 Arthur Suzuki 2022-11-14 17:07:30 UTC
unmet dependancies, can't apply on 21.11.x
Comment 28 Arthur Suzuki 2022-11-14 17:08:11 UTC
ups, wrong bug
Comment 29 Arthur Suzuki 2022-11-15 17:04:28 UTC
applied to 21.11 for 21.11.14
Comment 30 Victor Grousset/tuxayo 2022-11-26 23:34:32 UTC
Not backported to oldoldstable (21.05.x). Feel free to ask if it's needed.

Nothing to document it seems, marking resolved.