topic Re: 2.0 Extension: Query cannot use DELETE, DROP, CREATE, UPDATE and REPLACE commands in Extensions

2.0 Extension: Query cannot use DELETE, DROP, CREATE, UPDATE and REPLACE commands

sivart_89 — Fri, 07 Jun 2024 14:48:30 GMT

I'm working on polishing up the our custom db query extension. I've uploaded it with no errors but upon activating it I see the below. Note that the query is a SELECT statement, it just has an UPDATE in the as part of the select and has CREATE in the WHERE clause. Are we really not able to run something like this and expected to modify our queries to account for this?

[1522c0dc-6529-3dc0-a551-8f0e3d62f960][f_8980186878066372557][781770][out]17:44:34.021 [main] ERROR com.dynatrace.shared.core.AbstractDataSource -- Error in YAML or JSON configuration: Verification failed! Query cannot use DELETE, DROP, CREATE, UPDATE and REPLACE commands in a custom extension in 'SqlGroup{query='SELECT PRCS.NM, ROUND((SYSDATE - CAST (UPDATE.TS AS DATE)) * 24 * 60, 2) Value FROM CUST.OUTAGE.STAGE.ETL.RESTART WHERE PRCS.NM IN ('wf.Proactive.ETR.CW', 'wf.Proactive.ETR.MW', 'wf.Proactive.Restore.CW', 'wf.Proactive.Restore.MW') AND CREATE.USER.ID = 'ETL' AND UPDATE.USER.ID = 'ETL'

Re: 2.0 Extension: Query cannot use DELETE, DROP, CREATE, UPDATE and REPLACE commands

sivart_89 — Fri, 07 Jun 2024 14:49:52 GMT

@Mike_L Hi Mike, is this a limitation that will soon be fixed? While we do have some time, this is preventing us from migrating away from the 1.0 db query extension.

Re: 2.0 Extension: Query cannot use DELETE, DROP, CREATE, UPDATE and REPLACE commands

Mike_L — Fri, 07 Jun 2024 14:58:58 GMT

This limitation has been set by our security department to lower the risk of extensions causing data issues. There is no plan to change it.

Re: 2.0 Extension: Query cannot use DELETE, DROP, CREATE, UPDATE and REPLACE commands

sivart_89 — Fri, 07 Jun 2024 15:17:16 GMT

So if we are querying from a table column that has update in the name of it, are we expected to restructure our db table column names to get this working? I get the concern here but is there not a better way of preventing update statements from running in the extension rather than just checking for the word update anywhere in the query?

Re: 2.0 Extension: Query cannot use DELETE, DROP, CREATE, UPDATE and REPLACE commands

Mike_L — Fri, 07 Jun 2024 15:19:31 GMT

I agree completely. Let me raise it with the product manager and make sure that you get an official reply (or that it will be looked at).

Re: 2.0 Extension: Query cannot use DELETE, DROP, CREATE, UPDATE and REPLACE commands

sivart_89 — Fri, 07 Jun 2024 15:21:19 GMT

Thank you Mike for looking more into this. Eager to hear their response here.

Re: 2.0 Extension: Query cannot use DELETE, DROP, CREATE, UPDATE and REPLACE commands

michal_nalezin — Tue, 18 Jun 2024 11:18:08 GMT

Hi,

Indeed our security check may disallow SQL keywords as (CREATE/UPDATE/DROP etc) in the query.
You are completely right that in your case these are used in a safe way (as column names).

I'm assuming that you have already tried to escape these problematic names with double quotes ("CREATE.USER.ID")
Anyway we are looking into it - I've created an internal issue

Best regards
Michal Nalezinski

Re: 2.0 Extension: Query cannot use DELETE, DROP, CREATE, UPDATE and REPLACE commands

sivart_89 — Mon, 24 Jun 2024 17:37:21 GMT

I have not tried escaping the quotes. Please let me know if I need to provide anything. Being you noted you are looking into it anyways I have not done anything more with this. I am curious what comes from this being, let me know once you have more information.

Re: 2.0 Extension: Query cannot use DELETE, DROP, CREATE, UPDATE and REPLACE commands

sivart_89 — Mon, 08 Jul 2024 21:51:56 GMT

Hi @michal_nalezin. Just touching base here to see if there is any sort of update here.

Re: 2.0 Extension: Query cannot use DELETE, DROP, CREATE, UPDATE and REPLACE commands

sivart_89 — Fri, 26 Jul 2024 12:37:47 GMT

@michal_nalezin @Mike_L any update on this?

Re: 2.0 Extension: Query cannot use DELETE, DROP, CREATE, UPDATE and REPLACE commands

Mike_L — Fri, 26 Jul 2024 15:37:31 GMT

It's on Nalezinski's table, I'm sure he'll reply when he has more details.

Re: 2.0 Extension: Query cannot use DELETE, DROP, CREATE, UPDATE and REPLACE commands

michal_nalezin — Mon, 09 Sep 2024 08:38:46 GMT

We have improved our security mechanics, currently we do forbid certain SQL keywords to be used as standalone words. Hence if you have tables or columns that share the same naming as some of SQL keywords (f.e. create.update - table named "create" and column named "update"), you can now safely use the providing that you type them with leading or preceding non-whitespace (non-blankspace) characters (f.e. select * from mytable.update, or select create.user from myschema.create).

Potentially you could use aliases for such names, so managing your query would be easier.

The change shall be available with 1.299 version of the cluster.