How can I safely and dynamically set column names in a query without using %s?-CodePudding

I am making a script for a coworker to be able to generate reports from a SQL Server database. I am using pymssql to manage the connection to the database.

My problem is that I want the person using the script to be able to specify which column they want to have returned, as well as the date range they need.

I am using this snippet of code but the column name don't work(from what I understand they get escaped and that is part of the problem) VS typing them out.

if len(columns) == 1:
    query = "SELECT %s FROM dbo.tblPayments WHERE %s < dbo.tblPayments.fldPayDate AND dbo.tblPayments.fldPayDate > %s;"
else:
    query = "SELECT "   ("%s, " * (len(columns) - 1))   "%s FROM dbo.tblPayments WHERE %s <= dbo.tblPayments.fldPayDate AND dbo.tblPayments.fldPayDate >= %s;"

I have seen people saying to use string concatenation with .format() instead but I don't want to insert security risks if I can avoid them. Is there something I am missing? What can I do to make it secure?

Thank you

CodePudding user response：

You can find all the columns of a certain table by using the sys.columns view. Get the list first and validate the user input in your application using this result. If the column is found you can use it to build the query string.

SELECT c.name
FROM sys.columns c
WHERE OBJECT_SCHEMA_NAME ( c.object_id) ='dbo'
AND OBJECT_NAME(c.object_id) = 'tblPayments'