"where 1=1" statement [duplicate]

Possible Duplicate:
Why would someone use WHERE 1=1 AND <conditions> in a SQL clause?

I saw some people use a statement to query a table in a MySQL database like the following:

select * from car_table where 1=1 and value="TOYOTA"

But what does 1=1 mean here?

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

"where 1=1" statement [duplicate]

Related

Recent Posts