[MDEV-23105] Cast number string with many leading zeros to decimal gives unexpected result Created: 2020-07-06  Updated: 2020-10-06  Resolved: 2020-08-06

Status: Closed
Project: MariaDB Server
Component/s: Data types
Affects Version/s: 10.1, 10.2, 10.3, 10.4, 10.5
Fix Version/s: 10.1.48, 10.2.35, 10.3.26, 10.4.16, 10.5.7

Type: Bug Priority: Major
Reporter: Sergey Lebedev Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None


 Description   

Casting numeric representing decimal value string with many leading
zeros into MySQL database gives an unexpected value. For example query:

"SELECT
CAST(0000000000000000000000000000000000000000000000000000000000000000000000000000000020.01
AS DECIMAL(15,2)) as val;"

gives value 9999999999999.99

The bug appears when input string is longer than 83 symbols and is
present in newest MySQL 8.0, maybe in its older version and in MariaDB
also. "83" limit seems to be related to the DECIMAL_MAX_STR_LENGTH
constant from MySQL C sources, when input string is longer than that
constant, MySQL sets resulted value to maximal decimal value with given
precision.

It could be dangerous in many case, for for example for online financial
services that use MySQL. A malicious persons could use this bug for
getting big amount of money on its account by entering sum with many
leading zeros.

 Comments   
Comment by Elena Stepanova [ 2020-08-04 ]

FWIW, it throws not even one, but two warnings while doing so, on all versions of MariaDB and MySQL (the text can be slightly different between different versions):

MariaDB [test]> SELECT
 
    -> CAST(0000000000000000000000000000000000000000000000000000000000000000000000000000000020.01
 
    -> AS DECIMAL(15,2)) as val;
 
+------------------+
 
| val              |
 
+------------------+
 
| 9999999999999.99 |
 
+------------------+
 
1 row in set, 2 warnings (0.001 sec)
 
 
 
MariaDB [test]> show warnings;
 
+---------+------+-------------------------------------------------------------+
 
| Level   | Code | Message                                                     |
 
+---------+------+-------------------------------------------------------------+
 
| Warning | 1916 | Got overflow when converting '' to DECIMAL. Value truncated |
 
| Warning | 1264 | Out of range value for column 'val' at row 1                |
 
+---------+------+-------------------------------------------------------------+
 
2 rows in set (0.000 sec)

If a financial service doesn't pay attention to warnings, it is bound to have dangerous problems.

Still, assigning to bar to check if it needs to be fixed.

Comment by Alexander Barkov [ 2020-08-05 ]

I think it's a bug. It could strip the leading '0' before actual converting to DECIMAL representation.

Comment by Sergey Lebedev [ 2020-08-05 ]

Thank you for fixing the issue!

IMHO the main problem here was that having too long input string it throws number overflow error, not 'Input string is too long' or something like that error. Which leads to a logical bug - too long input string causes wrong output number. And yes - stripping leading zeros in input string is The Right Thing

Generated at Thu Feb 08 09:19:53 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.