Shulou(Shulou.com)06/01 Report--

This article will explain in detail how to truncate long characters in MYSQL. The content of the article is of high quality. Therefore, Xiaobian shares it with you as a reference. I hope you will have a certain understanding of relevant knowledge after reading this article.

MYSQL long character truncation Even if there is no injection vulnerability, an attacker may log in to the background management page.

2, sql-mode settings

MySQL 5.0 and above supports three sql_mode modes: ANSI, TRADITIONAL, and STRICT_TRANS_TABLES.

ANSI mode: loose mode, changing syntax and behavior to make it more consistent with standard SQL. Check the inserted data. If it does not conform to the defined type or length, adjust or truncate the data type and save it, and warn. For the errors mentioned at the beginning of this article, you can set sql_mode to ANSI mode first, so that you can insert data, and the database will replace the field value of the result with NULL value for divisor 0.

TRADITIONAL mode: strict mode, when inserting data into mysql database, strict verification of data to ensure that error data can not be inserted, error error, not just warning. When applied to things, things roll back. Note: Discard INSERT/UPDATE as soon as an error is found. If you are using a non-transactional storage engine, this is not the way you want it, because data changes made prior to the error do not "roll" and the result is updates "only partially made."

STRICT_TRANS_TABLES mode: strict mode, strict verification of data, error data can not be inserted, error reported. If the given value cannot be inserted into the transaction table, the statement is discarded. For non-transactional tables, if the value appears on line 1 of a single-line or multiple-line statement, the statement is discarded.

STRICT_TRANS_TABLES：

In this mode, if a value cannot be inserted into a transactional table, the current operation is aborted and no restrictions are placed on non-transactional tables. Must be set, later items may depend on the setting of this item

NO_ENGINE_SUBSTITUTION：

If the required storage engine is disabled or not compiled, an error is thrown. When this value is not set, replace it with the default storage engine and throw an exception

ONLY_FULL_GROUP_BY：

For GROUP BY operations, if the individual columns that appear in SELECT do not appear in the GROUP BY clause, then the SQL is illegal

NO_AUTO_VALUE_ON_ZERO：

This value affects insertions from growing columns. By default, inserting 0 or NULL generates the next self-increasing value. This option is useful if the user wants to insert a value of 0 and the column is self-growing

NO_ZERO_IN_DATE：

Zero days and months are not allowed in strict mode

NO_ZERO_DATE：

Set this value, mysql database does not allow insertion of zero date, inserting zero date throws error instead of warning

ERROR_FOR_DIVISION_BY_ZERO：

During INSERT or UPDATE, if the data is divided by zero, an error is generated instead of a warning. MySQL returns NULL when the data is divided by zero if the pattern is not given

NO_AUTO_CREATE_USER：

Disable GRANT statements from creating users with empty passwords

PIPES_AS_CONCAT：

will "||"A concatenation operator treated as a string rather than an OR operator, which is the same as Oracle Database and similar to Concat for strings

ANSI_QUOTES：

When ANSI_QUOTES is enabled, strings cannot be quoted in double quotes because they are interpreted as identifiers

You can also view and set sql_mode variables on the command line

3. Cause: (1) STRICT_TRANS_TABLES or TRADITIONAL is not added in sql-mode (2) ANSI is added

Here username is varchar(5), that is, the maximum specified length is 5, and the input value is 'admsddsff', the length is 9, exceeding the specified length, but no error is reported. It is known from the query that it has been truncated. So we can exploit this loophole, like the line circled in red on the second sheet of the related material.

4. Prevention method: Add STRICT_TRANS_TABLES or TRADITIONAL in sql-mode

After adding "STRICT_TRANS_TABLES" or "TRADITIONAL," an error will be reported if the input data exceeds the specified length, and cannot be input.

About MYSQL how to truncate long characters to share here, I hope the above content can be of some help to everyone, you can learn more knowledge. If you think the article is good, you can share it so that more people can see it.

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.