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
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.
Most of time developer use these type of query if he is developing a query builder type application or building some complex SQL query so along with the select statement string add a conditional clause Where 1=1, and in program no need to add any check for it.
The query finds all rows for which 1 equals 1 and value equals 'TOYOTA'. So in this case it's useless, but if you omit a WHERE statement, it can be a good idea to use WHERE 1=1 to remind you that you chose NOT to use a WHERE clause.
the use of this comes in complex queries when passing conditions dynamically,You can concatenate conditions using an " AND " string. Then, instead of counting the number of conditions you're passing in, you place a "WHERE 1=1" at the end of your stock SQL statement and throw on the concatenated conditions.
no need to use 1=1 you can use 0=0 2=2,3=3,5=5 25=25 ......
select * from car_table where 0=0 and value="TOYOTA"
here also you will get the same result like 1=1 condition
because all these case is always true expression
1=1 is alias for true
i did this when i need to apply the filters dynamically.
like, while coding i dunno how many filter user will apply (fld1 = val1 and fld2=val2 and ...)
so, to repeat the statement "and fld = val" i start with "1 = 1".
hence, i need not trim the first "and " in the statement.
