91–100 of 168 posts
Never use WHERE 1=1. It is both a security risk and a performance risk to run dynamic ad-hoc queries.
Presumably 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.
Earlier quoted context omitted.
I don't understand the point at all. If you need to add some condition later on, why not just add it then? What benefit is there to just marking out the spot where you might add the condition at some point in the future?
For their one here it's just the ability to rapidly comment/uncomment conditions in a query editor while exploring the data or debugging the query, and not having to worry about the leading AND or OR. I've also seen it in code with iterative adds, for example: for crit in criteria: sql += " AND " + crit No needed to add a sentinel or other logic to skip the first AND. I saw it a lot before people got used to " AND ".…
Earlier quoted context omitted.
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 ",…
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.
Earlier quoted context omitted.
If you’re still using a diff tool that can’t do sub-line diffs it’s time to join the 20’s. I haven’t been forced to use one of those in over ten years.
> If you’re still using a diff tool that can’t do sub-line diffs it’s time to join the 20’s. I think you failed to understand what I wrote. Leading comma ensures one line diffs, but trailing comma forces two-line diffs when you add a trailing argument. With trailing comma, you need to touch the last line to add a comma, and then add the new argument in the line below. We are not discussing bundling all arguments in a…
It does not. It just moves the edge case to a different position: trailing comma has the "issue" when adding an argument to the end of the list while leading comma has it when adding an argument to the beginning.
Also, as pointed out by the other commenter, any decent modern diff tool will make it obvious that the change to the existing line is just the addition of a comma, which makes the difference basically moot.
Earlier quoted context omitted.
Can you elaborate on security issues here?
I 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.
Never use WHERE 1=1. It is both a security risk and a performance risk to run dynamic ad-hoc queries.
Can 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]...
Earlier quoted context omitted.
I'm not the biggest fan of how the first two conventions look, but they are real conventions used by real SQL people. And I can understand why they exist. I've seen them enough to not be bothered by them any more.
Yeah, unfortunately you're right that they are real conventions. Quite common too. I also _understand_ why they exist. It's simple: It makes code marginally easier to write. But writing confusing, unintuitive and honestly plain ugly code. Just so you can save a second after clicking run and the compiler tells you the mistake is a bad reason.
I've done plenty of SQL, and I've regularly run in to the "fuck about with fucking trailing commas until it's valid syntax"-problem. It's a very reasonable convention to have.
What should really happen is that the SQL standard should allow trailing commas:
select
a,
b,
from t;Everybody is up in arms about the comma suggestion but everyone thinks the 1=1 is a good idea in the where clause? If I saw that in a code review I don’t know what I’d think of the author.