In-depth analysis of SQL string length restriction Vulnerability

The limit length of a MySQL string does not seem to be important. In fact, it is closely related to the security of the entire MySQL database. It is worth further research and analysis.

SQL injection attacks have been widely discussed. However, the two security risks I will introduce today are ignored, this is a problem that can be caused by long SQL queries and Single Column SQL character length restrictions.

First, let's talk about super-long SQL queries.

Max_packet_size
This item is used to limit the length of data packets between the mysql client and the server. For example, if a query is "select * from user where 1", the length is only dozens of bytes, so it will not exceed the standard. In most cases, it is difficult for us to exceed the default mysql limit of 1 MB. we can imagine that the SQL statement of 1 MB is still very long ). After reading this article, I finally understand the reason why I failed to INSERT data using PEAR DB, probably because the data length exceeds the standard. For MySQL, if the size of the MySQL string to be queried exceeds this limit, mysql will not perform any query operations.

If the visitor may control the length of your SQL statement, your program may be attacked. In which cases can visitors control the length of SQL statements, such as searches without limit on the length of keywords. It is also possible that if your program needs to enable the user's login as a log, in short, wherever too long SQL queries are involved, you must carefully check your own SQL to prevent query from being invalid due to excessive length. But to be honest, I think this is not a big problem. The administrator of the database light can also set the length of max_packet_size of MySQL on his own, you can also make a length judgment when processing SQL queries that may be too long.

MySQL column length limit
This is the focus of this article. If the length of the inserted string exceeds the maximum length of the data table, MySQL inserts the previous MySQL string into the database without reporting the error to the web program. For careless programmers, this problem may lead to program vulnerabilities. In fact, there are many restrictions on wordpress, and the attack through this vulnerability should have no effect. The following are the original author's assumptions. If these conditions are met at the same time, it is quite easy to get the user name of a site, fortunately, wordpress does not meet the following conditions at the same time:

This web application allows the user to register an open registration wordpress to meet this condition );
The super administrator's username is known, such as admin, so that attackers can find the target. Poor wordpress is also satisfied)
Most of the default configurations used by MySQL are satisfied)
When registering a new user, the program did not limit the length of the user name I tested, wordpress also met)
The user name is limited to 16 characters, which is irrelevant to the above, just for example)
Next, let's take a look at how attackers attack:

First, the attacker uses the known super administrator id such as admin registration, then the program will use

(Show/hide) plain text
SELECT * FROM user WHERE username = 'admin'
To check whether the ID already exists. If it exists, registration is not allowed. Of course, attackers will certainly fail to try to register admin;

However, if the attacker uses 11 or more spaces between admin Xadmin and x to register, according to the above judgment, because admin x does not exist in the database, so of course, the registration is successful. In fact, the version before wordpress2.6.1 does. Because the column length is limited to 16 characters, the end x is truncated, now there are two identical user admins in the database. (Narrator: bad, not all of my programs need to be modified. In fact, there is no need. You only need to set the ID to UNIQUE. As a result, the following problems have nothing to do with you)

The attacker continues. At this time, the attacker successfully registered the admin username, and then logged on to the account using the admin and password to manage wordpress. Even if the account was registered, the attacker could not log on ), because the real admin account is registered before the attacker admin, the information displayed on the account information page is very likely to be the real admin information, including password prompts and emails, in this case, attackers can modify the admin information, including the password and password retrieval.

Therefore, if you write a web program, should you check whether your program has such vulnerabilities.

Number sorting in MySQL strings

In-depth understanding of MySQL string comparison functions

Provides you with an in-depth understanding of MYSQL string connection

MySQL string connection function repeat ()

MySQL string segmentation and concatenation statements