Why would someone use WHERE 1=1 AND i

Question

Why would someone use WHERE 1=1 AND <conditions> in a SQL clause (Either SQL obtained through concatenated strings, either view definition)

I've seen somewhere that this would be used to protect against SQL Injection, but it seems very weird.

If there is injection WHERE 1 = 1 AND injected OR 1=1 would have the same result as injected OR 1=1.

Later edit: What about the usage in a view definition?

---

Thank you for your answers.

Still, I don't understand why would someone use this construction for defining a view, or use it inside a stored procedure.

Take this for example:

CREATE VIEW vTest AS
SELECT FROM Table WHERE 1=1 AND table.Field=Value

Answer 1

1 = 1 expression is commonly used in generated sql code. This expression can simplify sql generating code reducing number of conditional statements.

Answer 2

If you came here searching for WHERE 1, note that WHERE 1 and WHERE 1=1 are identical. WHERE 1 is used rarely because some database systems reject it considering WHERE 1 not really being boolean.

Answer 3

I do this usually when I am building dynamic SQL for a report which has many dropdown values a user can select. Since the user may or may not select the values from each dropdown, we end up getting a hard time figuring out which condition was the first where clause. So we pad up the query with a where 1=1 in the end and add all where clauses after that.

Something like

select column1, column2 from my table where 1=1 {name} {age};

Then we would build the where clause like this and pass it as a parameter value

string name_whereClause= ddlName.SelectedIndex > 0 ? "AND name ='"+ ddlName.SelectedValue+ "'" : "";

As the where clause selection are unknown to us at runtime, so this helps us a great deal in finding whether to include an 'AND' or 'WHERE'.

Answer 4

where 1=0, This is done to check if the table exists. Don't know why 1=1 is used.

Answer 5

Actually, I've seen this sort of thing used in BIRT reports. The query passed to the BIRT runtime is of the form:

select a,b,c from t where a = ?

and the '?' is replaced at runtime by an actual parameter value selected from a drop-down box. The choices in the drop-down are given by:

select distinct a from t
union all
select '*' from sysibm.sysdummy1

so that you get all possible values plus "*". If the user selects "*" from the drop down box (meaning all values of a should be selected), the query has to be modified (by Javascript) before being run.

Since the "?" is a positional parameter and MUST remain there for other things to work, the Javascript modifies the query to be:

select a,b,c from t where ((a = ?) or (1==1))

That basically removes the effect of the where clause while still leaving the positional parameter in place.

I've also seen the AND case used by lazy coders whilst dynamically creating an SQL query.

Say you have to dynamically create a query that starts with select * from t and checks:

• the name is Bob; and
• the salary is > $20,000

some people would add the first with a WHERE and subsequent ones with an AND thus:

select * from t where name = 'Bob' and salary > 20000

Lazy programmers (and that's not necessarily a bad trait) wouldn't distinguish between the added conditions, they'd start with select * from t where 1=1 and just add AND clauses after that.

select * from t where 1=1 and name = 'Bob' and salary > 20000

Answer 6

I found usefull this pattern when I'm testing or doublechecking things on the database, so I can comment very quickly other conditions:

CREATE VIEW vTest AS
SELECT FROM Table WHERE 1=1 
AND Table.Field=Value
AND Table.IsValid=true

turns into:

CREATE VIEW vTest AS
SELECT FROM Table WHERE 1=1 
--AND Table.Field=Value
--AND Table.IsValid=true