Hi @Changaco! I was considering picking up this issue as a way to contribute but it looks like these cases would be covered by B608:hardcoded_sql_expressions (see injection_sql.py). Two instances of hardcoded sql expressions were detected in this snippet of code:

from psycopg2 import sql

sql.SQL('insert into %s values (%s, %s)' % ('albatross', 1, 2)) # detected

moa = 'insert into %s values (%s, %s)' % ('ostrich', 3, 4)
sql.SQL(moa) # detected

sql.SQL("insert into {} values (%s, %s)") # OK

Is there another variation of this issue involving psycopg2.sql.SQL that bandit is still not detecting?

@wtkm11 bandit still doesn't detect calling the SQL constructor with a non-literal. For example in a function that receives a table name as an argument, using sql.SQL(table_name) would be unsafe, it should be sql.Identifier(table_name) instead.

from psycopg2 import sql

def count_rows(db, table):
    print(db.one(sql.SQL('select count(*) from {}').format(sql.Identifier(table))))  # safe
    print(db.one(sql.SQL('select count(*) from {}').format(sql.SQL(table))))  # unsafe

I wrote a plugin test (#608) to detect situations like this:

from psycopg2 import sql

table = 'users; drop table users; --'
sql.SQL('select * from {}').format(sql.SQL(table))

The non-literal being passed in sql.SQL(table) would be detected. However, I feel that in this case the confidence should be LOW rather than MEDIUM. What do you think?

I don't see why it should be low confidence, and I think it would make the test useless because in my experience low confidence alerts are numerous and have to be silenced by default.