[Python] Not able to pass Column names as parameterised in ADD COLUMNS command in...

I'm developing a web application that interacts with Databricks using the Databricks SQL connector. The application allows users from different teams to add/rename/delete columns of existing tables. The column information is received as HTTP parameters. Snyk started raising SQL injection vulnerability since in the initial version we were passing parameters to the query through format string style. To resolve the issue, we tried using IDENTIFIER() on the schema/table name, but the same does not work on column names under ALTER, UPDATE command. You can refer to from this blog post https://community.databricks.com/t5/technical-blog/how-not-to-build-an-execute-immediate-demo/ba-p/82167

Here's the relevant part of my code:

def add_config(self, user_name, action, schema_name, table_name, field_names, field_datatypes):
alter_query = "ALTER TABLE IDENTIFIERsch || '.' || :tab) ADD COLUMNS ({})".format(columns_str)
cursor.execute(alter_query, {'sch': schema_name,'tab': table_name})
self.conn.commit()


I've also implemented a basic input check for safe identifiers:

def is_safe_identifier(self, identifier):
return re.match(r'^[a-zA-Z0-9_]+$', identifier) is not None


This is to ensure input Sanitisation for the http parameters.

How can I modify my code to achieve resolve the SQL Injection while also getting the feature being implemented (add columns dynamically)? Not able to think of any other method than format string.

Continue reading...