Hacker News Reader

Comment by Gunax | Hacker News Reader

What is a dynamic, adhoc query? Why does adding 1=1 support that?

Lets say its 2001 and you are writing some hot e-commerce stuff in plain php. You want to filter data depending on multiple fields in the submitted form. If some field is there, you add one more "AND" clause to the "WHERE", like this: if (isset($_POST['product'])) { $query .= "AND product = " . $_POST['product']; }. So in order not to check every time if the added clause is the first one you start with "WHERE 1=1 ", as "WHERE AND ..." would not work.

Php has nothing like this?

In [1]: "... WHERE " + " AND ".join(str(i) for i in range(4))

Out[1]: '... WHERE 0 AND 1 AND 2 AND 3'

Very strange.

This will produce broken SQL on empty clauses list. Very strange.

You're quite right, but this is easily fixed. That doesn't change my question, since something like this is much easier that the other logic.

The easiest fix for this is the "WHERE 1=1" or "WHERE true"

I get how this isn't good. But how else would you handle multi-field filtering, keep all the ANDs and use (product_id = $1 OR $1 IS NULL) so the unset filters are no-op? That's ok as long as the query planner is smart enough.

In this case. A query that you build by adding different strings. 1=1 is for adding AND statements to the WHERE clause dynamically. In your code. I never seen it used for anything else. Adhoc is just the practice of running raw SQL queries.

So you end up with things like this.

"SELECT * FROM Music WHERE 1=1" + "AND category='rock'"

The risk is now that you by mistake allow for SQL-injections but also every genre will generate a different query plan. Depending on what SQL engine you use this may hurt performance.

And one would think that this is a thing of the past. But it is not.

I'm wondering that as well. I don't get that suggestion at all.

Reply on news.ycombinator.com