Uploaded image for project: 'phpBB3'
  1. phpBB3
  2. PHPBB3-16985

MYSQLi bug - Incorrect string value: '\xF0\x9F\x8E\x89\xF0\x9F...' for column `phpbb`.`phpbb_privmsgs`.`message_subject`

    XMLWordPrintable

Details

    • Bug
    • Status: Patch Awaiting Review (View Workflow)
    • Major
    • Resolution: Unresolved
    • 3.3.7
    • None
    • None

    Description

      Related to CUSTDB-826 and (tangentially) to PHPBB3-16981.

      When sending a PM with the subject line "Re: Thanks! <img src=abc onerror=alert(1)> 🎉🎉🎉 &amp;quot; &amp;" on my local install, the following error is displayed:

       

      SQL ERROR [ mysqli ]

      Incorrect string value: '\xF0\x9F\x8E\x89\xF0\x9F...' for column `phpbb`.`phpbb_privmsgs`.`message_subject` at row 1 [1366]

      An SQL error occurred while fetching this page. Please contact the Board Administrator if this problem persists.

       

      This is despite using the correct `utf8mb4` encoding for the version of MySQL I'm using.

      The error indicates it comes from mysqli, and swapping out `sql_escape` in `phpbb/db/driver/mysqli.php` so that it uses `utf8_encode_ucr` appears to fix the problem:

       

          function sql_escape($msg)
          {
              return @mysqli_real_escape_string(
                  $this->db_connect_id,
                  utf8_encode_ucr($msg)
              );
      {{    }}}

       

      I don't see any drawbacks to doing this either, as `utf8_encode_ucr` is idempotent for any string that doesn't contain unencoded non-BMP characters, so double-encoding won't be an issue.

      For reasons I mentioned in the comments to CUSTDB-826, per Matthias Bynens PBUH, the ideal solution would be to deprecate/remove `utf8_encode_ucr` entirely and simply fix the problem at-source by only ever using `utf8mb4` encoding... however, running
      `mysqli_set_charset($this->db_connect_id, 'utf8mb4')` seems to do nothing, and in any case `utf8_encode_ucr` is already used in enough places that removing it now would be risky and probably just unnecessarily complicate things. Meanwhile, https://github.com/phpbb/phpbb/pull/6377 should fix PHPBB3-16981 and any more occurrences of the opposite problem (encoded HTML entities being displayed to the user in contexts where the original character should be shown instead).

      PR to follow.

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              luo-ning luo-ning
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated: