"where 1=1" statement [duplicate]

Question

It's usually when folks build up SQL statements.

When you add and value = "Toyota" you don't have to worry about whether there is a condition before or just WHERE. The optimiser should ignore it

No magic, just practical

---

Example Code:

commandText = "select * from car_table where 1=1";

if (modelYear <> 0)     commandText += " and year="+modelYear
if (manufacturer <> "") commandText += " and value="+QuotedStr(manufacturer)
if (color <> "")        commandText += " and color="+QuotedStr(color)
if (california)         commandText += " and hasCatalytic=1"

Otherwise you would have to have a complicated set of logic:

commandText = "select * from car_table"
whereClause = "";
if (modelYear <> 0)
{
   if (whereClause <> "") 
      whereClause = whereClause + " and ";
   commandText += "year="+modelYear;
}
if (manufacturer <> "")
{    
   if (whereClause <> "") 
      whereClause = whereClause + " and ";
   commandText += "value="+QuotedStr(manufacturer)
}
if (color <> "")
{
   if (whereClause <> "") 
      whereClause = whereClause + " and ";
   commandText += "color="+QuotedStr(color)
}
if (california)
{
   if (whereClause <> "") 
      whereClause = whereClause + " and ";
   commandText += "hasCatalytic=1"
}

if (whereClause <> "")
   commandText = commandText + "WHERE "+whereClause;

If that query is being built dynamically, original author probably doesn't want to consider an empty set of conditions, so ends with something like this:

sql = "select * from car_table where 1=1"
for each condition in condition_set

    sql = sql + " and " + condition.field + " = " + condition.value

end

1=1 will always be true, so the value="TOYOTA" bit is the important one.

You get this in a few scenarios including:

Generated SQL: It's easier to create a generate a complex where statement if you don't have to work out if you're adding the first condition or not, so often a 1=1 is put at the beginning, and all other conditions can be appended with an And

Debugging: Sometimes you see people put in a 1=1 at the top of a where condition as it enables them to freely chop and change the rest of the conditions when debugging a query. e.g.

select * from car_table
where 1=1
--and value="TOYOTA"
AND color="BLUE"
--AND wheels=4

It has to be said that it isn't particularly good practice and normally shouldn't occur in production code. It may even not help the optimization of the query very much.

As well as all the other answers, it's a simple technique for SQL injection attacks. If you add a OR where 1=1 statement to some SQL then it's going to return all the results due to the inherent truthiness of the expression.

Its just an always true expression. Some people use it as an work-around.

They have a static statement like:

select * from car_table where 1=1

So they can now add something to the where clause with

and someother filter

the 1=1 where condition is always true because always 1 is equal 1 , so this statement will be always true. While it means nothing sometimes. but other times developers uses this when the where condition is generated dynamically.

for example lets see this code

<?php
//not that this is just example
//do not use it like that in real environment because it security issue.
$cond = $_REQUEST['cond'];
if ($cond == "age"){
 $wherecond = " age > 18";
}         
$query = "select * from some_table where $wherecond";
?>

so in the above example if the $_REQUEST['cond'] is not "age" the query will return mysql error because there are nothing after the where condition.

the query will be select * from some_table where and that is error

to fix this issue (at least in this insecure example) we use

<?php
//not that this is just example
//do not use it like that in real environment because it security issue.
$cond = $_REQUEST['cond'];
if ($cond == "age"){
 $wherecond = " age > 18";
} else {
 $wherecond = " 1=1";
}        
$query = "select * from some_table where $wherecond";
?>

so now if the $_REQUEST['cond'] is not age the $wherecond will be 1=1 so the query will not have mysql error return.

the query will be select * from some_table where 1=1 and that avoid the mysql error

hope you understand when we use 1=1 while note that the above example is not real world example and it just to show you the idea.