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
Created attachment 140636 [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
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.
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
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.
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.
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
(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".
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.
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!
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
(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.
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>
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>
(In reply to Lari Taskula from comment #11) Thanks for updating the patches and the explanations. Now signed off.
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
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>
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>
Pushed to master for 22.11. Nice work everyone, thanks!
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
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>
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.
(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.
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?
(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? :)
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>
Backported to 22.05.x for upcoming 22.05.07 release
unmet dependancies, can't apply on 21.11.x
ups, wrong bug
applied to 21.11 for 21.11.14
Not backported to oldoldstable (21.05.x). Feel free to ask if it's needed. Nothing to document it seems, marking resolved.