topic Re: How to escape column comments when adding them programmatically in Get Started Discussions

How to escape column comments when adding them programmatically

JameDavi_51481 — Thu, 06 Jun 2024 17:08:56 GMT

I would like to add comments to all of our columns, programmatically. The only way I can find to do this is through SQL DDL - e.g. `alter table sometable alter column somecolumn comment 'some comment string`. However, I want to read this comment string from a YAML file, and protect against SQL injection. I cannot find a way to do this - if I use `alter table sometable alter column somecolumn comment :placeholder` I get a syntax error on the colon.

I know that e.g. special characters like single quotes work in comments, because I can edit and apply them manually - but it's not obvious how to do this with DDL when updating a table programmatically.

Re: How to escape column comments when adding them programmatically

brockb — Sat, 08 Jun 2024 20:24:16 GMT

Hi @JameDavi_51481 ,

I do not believe this is supported currently, please see this line in the Parameter Marker documentation:

> You must not reference a parameter marker in a DDL statement...
Ref: https://docs.databricks.com/en/sql/language-manual/sql-ref-parameter-marker.html#parameter-markers

Another consideration for you would be to manage the 'state' of your table (and table comments) using Terraform instead of a YAML file. Here's the documentation for the `databricks_sql_table` resource:
https://registry.terraform.io/providers/databricks/databricks/latest/docs/resources/sql_table

Alternatively, please do feel free to request this as a new feature at https://ideas.databricks.com

Hope it helps.