SQL injection vulnerability due to "hand-crafted" SQL statements

[Simon-Will]

Description of the issue

The inserts into the SQLite database are done with an SQL statement that is created from the columns in the dataframe that is in turn read from the MaStR XML files:

open-MaStR/open_mastr/xml_download/utils_write_to_database.py

Line 598 in d2bcd43

insert_stmt = f"INSERT INTO {sql_table_name} ({','.join(column_list)}) VALUES ({','.join(['?' for _ in column_list])}) ON CONFLICT DO NOTHING"

A similar case is the point where missing columns are added:

open-MaStR/open_mastr/xml_download/utils_write_to_database.py

Lines 492 to 495 in d2bcd43

	alter_query = 'ALTER TABLE %s ADD "%s" VARCHAR NULL;' % (
	table_name,
	column_name,
	)

This is potentially dangerous. If an attacker can control the XML files, they can control the column names and execute arbitrary SQL here.

Steps to Reproduce

(I haven't produced a malicious XML file, but could do so if necessary.)

Ideas of solution

The best solution is probably creating an SQLAlchemy table object and using that to prepare the SQL statements.

Context and Environment

• Version used:
• Operating system:
• Environment setup and (python) version:

Workflow checklist

• I have checked the documentation and confirmed this issue is not already addressed there.
• I am aware of the workflow in CONTRIBUTING.md