Bug 30393

Summary: datatables wrapper should handle searching for %, _ and \
Product: Koha Reporter: Tomás Cohen Arazi <tomascohen>
Component: Architecture, internals, and plumbingAssignee: Tomás Cohen Arazi <tomascohen>
Status: Pushed to stable --- QA Contact: Testopia <testopia>
Severity: normal    
Priority: P5 - low CC: kyle, martin.renvoize, severine.queune
Version: master   
Hardware: All   
OS: All   
See Also: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30063
Change sponsored?: --- Patch complexity: ---
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
22.05.00,21.11.05
Bug Depends on:    
Bug Blocks: 30055, 30394    
Attachments: Bug 30393: Make datatables wrapper handle searching for % and _
Bug 30393: Make datatables wrapper handle searching for %, _, \
Bug 30393: Make datatables wrapper handle searching for %, _, \
Bug 30393: Make datatables wrapper handle searching for %, _, \
Bug 30393: Make datatables wrapper handle searching for %, _, \

Description Tomás Cohen Arazi 2022-03-29 10:55:32 UTC
This characters have special meanings in LIKE queries in MariaDB/MySQL and should be escaped correctly. [1]

[1] https://dev.mysql.com/doc/refman/8.0/en/string-literals.html
Comment 1 Tomás Cohen Arazi 2022-03-30 09:30:57 UTC
Created attachment 132544 [details] [review]
Bug 30393: Make datatables wrapper handle searching for % and _

This patch makes searching for '%' and '_' work by escaping those
symbols in the query before sending them to the API.

To test:
1. Go to the cities page
2. Add two cities:
   - 'Cordoba %'
   - 'Buenos Aires _'
3. Use the column search on the name, alternating _ and % as the query
=> FAIL: Weird behavior
4. Apply this patch and reload
5. Repeat 3
=> SUCCESS: Filtering works correctly!
6. Sign off :-D

Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Comment 2 Tomás Cohen Arazi 2022-03-30 10:13:08 UTC
Created attachment 132551 [details] [review]
Bug 30393: Make datatables wrapper handle searching for %, _, \

This patch makes searching for '%','_' and '\' work by escaping those
symbols in the query before sending them to the API.

To test:
1. Go to the cities page
2. Add two cities:
   - 'Cordoba %'
   - 'Buenos Aires _'
   - 'London \'
3. Use the column search on the name, alternating _, \ and % as the query
=> FAIL: Weird behavior
4. Apply this patch and reload
5. Repeat 3
=> SUCCESS: Filtering works correctly!
6. Sign off :-D

Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Comment 3 Séverine Queune 2022-03-30 10:29:55 UTC
Created attachment 132553 [details] [review]
Bug 30393: Make datatables wrapper handle searching for %, _, \

This patch makes searching for '%','_' and '\' work by escaping those
symbols in the query before sending them to the API.

To test:
1. Go to the cities page
2. Add two cities:
   - 'Cordoba %'
   - 'Buenos Aires _'
   - 'London \'
3. Use the column search on the name, alternating _, \ and % as the query
=> FAIL: Weird behavior
4. Apply this patch and reload
5. Repeat 3
=> SUCCESS: Filtering works correctly!
6. Sign off :-D

Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Signed-off-by: Séverine Queune <severine.queune@bulac.fr>
Comment 4 Séverine Queune 2022-03-30 10:31:10 UTC
Works nice, this is an happy sign-off ! :D
Comment 5 Jonathan Druart 2022-03-30 11:52:24 UTC
Created attachment 132561 [details] [review]
Bug 30393: Make datatables wrapper handle searching for %, _, \

This patch makes searching for '%','_' and '\' work by escaping those
symbols in the query before sending them to the API.

To test:
1. Go to the cities page
2. Add two cities:
   - 'Cordoba %'
   - 'Buenos Aires _'
   - 'London \'
3. Use the column search on the name, alternating _, \ and % as the query
=> FAIL: Weird behavior
4. Apply this patch and reload
5. Repeat 3
=> SUCCESS: Filtering works correctly!
6. Sign off :-D

Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Signed-off-by: Séverine Queune <severine.queune@bulac.fr>

Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
Comment 6 Martin Renvoize 2022-03-30 12:37:57 UTC
Created attachment 132597 [details] [review]
Bug 30393: Make datatables wrapper handle searching for %, _, \

This patch makes searching for '%','_' and '\' work by escaping those
symbols in the query before sending them to the API.

To test:
1. Go to the cities page
2. Add two cities:
   - 'Cordoba %'
   - 'Buenos Aires _'
   - 'London \'
3. Use the column search on the name, alternating _, \ and % as the query
=> FAIL: Weird behavior
4. Apply this patch and reload
5. Repeat 3
=> SUCCESS: Filtering works correctly!
6. Sign off :-D

Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Signed-off-by: Séverine Queune <severine.queune@bulac.fr>
Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Comment 7 Martin Renvoize 2022-03-30 12:38:29 UTC
Just adding another QA stamp as I was also working through testing this one.
Comment 8 Fridolin Somers 2022-04-04 07:49:23 UTC
Pushed to master for 22.05, thanks to everybody involved 🦄
Comment 9 Kyle M Hall 2022-04-08 12:25:57 UTC
Pushed to 21.11.x for 21.11.05