Programmers often use “where 1=1” in their code during programming. Why?

SQL injection

Where 1=1 where 1=1 where 1=1 where 1=1 Such as:

select * fromcustomers; withselect * from customers where 1=1;
Copy the code

The results are exactly the same.

Yes, the above query results in no difference, but that’s not what we added it for. We know that 1=1 means true, that is, forever true, when SQL injection with or operator will get unexpected results.

For example, when we want to delete a record with the customer name “Zhang SAN”, we can write:

delete from customers where name='Joe'
Copy the code

If you add or 1=1 after the where statement, what will happen? That is:

delete from customers where name='Joe' or 1=1
Copy the code

If or 1=1, all entries in the table will be deleted.

Of course, we must not do this kind of thing, also do not allow others to take advantage of, here is just to express where 1=1 one of the functions.

Grammar specification

We use where 1=1 when we write code to make sure that the syntax is correct.

Let’s start with the following Java code:

String sql="select * from table_name where 1=1";
if( condition 1) {
  sql=sql+" and var2=value2";
}
if(condition 2) {
  sql=sql+" and var3=value3";
}
Copy the code

If condition 1 is true, the following SQL code will be executed after concatenation:

select * from table_name where and var2=value2;
Copy the code

There is an obvious SQL syntax error: and must be conditional before and after.

Some people say I just write where in the if statement, so I don’t write where 1=1.

String sql="select * from table_name";
if( condition 1) {
  sql=sql+" where var2=value2 ";
}
if(condition 2) {
  sql=sql+" where var3=value3";
}
Copy the code

If condition 1 is true and condition 2 is false, the following SQL code is executed:

select * from table_name where var2=value2;
Copy the code

This is true, but what if condition 1 and condition 2 are both true? The SQL statement then looks like this:

select * from table_name 
where var2=value2 
where var3=value3;
Copy the code

This is clearly not SQL syntactic.

1=1 where 1=1 where 1=1 where 1=1 where 1=1 where 1=1 where 1=1

Copy the table

When backing up data, we often use where 1=1. Of course, we can not write where 1=1, but if we want to filter some data after writing it, it will be more convenient to add and conditions at the end.

create table  table_name
as   
select * from  Source_table
where   1=1;
Copy the code

Replicate table structure

Select * from table where 1=1 where 1<>1 and 1=2 where 1<>1 and 1=2

create table  table_name
as   
select  * from   
Source_table where   1 <> 1;
Copy the code

For more technical content on SQL, please follow the official number: Java Architect Consortium