CODEWITHSUNDEEP
pythonsql-serversql-injectionpymssql
Sillyreduction
Sillyreduction

Reputation: 147

Best way to prevent sql "injection" when using column as variable

I'm using pymssql to Update, Drop, and Add columns to a MS SQL server. the columns are sadly variables set by external sources such as reading the database, reading from another database. Now i'm trying to prevent "bad" sql to get through as i don't know exactly what the other database gives me. 

'ALTER TABLE tablenameA ADD  [' + columnname + '] varchar(25) NULL'
'ALTER TABLE tablenameA DROP COLUMN ['+columnname+']'
('UPDATE tablenameA SET [' + columnname + ']=%s WHERE id = 2', value)

Now i can't use a whitelist as i don't know what column names is to be added, my only other option i can think of is to use a blacklist, but i was wondering if there maybe exists a third option.

(The column names are gotten from a column in a table with type string)

Upvotes: 1

Views: 1520

Answers (1)

Martijn Pieters
Martijn Pieters

Reputation: 1123480

Transact-SQL has a function to turn a SQL string into a safe object name: QUOTENAME(). Use it around a bind parameter to have the database driver provide a properly quoted SQL object:

cursor.execute('SELECT QUOTENAME(%s)', (columnname,))
quoted_columnname, = next(cursor)

Now you can use that string in a new query:

query = 'ALTER TABLE tablenameA ADD {} varchar(25) NULL'.format(quoted_columnname)

etc. I used str.format() to insert the string here, rather than use string concatenation. Note that the [...] square brackets are no longer needed; QUOTENAME() took care of that.

Upvotes: 4

Related Questions