Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-23105

Cast number string with many leading zeros to decimal gives unexpected result

Details

    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.

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          elenst Elena Stepanova added a comment -

          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.

          elenst Elena Stepanova added a comment - 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.
          bar Alexander Barkov added a comment -

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

          bar Alexander Barkov added a comment - I think it's a bug. It could strip the leading '0' before actual converting to DECIMAL representation.
          sergeylebedev Sergey Lebedev added a comment - - edited

          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

          sergeylebedev Sergey Lebedev added a comment - - edited 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

          People

            bar Alexander Barkov
            sergeylebedev Sergey Lebedev
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.