Fix Bandit SQL injection issue in pandas.read_sql()

Question

I am working on a project which has a lot of queries that are being run in python. When I performed the bandit check, I saw the issue -

Test results:
>> Issue: [B608:hardcoded_sql_expressions] Possible SQL injection vector through string-based query construction.
   Severity: Medium   Confidence: Low
   Location: main.py:160
   More Info: https://bandit.readthedocs.io/en/latest/plugins/b608_hardcoded_sql_expressions.html

This issue was present for all the places I made use of the raw SQL queries.

How can I modify the string interpolation to fix the issue ?

Sample Code -

import pandas as pd

table_name = "orders"

df = pd.read_sql(sql=f'''
    SELECT * FROM {table_name};
''')

I have tried the following ways but it did not work.

import pandas as pd

table_name = "orders"

# Try 1
df = pd.read_sql(sql='''
    SELECT * FROM {};
'''.format(table_name))

# Try 2
df = pd.read_sql(sql='''
    SELECT * FROM %s;
''' %(table_name,))

I followed this blog but I may not be able to use the cursor object from psycopg2. So, need something that can help with string formatting and interpolation as I will be able to use it within the pandas library.

Answer 1

If you convert cur.fetchall object to dataframe then it will work

cur.execute("select instrument, price, date from my_prices")
df = DataFrame(cur.fetchall(), columns=['instrument', 'price', 'date'])