Summary of implicit conversions of MySQL and summary of mysql

Summary of implicit conversions of MySQL and summary of mysql

Preface

I saw an article a few days ago: the implicit conversions of MySQL with millions of values are quite good, and I am not very clear about the implicit conversions of MySQL, so I sorted it out. Hope to help you.

When we compare values of different types, to make these values "comparable" (also known as type compatibility ), mySQL performs some Implicit conversions (Implicit type conversion ).

For example:

mysql> SELECT 1+'1'; -> 2mysql> SELECT CONCAT(2,' test'); -> '2 test'

Obviously, implicit conversions occur during the execution of the preceding SQL statement. In addition, we can tell from the results that in the first SQL statement, the "1" of the string is converted to the number 1, while in the second SQL statement, convert Number 2 to string "2 ".

MySQL also provides the CAST () function. We can use it to explicitly convert a value to a string. When usingCONCA()Implicit conversion may also occur when a function is used, because the desired parameter is in the string format. But what if we pass a string instead of a string:

mysql> SELECT 38.8, CAST(38.8 AS CHAR); -> 38.8, '38.8'mysql> SELECT 38.8, CONCAT(38.8); -> 38.8, '38.8'

Implicit conversion rules

The implicit conversion rules in the official documentation are described as follows:

If one or both arguments are NULL, the result of the comparison is NULL, should t for the NULL-safe <=> equality comparison operator. for NULL <=> NULL, the result is true. no conversion is needed.

• If both arguments in a comparison operation are strings, they are compared as strings.
• If both arguments are integers, they are compared as integers.
• Hexadecimal values are treated as binary strings if not compared to a number.
• If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a constant, the constant is converted to a timestamp before the comparison is already med. this is done to be more ODBC-friendly. note that this is not done for the arguments to IN ()! To be safe, always use complete datetime, date, or time strings when doing comparisons. for example, to achieve best results when using BETWEEN with date or time values, use CAST () to explicitly convert the values to the desired data type.
  A single-row subquery from a table or tables is not considered a constant. for example, if a subquery returns an integer to be compared to a DATETIME value, the comparison is done as two integers. the integer is not converted to a temporal value. to compare the operands as DATETIME values, use CAST () to explicitly convert the subquery value to DATETIME.
• If one of the arguments is a decimal value, comparison depends on the other argument. the arguments are compared as decimal values if the other argument is a decimal or integer value, or as floating-point values if the other argument is a floating-point value.
• In all other cases, the arguments are compared as floating-point (real) numbers.

The translation is as follows:

1. When at least one of the two parameters is NULL, the comparison result is also NULL. The exception is that when two NULL values are compared, 1 is returned. No type conversion is required in both cases.
   
2. Both parameters are strings and will be compared according to strings without type conversion.
   
3. Both parameters are integers, which are compared by integers without type conversion.
   
4. When the hexadecimal value is compared with a non-numeric value, it is treated as a binary string.
   
5. One parameter is TIMESTAMP or DATETIME, and the other is a constant, the constant will be converted to timestamp
   
6. One parameter is of the decimal type. If another parameter is of the decimal or Integer type, the integer is converted to the decimal type for comparison. If the other parameter is of the floating point type, converts decimal to a floating point number for comparison.
   
7. In all other cases, both parameters are converted to floating-point numbers for comparison.
   

Notes

Security question: if the password type is string and the query condition is int 0, it will match.

mysql> select * from test;+----+-------+-----------+| id | name | password |+----+-------+-----------+| 1 | test1 | password1 || 2 | test2 | password2 |+----+-------+-----------+2 rows in set (0.00 sec)mysql> select * from test where name = 'test1' and password = 0;+----+-------+-----------+| id | name | password |+----+-------+-----------+| 1 | test1 | password1 |+----+-------+-----------+1 row in set, 1 warning (0.00 sec)mysql> show warnings;+---------+------+-----------------------------------------------+| Level | Code | Message   |+---------+------+-----------------------------------------------+| Warning | 1292 | Truncated incorrect DOUBLE value: 'password1' |+---------+------+-----------------------------------------------+1 row in set (0.00 sec)

I believe in the above example, some clever people can find that the above example can also be used for SQL injection.

Assume that the website logon fails. Use the following method:

SELECT * FROM users WHERE username = '$_POST["username"]' AND password = '$_POST["password"]'

If username isa' OR 1='1, Then enter the password as needed, and the following query is generated:

SELECT * FROM users WHERE username = 'a' OR 1='1' AND password = 'anyvalue'

It is possible to log on to the system. In fact, if attackers read this article, they can use implicit conversion to log on. As follows:

mysql> select * from test;+----+-------+-----------+| id | name | password |+----+-------+-----------+| 1 | test1 | password1 || 2 | test2 | password2 || 3 | aaa | aaaa || 4 | 55aaa | 55aaaa |+----+-------+-----------+4 rows in set (0.00 sec)mysql> select * from test where name = 'a' + '55';+----+-------+----------+| id | name | password |+----+-------+----------+| 4 | 55aaa | 55aaaa |+----+-------+----------+1 row in set, 5 warnings (0.00 sec)

The reason for this is:

