6 ms·
Never use WHERE 1=1. It is both a security risk and a performance risk to run dynamic ad-hoc queries.
by AtNightWeCode 2y ago
Never use WHERE 1=1. It is both a security risk and a performance risk to run dynamic ad-hoc queries.
- regexman1 2y agoI'll add this as a caveat. I'm an analyst so my SQL isn't really exposed to anyone other than myself and so I wasn't aware of this, thanks for flagging.
- halayli 2y agoA random person claims adding 1=1 is a security risk and you are going to add it as caveat without verifying if the claim is true nor knowing why? That's how misinformation spreads around. OP doesn't know what they are talking about because adding 1=1 is not a security risk. 1=1 is related to sql injections where a malicious attacker injects 'OR 1=1' into the end of the where clause to disable the where clause completely. OP probably saw '1=1' and threw that into the comment.
- regexman1 2y agoFair point!
- AtNightWeCode 2y agoRead my other comments. I worked with SQL on and off since the last century. It has nothing to do with your poor assumptions.
- the_gorilla 2y agoDuration of working with SQL doesn't matter. The better SQL programmers don't do it specifically, and have experience in real languages that they bring over to database queries.
- AtNightWeCode 2y agoNot sure I get this. But I think it does matter since you understand why people do it to begin with. I worked on two enterprise solutions over the last couple of years that have this exact problem. That people are using WHERE 1=1 and then add random "AND something=something" that completely trashes the performance of the db. Also, it does not matter as much on-prem. But in cloud envs it does. Since you can't really spike CPU and mem the same way as on-prem. The reason I pointed out this specific issue is just that I thought it was the worsed of many poor tips. ChatGPT can give better tips.
- the_gorilla 2y agoIf the query planner can't optimize out "IF TRUE" I don't know what to say. Is there something deeper happening or is this just gross incompetence?
- AtNightWeCode 2y agoJesus. The problem is not WHERE 1=1. It is WHY people do it. People do it because they then in Python, JS or whatever can easily add conditions. Like QUERY + "AND x='blabla'". There is the problem. Every time you create a unique SQL statement the query will need a new query plan in most SQL engines. Some will cache parts of the query. And you could use parameters along with this paradigm but if you are this bad at SQL I doubt it. It is kinda funny that op backpedal on this cause to me the whole site is amateurish. I just pointed out what I thought was the worst part. It is likely that it is generated by AI. Either way the post is terrible.
- regexman1 2y agoI'm an analyst so I literally only query for analytical purposes. I don't have any SQL code embedded in an application so the 1=1 is purely for tweaking/testing a query more quickly. I certainly didn't use AI, all these tips/tricks are from work experience and reading Snowflake documentation and the like, but I guess I can't convince you either way. Regardless I appreciate the feedback!
- paperplatter 2y ago1=1 is not a security risk
- Gunax 2y agoWhat is a dynamic, adhoc query? Why does adding 1=1 support that?
- abrookewood 2y agoI'm wondering that as well. I don't get that suggestion at all.
- egormakarov 2y agoLets 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.
- freilanzer 2y agoPhp 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.
- egormakarov 2y agoThis will produce broken SQL on empty clauses list. Very strange.
- freilanzer 2y agoYou'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.
- paperplatter 2y agoThe easiest fix for this is the "WHERE 1=1" or "WHERE true"
- adamzochowski 2y agoCan you expand on this? How is having WHERE 1=1 AND ...[usual-where-clause]... A performance and security compared to doing WHERE ...[usual-where-clause]...
- AtNightWeCode 2y agoIf you use it in the same way you use trailing commas. Fair. But the site says to make it easier to add dynamic conditions. Which is a terrible idea in maybe not all but many SQL engines.
- jcz_nz 2y agoCan you elaborate on security issues here?
- thestepafter 2y agoI think that it means the reason for doing where 1 = 1 is sometimes to allow for easy insertion of dynamic queries which can be a security and performance issue. The actual usage of where 1 = 1 doesn't cause the security or performance issue.
- AtNightWeCode 2y agoWhich is exactly what the site says. To insert dynamic conditions. I know that you can use 1=1 for the same reasons as trailing commas. But kinda obvious that this is not the case here.
- regexman1 2y agoJust to be clear I'm using it for the same reason as trailing commas. If I'm inspecting a dataset I use WHERE 1=1 so I can add and remove conditions more easily. I realise the confusion is in my wording of dynamic - I might amend the README.md to clarify. Thanks for the feedback!
- ricardo81 2y agoPresumably you are thinking about queries in code that add WHERE clauses dynamically that aren't escaped correctly- which doesn't have to be the case. 1 = 1 is at least handy for simply joining a variadic amount of other clauses with ' AND ' rather than counting if there's any to add at all.
- AtNightWeCode 2y agoYes. "Use a dummy value in the WHERE clause so you can dynamically add and remove conditions with ease:" I don't know how to read this in another way.
- regexman1 2y agoI've amended the README.md to explain what I meant. My error was in using the word dynamic!
- DH61AG 2y agoThis doesn't make any sense at all.