Hi SQl expert,
I have seen in SQL statement of: where 1 = 1. What actually this where clause for? What is the benefit having this where 1 = 1?
Basically it is just for programmer convenience since you can just add additional conditions with AND... after that and it has no impact on execution time.
Note that WHERE 1 is identical to WHERE 1=1; both mean WHERE TRUE but the former is rejected by many database management systems as not really being boolean.
It's just a dummy condition. It is valid when used in dynamic SQL. To avoid SQL Injection Where 1=1 is used inside the dynamic SQL.
1 Like
Where 1=1 has NOTHING to do with avoiding SQL Injection.
1 Like
So that when dynamically constructing SQL, all conditions added can start with "AND ". Otherwise you'd constantly have to test to see which was the first condition added.
For example, say you allow people to enter up to 5 dynamic column conditions. With the "WHERE 1 = 1" in place, you just do this:
IF @cond1 > ''
SET @sql = @sql + 'AND (' + @cond1 + ')'
IF @cond2 > ''
SET @sql = @sql + 'AND (' + @cond2 + ')'
--...
--Without the "WHERE 1 = 1", you have to do something like this, ugh!:
IF @cond1 > ''
SET @sql = @sql + @cond1
IF @cond2 > ''
SET @sql = @sql + CASE WHEN @cond1 > '' THEN 'AND ' ELSE '' END + @cond2
IF @cond3 > ''
SET @sql = @sql + CASE WHEN @cond1 > '' OR @cond2 > '' THEN 'AND ' ELSE '' END + @cond3
--...
I agree. But, I have seen people using 1=1 when constructing dynamic SQL. They start with a statement such as
SELECT col1, col2 FROM SomeTable WHERE 1 = 1
Then, depending on the logic they want to add, they add additional where clauses such as
AND DeliveryDate > PromisedDate
This allows the construction of additional where clauses without worrying about whether you need an "AND" at the beginning or not. So it is a matter of convenience.
Refer the link to know about the correct purpose
http://blog.sqlauthority.com/2015/10/01/sql-server-what-does-where-11-mean-in-the-code/
Isn't Pinal saying the same thing as what I described? I don't see why Pinal's description is any more correct than what I posted, nor do I see any fallacy in what I posted. Am I missing something?
I think @viggneshwar missed the point that 1 = 1 is not passed in this case. In the referenced blog it was passed into a dynamic code where here it is already at the start and the next command MUST NOT be linked with an OR.
I use 1 = 1 when debugging so I can comment my criteria
SELECT c1, c2
FROM mytable
WHERE 1 = 1
--AND c3 > 50
AND c3 > 100;
Absolutely agreed but, like I said, by itself, WHERE 1=1 has nothing to do with the prevention of SQL INJECTION in dynamic SQL. In fact, it's a strong indication of where SQL INJECTION is a possibility.
I see what you're getting at in that article but you've misinterpreted it a bit. The attacker isn't injecting "WHERE 1=1". The attacker is injecting "OR 1=1".
Actually, that is precisely the WRONG way to use WHERE 1=1 because every example on that post is subject to even simple SQL INJECTION because it concatenates user input into the SQL.
If you must use Dynamic SQL, do it correctly. Please see the following...
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Notice that only the variable names are concatenated and never the content of the variables.
I avoid using Pinal's site for reference. Seems to me that it has very high Google ranking, and yet often I have either seen flaws in his examples, or comments that point out the flaws, and yet the comments are not replied to, nor the original example improved or fixed.
Maybe it's just me?
No but it's not just things on that site. Everyone makes mistakes, especially me. 
To be honest, I'm finding that a whole lot of people in the world post some pretty bad stuff. My latest thing is all the people that still think recursive CTEs that "increment" a count or a date are somehow the bee's knees. They're absolutely horrible for performance and resource usage but people keep justifying that the difference between 15 parallel CPU ms and <1ms isn't a concern. They forget that the rCTE is doing nearly 4400 reads (= 36 MILLION bytes of memory IO) and they also don't have a clue how that stuff stacks up over time. Imagine if you made all your "simple" code run more than 15 times faster.
As Granny used to say, "Mind the Pennies and the Dollars will take care of themselves". As I say, "DO sweat the small stuff and everything is small stuff".
The implicit assumption that you are making is that one uses dynamic SQL to pick up user input and concatenate - which perhaps is true in a lot of cases. In Pinal's article, he mentions "Suppose you want to use two parameters and pass values...." which implies precisely that type of user-input, and so it would have been useful for him to at least mention the possibility of SQL injection attacks when used that way.
My hope is that even novices, when searching for dynamic SQL are usually presented with the possibility of SQL injection attacks, and so would be aware of the dangers. In the example that I posted earlier, I was concatenating the string
AND DeliveryDate > PromisedDate
Even though I did not explicitly say it, the thought in my mind was that decision to include or not include that string is based on a user-supplied boolean parameter such as @ShowOnlyLateDeliveries. When used that way, the dynamic SQL would be immune to injection attacks.
Great, thanks guys for all the responded, link. All of these are great info. You guys ROCK!