0

I'm working on postgresql and doing security check about SQL injection. I'm refactoring my code to avoid SQL injection.

I want to do the average of a column named "television" Here my code :

with connection.cursor() as cursor:
   sql.append(
        f"""SELECT COUNT(*),
              AVG(%s)
              FROM dataset"""
    )
    values.append(target)

    cursor.execute(sql, values)

If i print values:

['television']

But i got error :

LINE 3:      AVG('television'),

It's look like simple quote are problem here, i need to have AVG("television"). There is a way to force simple quote to double quote ?

I tried to parameter it before like that :

new_target = f'"{target}"'
with connection.cursor() as cursor:
    sql.append(
        f"""SELECT COUNT(*),
              AVG(%s)
              FROM dataset"""
    )
    values.append(new_target)

    cursor.execute(sql, values)

But got almost same error:

AVG('"television"'),
Valentin Garreau
  • 963
  • 1
  • 10
  • 32
  • 1
    We can't parameterize the column names in the SQL string. Either you have to construct the SQL string by concatenation or keep a placeholder and replace it with the table name. I prefer/follow the 2nd one. Checkout: https://stackoverflow.com/questions/11312737/can-i-parameterize-the-table-name-in-a-prepared-statement – Manonandan S K Jun 13 '23 at 09:38
  • Near-duplicate, if you’re using psycopg2 (it’s about a table name, not a column name, but the same solutions apply): https://stackoverflow.com/questions/13793399/passing-table-name-as-a-parameter-in-psycopg2/42947632#42947632 – Ture Pålsson Jun 13 '23 at 10:41

0 Answers0