mysql> select '55aaa' = 55;+--------------+| '55aaa' = 55 |+--------------+| 1 |+--------------+1 row in set, 1 warning (0.00 sec)mysql> select 'a' + '55';+------------+| 'a' + '55' |+------------+| 55 |+------------+1 row in set, 1 warning (0.00 sec)

Here are some examples to review the above conversion rules:

mysql> select 1+1;+-----+| 1+1 |+-----+| 2 |+-----+1 row in set (0.00 sec)mysql> select 'aa' + 1;+----------+| 'aa' + 1 |+----------+| 1 |+----------+1 row in set, 1 warning (0.00 sec)mysql> show warnings;+---------+------+----------------------------------------+| Level | Code | Message  |+---------+------+----------------------------------------+| Warning | 1292 | Truncated incorrect DOUBLE value: 'aa' |+---------+------+----------------------------------------+1 row in set (0.00 sec)

Sum the strings "aa" and 1 to get 1. Because the types of "aa" and number 1 are different, the official MySQL documentation tells us:

When an operator is used with operands of different types, type conversion occurs to make the operands compatible.

View warnings. You can see that the implicit conversion converts a string to the double type. However, because the string is non-numeric, it is converted to 0. Therefore, 0 + 1 = 1 is calculated.

In the preceding example, implicit conversion occurs because of different types. What if we use values of the same type for calculation?

mysql> select 'a' + 'b';+-----------+| 'a' + 'b' |+-----------+|  0 |+-----------+1 row in set, 2 warnings (0.00 sec)mysql> show warnings;+---------+------+---------------------------------------+| Level | Code | Message    |+---------+------+---------------------------------------+| Warning | 1292 | Truncated incorrect DOUBLE value: 'a' || Warning | 1292 | Truncated incorrect DOUBLE value: 'b' |+---------+------+---------------------------------------+2 rows in set (0.00 sec)

Is it a bit depressing?

This is because + is the arithmetic operator, which can explain why both a and B are converted to double. The conversion is actually: 0 + 0 = 0.

Let's look at another example:

mysql> select 'a'+'b'='c';+-------------+| 'a'+'b'='c' |+-------------+|  1 |+-------------+1 row in set, 3 warnings (0.00 sec)mysql> show warnings;+---------+------+---------------------------------------+| Level | Code | Message    |+---------+------+---------------------------------------+| Warning | 1292 | Truncated incorrect DOUBLE value: 'a' || Warning | 1292 | Truncated incorrect DOUBLE value: 'b' || Warning | 1292 | Truncated incorrect DOUBLE value: 'c' |+---------+------+---------------------------------------+3 rows in set (0.00 sec)

Now let's take a good look at the above example. The result of a + B = c is, which can be interpreted as TRUE in MySQL. Because the result of 'A' + 'B' is 0, c is implicitly converted to 0, therefore, the comparison is actually: 0 = 0, that is, true, that is, 1.

The second important point is to prevent multiple queries or data deletion.

mysql> select * from test;+----+-------+-----------+| id | name | password |+----+-------+-----------+| 1 | test1 | password1 || 2 | test2 | password2 || 3 | aaa | aaaa || 4 | 55aaa | 55aaaa || 5 | 1212 | aaa || 6 | 1212a | aaa |+----+-------+-----------+6 rows in set (0.00 sec)mysql> select * from test where name = 1212;+----+-------+----------+| id | name | password |+----+-------+----------+| 5 | 1212 | aaa || 6 | 1212a | aaa |+----+-------+----------+2 rows in set, 5 warnings (0.00 sec)mysql> select * from test where name = '1212';+----+------+----------+| id | name | password |+----+------+----------+| 5 | 1212 | aaa |+----+------+----------+1 row in set (0.00 sec)

The above example is intended to query the record whose id is 5, and the record whose id is 6 is also queried. What do I want to explain? Sometimes some columns in our database tables are of the varchar type, but the stored value is a string value of a pure number such as '000000'. Some students are not used to quotation marks when writing SQL statements. In this way, some data may be operated more during select, update, or delete operations. So do not forget the quotation marks.

Instructions on converting strings to numbers

mysql> select 'a' = 0;+---------+| 'a' = 0 |+---------+| 1 |+---------+1 row in set, 1 warning (0.00 sec)mysql> select '1a' = 1;+----------+| '1a' = 1 |+----------+| 1 |+----------+1 row in set, 1 warning (0.00 sec)mysql> select '1a1b' = 1;+------------+| '1a1b' = 1 |+------------+|  1 |+------------+1 row in set, 1 warning (0.00 sec)mysql> select '1a2b3' = 1;+-------------+| '1a2b3' = 1 |+-------------+|  1 |+-------------+1 row in set, 1 warning (0.00 sec)mysql> select 'a1b2c3' = 0;+--------------+| 'a1b2c3' = 0 |+--------------+|  1 |+--------------+1 row in set, 1 warning (0.00 sec)

From the above example, we can see that when converting a string into a number, it is actually processed from the left.

1. If the first character of a string is a non-numeric character, convert it to 0.
2. If the string starts with a number
3. If the string contains digits, the number to be converted is the number corresponding to the entire string.
4. If a string contains non-numbers, the converted numbers are the values corresponding to the numbers at the beginning.
   

Summary

The above is all the content of this article. If you have other better examples or have been implicitly converted into pitfalls, please share them. I hope the content of this article will help you in your study or work. If you have any questions, you can leave a message.