Handling nulls in parameterized query postgres

Question

I've got a sql query where I'm using the sqlalchemy text function because I have a rather long and complicated query. I'd like to keep this discussion to bits about how I can handle it using compositional sql, unless its for some reason unsolvable that way.

right now, one of my subqueries uses a syntax like this

select * 
from table_a
join table_b on table_a.b_id = table_b.id
join table_c on table_c.b_id = table_b.id
where table_a.timestamp BETWEEN to_date(:time_range_start,'yyyy-mm-dd HH24:MI:SS'::text) and to_date(:time_range_end,'yyyy-mm-dd HH24:MI:SS'::text)
AND table_a.id = ANY(:a_ids)
AND table_a.attribute_id = ANY(:attribute_ids)
AND table_b.id = ANY(:b_ids)

So what I need to understand is either 1. A default value to give to the ANY() function so that it essentially makes it not filter 2. A way to conditionally format in that portion of the query into the text

I could potentially just use a python string format before calling the text() function on it?

robinsax · Accepted Answer · 2018-12-21 23:43:36Z

1

Using a direct string format is a bad idea, that's how injection happens.

Perhaps add a boolean value to the ANY conditions such that you can force them to be TRUE (and therefore not filter). For example.

...
AND (:force_a_id OR table_a.id = ANY(:a_ids))
AND (:force_a_attr OR table_a.attribute_id = ANY(:attribute_ids))
...

However, I would really recommend using SQLAlchemy's query API, this is well within the set of problems it solves.

answered Dec 21, 2018 at 23:43

robinsax

1,2207 silver badges9 bronze badges

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

6 Comments

Ilja Everilä Over a year ago

Composing a query from static bits of SQL is not usually a vector for injection, though a bit unwieldy.

robinsax Over a year ago

True, but there's lots of tricky stuff the bad guys can do to you (esp. in a language with loose types) if you're not careful. For example

Ilja Everilä Over a year ago

Note the word static. You can safely piece together SQL as long as it is not built from data provided from the outside (such as table and column names). It is what SQLAlchemy does under the hood. I'm not saying it's a nice approach, though, but sometimes necessary.

Ilja Everilä Over a year ago

But you are spot on in that OPs problem is what SQLA query building capabilities are for.

Joe Sadaka Over a year ago

the sample SQL here would be fine in SQLA builder, but the full query I had was significantly complicated and I couldn't figure out how ot put it in the builder effectively. I have been using the sqlalchemy text() function instead because I thought that it protects against injection somewhat as well.

|

Collectives™ on Stack Overflow

Handling nulls in parameterized query postgres

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related