Can't search for "Carole"

[graham_mueller]

A weird, random thing we ran into today. A user searched for a patient named Carole...

Code: Select all

Query cannot contain "ROLE"

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%'
      )

[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".

[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.

[graham_mueller]

Heck, I can run that query with just the word CREATE, DROP, or SET and it works. It only fails with ROLE.

[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.

[jordansparks]

Our filter needs to be better. It should use regular expressions to ignore those keywords if present inside of quotes.

[graham_mueller]

An additional random word check that's failing-

Code: Select all

Query cannot contain "SLOW".

When searching for, eg, John Oslow.

[justine]

An additional random word check that's failing-

Code: Select all

Query cannot contain "SLOW".

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!

[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.

[graham_mueller]

Hi all,

As of Februrary, this was listed as in beta, I assume at some point after it was promoted. We are still encountering the issue, though. Search was again, for Carole.

ETA: This log is from 2025-07-26T00:48:28.709790318Z

[justine]

Hi all,

As of Februrary, this was listed as in beta, I assume at some point after it was promoted. We are still encountering the issue, though. Search was again, for Carole.

ETA: This log is from 2025-07-26T00:48:28.709790318Z

Good morning graham_mueller,

Enhanced queries screening was implemented in version 24.4.23. What version are you on? What does the exact query you are running look like?

Thanks!

[graham_mueller]

Ah, I had thought this filtering was done in the OpenDental central API layer. I am not sure what that clinic is using, but presumably lower. I guess we'll have to add a workaround for any clients that are below that.