3 ms·

This is absolutely good advice, but people giving it rarely acknowledge that it isn't always possible. A particular irritant is that most (all?) db APIs make i

by paol 10y ago

This is absolutely good advice, but people giving it rarely acknowledge that it isn't always possible. A particular irritant is that most (all?) db APIs make it impossible to use parametrized queries with IN clauses. I find myself having to implement string escaping functions sooner or later in every project, because of stuff like that.

tmd83 10y ago
This is such an irritant. Everyone supports variable size IN clause in the form of a sub-select AFAIK. You should be able to abstract the parsing and have an api to address that. Yet for years its missing and you end up all sorts of garbage under-performing code. Our current solution is to use of temporary table because other solutions (including dynamically adding parameter in query) makes them unique queries and crashes performance.
icebraining 10y ago
At least in Postgres, you can use ANY and pass an array of values. From what I've read, the generated query plan is equivalent.
- masklinn 10y ago
  > From what I've read, the generated query plan is equivalent. I believe at one point IN (values) was just sugar for = ANY(ARRAY(values)), don't know if that's still the case. Still requires that your DB adapter supports array parameters though.
  - snuxoll 10y ago
    Alternatively, you can use a temp table and a join. Not ideal, but still safe from injection and should have roughly the same overhead in the database.
continuational 10y ago
select * from Person where email = any(?)
batbomb 10y ago
This isn't the case with oracle, MySQL, or hsqldb, at least when using jdbc. I believe it's also not true for SQLite and postgres.
masklinn 10y ago
> A particular irritant is that most (all?) db APIs make it impossible to use parametrized queries with IN clauses Psycopg2 has a generic adaptation of iterables to a form suitable for IN, but it's hand-rolled: https://github.com/psycopg/psycopg2/blob/732ea90a4ff85f6d0cca8df6e3dc9d18bfd1c8b4/lib/extensions.py#L121 https://github.com/psycopg/psycopg2/blob/732ea90a4ff85f6d0cc... Real convenient though.
tbrownaw 10y ago
That's when you combine parameterized queries and concatenated SQL: sql = '... col_name IN (' foreach(var arg in args) if (!first) sql.append(', ') sql.append('?') params.add(arg) sql.append(')')

by paol 10y ago

tmd83 10y ago

This is such an irritant. Everyone supports variable size IN clause in the form of a sub-select AFAIK. You should be able to abstract the parsing and have an api to address that. Yet for years its missing and you end up all sorts of garbage under-performing code. Our current solution is to use of temporary table because other solutions (including dynamically adding parameter in query) makes them unique queries and crashes performance.

icebraining 10y ago

At least in Postgres, you can use ANY and pass an array of values. From what I've read, the generated query plan is equivalent.

masklinn 10y ago
> From what I've read, the generated query plan is equivalent. I believe at one point IN (values) was just sugar for = ANY(ARRAY(values)), don't know if that's still the case. Still requires that your DB adapter supports array parameters though.
- snuxoll 10y ago
  Alternatively, you can use a temp table and a join. Not ideal, but still safe from injection and should have roughly the same overhead in the database.

continuational 10y ago

select * from Person where email = any(?)

batbomb 10y ago

This isn't the case with oracle, MySQL, or hsqldb, at least when using jdbc. I believe it's also not true for SQLite and postgres.

masklinn 10y ago

> A particular irritant is that most (all?) db APIs make it impossible to use parametrized queries with IN clauses Psycopg2 has a generic adaptation of iterables to a form suitable for IN, but it's hand-rolled: https://github.com/psycopg/psycopg2/blob/732ea90a4ff85f6d0cca8df6e3dc9d18bfd1c8b4/lib/extensions.py#L121 https://github.com/psycopg/psycopg2/blob/732ea90a4ff85f6d0cc... Real convenient though.

tbrownaw 10y ago

That's when you combine parameterized queries and concatenated SQL: sql = '... col_name IN (' foreach(var arg in args) if (!first) sql.append(', ') sql.append('?') params.add(arg) sql.append(')')