0

I am not sure why bandit is notifying the below as 'Detected possible formatted SQL query. Use parameterized queries instead.':

    conn.execute(f"DROP VIEW {view_name};")

Is there a way to parameterize the view_name? or concatenation is the only way forward to remove bandit flags here?

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
Lucky Ratnawat
  • 67
  • 7
  • @Progman: Thanks for your input. The programming language used here is Python (FastAPI), and the ORM is SQLAlchemy. – Lucky Ratnawat May 29 '23 at 11:36
  • You could try quoting the view name `conn.execute(f"""DROP VIEW "{view_name}";""")`. – snakecharmerb May 29 '23 at 11:54
  • @snakecharmerb: Thank you, I will try and let you know. But I doubt if it's going to throw syntax error because the view_name is already a String. – Lucky Ratnawat May 29 '23 at 12:10
  • 1
    You reference Bandit, is this the one you mean? https://github.com/PyCQA/bandit Note the Stack Overflow 'bandit' tag refers to some gem for Ruby on Rails (https://github.com/bmuller/bandit). – Bill Karwin May 29 '23 at 13:15

1 Answers1

2

In SQL, you can't parameterize identifiers, only values. A view name is an identifier. A quoted string constant or numeric constant is a value.

It's more common to use application variables as values in a formatted SQL statement, so it's not surprising that your Bandit detection tool suggests to use parameters. But you can't do that in this case.

When making SQL statements with dynamic identifiers, the best you can do is to make sure your view_name variable is safe from SQL injection threats. That is, it contains no untrusted content. Either set it explicitly in your code, allowing no external content to be used, or else use some pattern-matching code to ensure it is a valid view name and nothing else.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thank you for your suggestion, I will make sure that the variable is safe from injection. But, should we just suppress/dismiss such flags by Bandit608? – Lucky Ratnawat May 29 '23 at 13:57
  • 1
    I have never used Bandit, so I don't know what options exist. You should consult the documentation or open an issue with that project to ask what they suggest for cases like yours. In any case, their error message is pretty misleading. – Bill Karwin May 29 '23 at 13:58