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

[sivart_89]

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'

[sivart_89]

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

[Mike_L]

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.

[sivart_89]

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?

[Mike_L]

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

[sivart_89]

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

[michal_nalezin]

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

[sivart_89]

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.

[sivart_89]

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

[sivart_89]

@michal_nalezin @Mike_L  any update on this?

[Mike_L]

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

[michal_nalezin · Accepted solution]

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.