What's the use of where 1=1?

Source: Internet
Author: User
Tags sql injection

Why add the where 1=1 to the back of the SQL statement and really wonder about the children's shoes that were first seen.

It is felt that the select * from table1 where 1=1 is completely indistinguishable from the select * from table1.

In fact, this idea is wrong. Remember that the "existence is reasonable"

Regardless of where 1=1 or 1<>2 in the SQL statement, ' a ' = ' a ', ' a ' <> ' B ', the purpose is only one, where the condition is never true, and the result is unconstrained.

This is used in SQL injection,

For example: DELETE fromtable_a WHERE a= ' fallout

To forcibly add DELETE from table_a WHERE a= ' Fallout ' or 1=1

This data, which was originally deleted as a value of fallout, becomes an unconstrained deletion.

1=1 forever True, 1<>1 forever false.

For example, a fuzzy query may have a, B, C, d constraints, or maybe not, how to deal with it.

String sql = select * FROM table1 where 1=1;
if (!a.equals ("")) {
sql=sql+ "a= '" +a+ "";
}
if (!b.equals ("")) {
sql=sql+ "B" "+b+" ";
}
if (!c.equals ("")) {
sql=sql+ "c= '" +c+ "";
}
if (!d.equals ("")) {
sql=sql+ "d= '" +d+ "";
}

Why write Extra 1=1. I'll find out soon.

Where 1=1 is written in order to detect conditions in the process of checking

The above example of the four parameters may be empty, when you want to construct the statement, a test and write a statement on the trouble

How do you write here? Do you want to add where or directly with and? You also need to detect whether the parameter is empty

With the where 1=1, there is no such problem, provided that and is directly and, or is directly connected to or

If you do not write 1=1, then in each of the query conditions that are not empty, must be judged there is no where sentence. Or you're going to add where you first appear.


Still don't understand.

The use of 1=1 is mainly used for component dynamic SQL

String SQL  =  "Select A,b from Table_a  where 1=1"; 
if (!b.equals (""))
      SQL + = "and  b= '" +b+ "";
When the user chooses B (if the B value is qwe)

The result is: String sql = ' Select A,b from table_a where 1=1 and b= ' qwe ';

But when the user does not choose b that B is a null value

The result is that String sql = ' Select A,b from table_a where 1=1 ';, the run will not be wrong, equivalent to the condition of No limit B.

But if there is no 1=1 condition, then l String sql = ' Select a,b from table_a where '; This will cause an error.


Use of 1<>1: for applications where no data is taken from the structure
For example:
CREATE TABLE Table_temp tablespace tbs_temp as SELECT * from Table_ori where 1<>1
Build a table table_temp the same as the Table_ori structure, but don't table_ori the data. (in addition to the table structure, other structures are the same)

In addition to the 1=1 or 1<>1 other than the conditions of perpetual and permanent leave the same.

Copy table

Create table_name AS SELECT * from source_table where 1=1;

Duplicate table structure

Create table_name AS SELECT * from source_table where 1 <> 1;



Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.