Error when calling cursor.execute with psycopg2 sql.SQL object

Using Django Using the ORM
1

Hi. I am trying to use sql.SQL from psycopg2 to build an sql string with placeholders and then use connection from django.db and call cursor.execute. But I am getting this error: TypeError: "object of type 'Composed' has no len()". Now if I create connection object directly from psycopg2, then it works. Is what I am doing using django.db.connection correct?

Not working code

from django.db import connection
from psycopg2 import sql

model_instances_to_update = []
for model_instance in models_queryset:    
    model_instances_to_update.append(
        (
            id,
            col_1_value,
            col_2_value,
            col_3_value,
            col_4_value
        )
    )

model_instance_update_query = sql.SQL(
    """
    UPDATE {table} AS model_table SET
    col_1 = model_table_new.col_1,
    col_2 = model_table_new.col_2,
    col_3 = model_table_new.col_3,
    col_4 = model_table_new.col_4
    FROM (VALUES {records_list_template}) AS model_table_new(id, col_1, col_2, col_3, col_4)
    WHERE model_table.id = model_table_new.id;
    """
).format(
    table=sql.Identifier("table_name"),
    records_list_template=sql.SQL(",").join(
        [sql.Placeholder()] * len(model_instances_to_update)
    ),
)

with connection.cursor() as cursor:
    cursor.execute(
      model_instance_update_query,
      model_instances_to_update,
  )

Working Code

from psycopg2 import sql

model_instances_to_update = []
for model_instance in models_queryset:    
    model_instances_to_update.append(
        (
            id,
            col_1_value,
            col_2_value,
            col_3_value,
            col_4_value
        )
    )

model_instance_update_query = sql.SQL(
    """
    UPDATE {table} AS model_table SET
    col_1 = model_table_new.col_1,
    col_2 = model_table_new.col_2,
    col_3 = model_table_new.col_3,
    col_4 = model_table_new.col_4
    FROM (VALUES {records_list_template}) AS model_table_new(id, col_1, col_2, col_3, col_4)
    WHERE model_table.id = model_table_new.id;
    """
).format(
    table=sql.Identifier("table_name"),
    records_list_template=sql.SQL(",").join(
        [sql.Placeholder()] * len(model_instances_to_update)
    ),
)

database = settings.DATABASES["default"]
pg_connection_dict = {
    "dbname": database["NAME"],
    "user": database["USER"],
    "password": database["PASSWORD"],
    "port": database["PORT"],
    "host": database["HOST"],
}

connection = psycopg2.connect(**pg_connection_dict)
cursor = connection.cursor()
cursor.execute(
      model_instance_update_query,
      model_instances_to_update,
  )
connection.close()

Is this the intended behaviour of django.db.connection or am I doing something wrong here?

2

Yes. You do not directly use the psycopg2.sql.SQL object with a Django connection.

See Performing raw SQL queries | Django documentation | Django for how to create and execute raw SQL using the connection object. (You don’t create an sql.SQL object to pass to cursor.execute, you pass the SQL string itself.)

3

Got it. So django internally prevents sql injection if placeholders are not surrounded by quotes?

4

Correct. It builds the sql as “sql with parameters” and not as a complete sql query string.

5

Ok. In fact I was writing and sending sql strings directly with placeholders and passed values in list just as it is in documentation. But I ran static code checker on my django application and it showed this as security issue and it stopped showing that only when I passed list to params using AsIs from psycopg2. Basically it was as below:

from django.db import connection
from psycopg2.extensions import AsIs

params_dict = {"list_of_values": AsIs(model_instances_to_update)}
cursor.execute(sql_string, params)

Now sql.SQL is the recommended way as per psycopg2 instead of AsIs. So thought of executing it like this. So I think I have to make some config changes in static code checker to silence these warnings.