Summary of Implicit Type Conversion for MySQL

Preface

I read an article the other day: Implicit type conversion for MySQL, worth millions, feels good, and I didn't know it very well before, so I cleaned it up.Hope to help you all.

When we compare different types of values, MySQL makes some implicit transformations (Implicit type conversion) to make these values "comparable" (also known as type compatibility).

Examples include the following:

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

Clearly, the implicit transformation occurred during the execution of the SQL statement above.And you can tell from the results that in Article 1, SQL, the "1" of the string is converted to the number 1, whereas in Article 2, SQL, the number 2 is converted to the string "2".

MySQL also provides the CAST() function.We can use it to explicitly convert values to strings.When used CONCA() Implicit conversion may also occur when a function is used, because it expects parameters to be in the form of strings, but what if we are not passing strings?

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 transformation rules

The rules for implicit transformation in official documents are described below:

If one or both arguments are NULL, the result of the comparison is NULL, except 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 performed. 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.

To translate into Chinese is:

When at least one of the two parameters is NULL, the comparison results are NULL, with the exception of using < = > A comparison of two NULLs returns 1, neither of which requires a type conversion
Both parameters are strings and will be compared by strings without type conversion
Both parameters are integers, compared as integers, no type conversion
When comparing hexadecimal values to non-numbers, they are treated as binary strings
One parameter is TIMESTAMP or DATETIME, and the other parameter is constant, which is converted to timestamp
One parameter is of type decimal. If the other parameter is decimal or an integer, the integer is converted to decimal for comparison. If the other parameter is a floating point number, the decimal is converted to a floating point number for comparison.
In all other cases, the two parameters are converted to floating point numbers and compared

Points of Attention

Security Question: If the password type is a string, the query condition int 0 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)

Believe the above example, a few smart students can find that the above example can also be used as sql injection.

Assuming that the site is frustrated with the login block, use the following method:

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

If username enters yes a' OR 1='1 Then password freely enters, which generates the following query:

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

It is possible to log in to the system.In fact, if an attacker has read this article, he or she can log in using implicit transformation.The following:

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 reasons for this are as follows:

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 a few 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 "aa" is not the same type as the number 1, the official MySQL document tells us:

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

Looking at warnings, you can see that implicit conversion converts the string to double type.But because the string is non-numeric, it is converted to zero, so the final calculation is 0+1=1

The examples above are of different types, so there is an implicit transformation, so what if we use the same type of value to do the operation?

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 little depressing?

This happens because + is the arithmetic operator arithmetic operator, which explains why both a and b are converted to double.Because after the conversion it is: 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 it looks like you have a good understanding of the above examples.a+b=c result is 1, 1 can be interpreted as TRUE in MySQL because'a'+'b'result is 0, c will also be implicitly converted to 0, so the comparison is: 0=0 is true, which is 1.

The second important thing to note is to prevent multiple queries or deletions of data

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

0

&8203;The example above was meant to query the record with id 5. The result also queries the record with id 6.What do I want to say?Sometimes some columns in our database tables are of type varchar, but the stored value is a string value of pure numbers like'1123'. Some students are not accustomed to quoting when writing sql.In this way, when select, update, or delete is performed, it is possible to manipulate a little more data.So don't forget where you should put quotation marks.

Some instructions on string to number

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

1

From the example above, you can see that when you convert a string to a number, it is actually processed from the left.

If the first character of a string is a non-numeric character, converting to a number is 0 If the string begins with a number If all strings are numbers, converting to numbers is the number corresponding to the entire string If there are non-numbers in the string, the converted numbers are the values corresponding to the first ones

summary

That's all there is in this article. If you have other, better examples, or situations that have been implicitly transformed, share them.I hope that the content of this article can help you in your study or work. If you have questions, you can leave a message to exchange.