Shulou(Shulou.com)05/31 Report--

This article mainly explains "what is the meaning of writing 1 after the Where condition in the SQL sentence". The content of the explanation in the article is simple and clear, and it is easy to learn and understand. please follow the editor's train of thought to study and learn "what is the meaning of writing 1 after the Where condition in the SQL sentence"!

SQL injection

Students who see this way of writing for the first time must be wondering whether to add where 1 # 1, isn't the query all the same? For example:

Select * from customers; and select * from customers where 1

There is no difference between the results of the query.

Yes, there is no difference in the query results above, but that's not why we want to add it. We know that 1x1 means true, that is, forever true, and when SQL is injected with the or operator, you will get an unintended result.

For example, when we want to delete a record with the customer name "Zhang San", we can write:

Delete from customers where name=' Zhang San'

What will happen if you add or 1 to 1 after the where statement at this time?

That is:

Delete from customers where name=' Zhang San'or 1

Originally, as long as Zhang San's records were deleted, as a result, due to the addition of the true condition of or 1, the records in the whole table would be deleted.

Of course, we must not do this kind of thing, nor can we allow others to take advantage of it. This is just to express one of the functions of where 1.

Grammatical norm

In the process of writing code, in order to ensure the syntax specification, we will also use where 1. 1.

Let's take a look at 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";}

If we do not write 1x1, when condition 1 is true, the SQL code executed after the code is stitched is as follows:

Select * from table_name where and var2=value2

Obviously, there is a syntax error for SQL: and must be conditional before and after.

Some people say that if I write where directly in the if statement, I will not write where 1.

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

When condition 1 is true and condition 2 is false, the SQL code executed above is:

Select * from table_name where var2=value2

There are no syntax errors, but what if both condition 1 and condition 2 are true? Then the SQL statement looks like this:

Select * from table_name where var2=value2 where var3=value3

Obviously, this does not conform to the SQL syntax specification.

Where 1 is written here to avoid grammatical errors caused by the first word after the where keyword "and". With or without and conditions, there will be no grammatical errors.

Copy table

We often use where 1 backup 1 when backing up data. Of course, you don't have to write these two. If you want to filter some data and then back up, it will be more convenient to add and conditions later.

Create table table_name as select * from Source_table where 1

Copy table structure

If there is 1x1, there will be a condition of permanent leave such as 11 or 2, which adds where 11 when copying the table, which means that no record meets the criteria, so we can just copy the table structure and not copy the data.

Create table table_name as select * from Source_table where 1 1

The disadvantages of 1: 1

When we write SQL, we add 1: 1, although we can guarantee that the grammar will not make mistakes!

Select * from table where 1

But because there is no field named 1 in table, the SQL is actually equivalent to select * from table. This SQL statement is obviously a full table scan, which requires a lot of IO operations. The larger the amount of data, the slower the amount of data.

Therefore, when querying, other conditions need to be added after the where1=1, and these conditions need to be properly indexed, so the efficiency will be greatly improved.

Thank you for your reading, the above is the content of "what is the meaning of writing 1 after the Where condition in the SQL sentence". After the study of this article, I believe you have a deeper understanding of what it means to write 1 after the Where condition in the SQL sentence, and the specific use still needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.