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.