Django raw queries and Postgres declare variable

Question

I have a file with many raw sql queries, that uses text substitution i.e

select * from table1 where date_col between '%%from_date%%' and '%%to_date%%'

these %%date_from%% and %%date_to%% are then replaced by values using python string replace function. this works fine and the queries work. however it is not ideal as it can be open to sql injection. (and no I can't use models or anything beside the raw sql) to make matters worse I can't use %s and parametrise the queries because the variables change order and %s works by order (as far as I can tell)

I thought about using declare in order to have my variables at the start of the select block i.e

sql - 
declare from_date date:= %s
declare to_date date:= %s
select * from table1 where date_col between to_date and from_date

python - 
with with connection.cursor() as cursor:
  cursor.execute(query, [from_date, to_date'])

but this gives out

django.db.utils.ProgrammingError: syntax error at or near "date"
LINE 1: DECLARE from_date date := '2022-01-01'::date DECLARE to_date...
                          ^

I don't know what I'm doing wrong and I'll be glad for some help. as far as I can see I got the syntax right, maybe django raw queries only support select? is there another way to make it work with parameterization ? appreciate the help

Mike Organek · Accepted Answer · 2022-07-10 14:25:52Z

1

Try making a top CTE to hold your variables, instead:

with invars as (
  select (%s)::date as from_date, (%s)::date as to_date
)
select *
  from invars i
       join table1 t 
         on t.date_col between i.to_date and i.from_date;

If the problem is that sometimes from_date and to_date are mixed up, then you can use BETWEEN SYMMETRIC.

If you need to use those variables in a query or another CTE that does not have anything to join to the invars, then use cross join to make invar's columns available.

answered Jul 10, 2022 at 14:25

Mike Organek

12.6k3 gold badges15 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Cptmaxon Over a year ago

from_date and to_date are examples, there's about 10 variables spread around the queries :( although your workaround might work, I'm checking :D

Mike Organek Over a year ago

@Cptmaxon That invars CTE trick works really well in my experience--especially if you have variables that need to be used in multiple places in a large query. Line them up like that, and it is easy to see whether your arguments tuple matches what the query expects.

Collectives™ on Stack Overflow

Django raw queries and Postgres declare variable

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related