Page 1 of 1
Can't search for "Carole"
Posted: Tue Oct 15, 2024 9:48 am
by graham_mueller
A weird, random thing we ran into today. A user searched for a patient named Carole...
I thought surely I must have made a mistake somewhere and that it wasn't that, but searching "Caole" (or something real, like John) works fine.
This is from a call to the ShortQuery endpoint, the query is
Code: Select all
SELECT COUNT(*) as count
FROM patient
WHERE PatStatus NOT IN (3, 4)
AND (
CONCAT(TRIM(FName), ' ', TRIM(LName)) LIKE '%carole%'
OR Email LIKE '%carole%'
OR HmPhone LIKE '%carole%'
OR WkPhone LIKE '%carole%'
OR WirelessPhone LIKE '%carole%'
)
Re: Can't search for "Carole"
Posted: Tue Oct 15, 2024 10:43 am
by SLeon
Good morning. The Open Dental API screens all payload queries in Queries POST and Queries PUT ShortQuery request to be read-only (or on temp tables). It also screens out SQL actions that are inappropriate to perform via the API, such as the SLEEP command.
The reserved word "ROLE" is used to view, create, or revoke permissions for mysql users. Naturally, this is inappropriate to perform via the API. The word "ROLE" is used with the keywords "CREATE", "DROP", "SET", or "RESET". Because those keywords are allowed on temp tables, we added "ROLE" to our list of forbidden words. Additionally, it is designed this way because it can't effectively screen "CREATE ROLE" or "CREATE /*comment*/ ROLE" without looking for just "ROLE".
Re: Can't search for "Carole"
Posted: Tue Oct 15, 2024 11:06 am
by graham_mueller
I understand the limitations of your query API, but if you're going to search for the text "role," could you at least make it "freestanding," prefixed by a non-word character or something sane to prevent this sort of failure? This behavior is different for (eg) SET, searching for a patient named Seth or Rosette does not have the same problem.
Re: Can't search for "Carole"
Posted: Tue Oct 15, 2024 11:14 am
by graham_mueller
Heck, I can run that query with just the word CREATE, DROP, or SET and it works. It only fails with ROLE.
Re: Can't search for "Carole"
Posted: Tue Oct 15, 2024 11:40 am
by SLeon
I realize failed to mention in my last post that I intended too further look into our screening logic to see what we can (safely) do. I will update this thread when I do. However, it is lower priority than API Feature Requests.
Re: Can't search for "Carole"
Posted: Mon Oct 21, 2024 7:06 am
by jordansparks
Our filter needs to be better. It should use regular expressions to ignore those keywords if present inside of quotes.
Re: Can't search for "Carole"
Posted: Mon Jan 20, 2025 11:04 am
by graham_mueller
An additional random word check that's failing-
When searching for, eg, John Oslow.
Re: Can't search for "Carole"
Posted: Mon Jan 20, 2025 11:11 am
by justine
graham_mueller wrote: ↑Mon Jan 20, 2025 11:04 am
An additional random word check that's failing-
When searching for, eg, John Oslow.
Thanks for the heads up, this ShortQuery endpoint enhancement is coming up next. I have added 'John Oslow' to the job/unit test requirements.
Thanks!
Re: Can't search for "Carole"
Posted: Fri Feb 28, 2025 3:03 pm
by RyanH
Hello graham_mueller,
We have completed this feature request, and the implementation is live on our beta. The query screening algorithm has been enhanced to not falsely flag reserved or forbidden keywords if they are contained within other words. Your examples of “ROLE” in “Carole” or “SLOW” in “John Oslow” will now pass the screening.