"where 1=1" statement [duplicate]

Question

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?

Bathhouse

Note that in MySQL, just WHERE 1 and ... would work as well... – Iyar

it is a alias for true – Prepotent

also used in sqlinjection attempts – Underdeveloped

Also note that SELECT * FROM myTable WHERE 1 = 2 is a very basic and generic way of fetching the column schema of a table :) (Just in case there are no other options available) – Publicity

It prevents your query from running in the alternate universe where 1=2. – Wheeled

@Christian: what do you mean by table schema here because when I execute that query, I just see empty resultset with column name of that table. Is it the same you mean table schema! – Amylose

@Amylose That's the whole point, to get no rows but all columns of that table. Executing such a query e.g. via ADO.NET, you get a DataTable with a Columns collection where you can check out some properties of the columns :) – Publicity

Answer 1

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;

Audryaudrye

Awesome. Now I can build my complex queries with strings rather than arrays ;). I did, however, want to verify with an EXPLAIN query whether it triggered "Using Where." Indeed, it does not. – Slipcase

Though more experienced programmers would do something like " and ".Join(conditions) to generate the where statement... or use an O/RM... – Alar

If you were building stuff of "or"s you would instead start with "where false " or "where 1=0 ". – Gastritis

The problem with conditions.join(" and ") is that if conditions.length == 0 you end up with a dangling where. So experienced programmers would actually use 1=1. – Georgianngeorgianna

Answer 2

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

Berey

IMO this is just bad coding. Personally I store all conditions in an array then stick them together with "AND". – Embowel

I believe it would more like " AND " – Langille

@Embowel - And that's absolutely a better way to do things; but sometimes you don't have the luxury of easy to manipulate arrays and array join statements. – Demesne

@BenW You don't need easy to manipulate arrays and array join statements to do if (first) { s += " WHERE"; first = false; } else { s += " AND"; } – Towline

Storing an array of database filter conditions in programming code is a good development practice? "where 1=1" is a classic dynamic sql in a stored proc pattern. – Wheeled

Answer 3

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.

Balm

Answer 4

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.

Ratiocinate

I can't see how this is related to the question. adding 'OR 1=1' (I guess the OR where was a typo) through SQLi will return all results no matter if you have "where 1=1 and cond1" or just "where cond1". – Maomaoism

The question was "what does 1=1 mean" and I just thought it'd be a useful example to show how it can be used. – Ratiocinate

I agree that it's a useful note. If someone comes across the question of why am I seeing 1=1 they should always consider the possibility it's a user crafted SQL injection. – Irmairme

Sorry, I didn't notice it was about the 1=1 read from logs but instead thought it was only about why using 1=1 AND something. Indeed, it's a useful within that context. – Maomaoism

+1 for OR. I didn't understand the other SQL injection answer until I read this. – Defeatism

Answer 5

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

Rattail

Answer 6

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.

Gesualdo

How are your examples a security issue? There is no SQL Injection possible. The security issue would be to use $cond (without converting it to an integer) to specify the minimum age in the query, and to check if isset($_REQUEST['cond']) is actually true (otherwise using 1 = 1). – Denson

Answer 7

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.

Gpo

Answer 8

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.

Savoyard

Answer 9

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

Pianette

Answer 10

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.

Presber