Is it safe to concatenate SQL statement if user input is not used directly?



  • 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 https://www.psycopg.org/docs/usage.html#the-problem-with-the-query-parameters :

    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?



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


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2