CODEWITHSUNDEEP
pythondjangopostgresql
Cptmaxon
Cptmaxon

Reputation: 525

Django raw queries and Postgres declare variable

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

Upvotes: 2

Views: 351

Answers (1)

Mike Organek
Mike Organek

Reputation: 12494

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.

Upvotes: 1

Related Questions