0

Everything I have read says concatenation in SQL is a security flaw. Yet I wonder if I have a structure in Python like this:

columnNames = [ column_zero, column_one ]

if userInput == 0:

    columnIndex = 0

elif userInput == 1:

    columnIndex = 1

And then in SQL (psycopg2) I concatenate the query:

'UPDATE tableName 
SET ' + columnNames[ columnIndex  ] + ' = %s
WHERE id = %s', ( directUserInput, 1 )

Are there any security risks here?

Here is the often quoted red ink warning in bold and caps from psycopg docs:

Warning Never, never, NEVER use Python string concatenation (+) or string parameters interpolation (%) to pass variables to a SQL query string. Not even at gunpoint.

For double safety I implemented the new psycopg2.sql module they got:

sql.SQL( 'UPDATE tableName 
SET {} = %s
WHERE id = %s' ).format( sql.Identifier( columnNames[ columnIndex ] ) ), [ directUserInput, 1 ]

Yet I wonder, what's wrong with properly sanitized concatenated string in query? If the user puts in ';DROP DATABASE;' into either script, nothing should happen. Is there some bit-banging going on in python interpreter that makes concatenation dangerous?

3
  • Depends on the "...". Commented Jan 23, 2022 at 7:03
  • @GerardH.Pille I expanded ... but I'm assuming everything except for the concatenation part is safe Commented Jan 23, 2022 at 7:10
  • With double safety you've only halved the risk. Keep going! BTW, I don't want to know what could go wrong, since there is a method where it can't. It needs a little effort, that's true. Commented Jan 23, 2022 at 9:50

1 Answer 1

0

Yes, it's dangerous. Use pg8000 and prepared statements.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.