a symbol that crashed phpbb.com

[johnjohn2011]

Hi, there is a bug - if you attempt to paste the symbol in the second line of the attached text file (or if you paste the entire content), phpbb.com will panic.

[Noxwizard]

That is a limitation of MySQL. You're trying to post a 4 byte character and MySQL didn't add 4 byte UTF-8 support until 5.5.3. We are currently running on the 5.1.x line which is why you see that error.

[johnjohn2011]

Indeed, Centos 5.5 (Final) only has MySQL 5.0.77. Is there any workaround (installing MySQL manually without automatic security updates is not an option). Otherwise huge number of installations will have this problem, because most people may not upgrade away from Centos 5 until 2014, when automatic updates stops.

[Noxwizard]

There's not really a workaround, since MySQL doesn't support it at all and if you filed a ticket, it would just be marked as Not a Bug. According to the MySQL forum, if you disable the strict_trans_table mode, that will make the error go away, but it's probably just going to truncate the value which doesn't solve the problem either. If you really want to use those characters, you're going to have to use a database that fully supports UTF-8.

[johnjohn2011]

We don't use that kind of symbol, but some user discovered this way of crashing phpbb, that made us feel bad about our forum (it's unprofessional at least).

If disabling the strict_trans_table mode can fix this, it'd be a valid workaround. But I can't figure out how to disable the strict_trans_tables mode. SQL Modes is not defined in our my.cnf, and in mysql all the following give empty results:

mysql> SELECT @@GLOBAL.sql_mode;
mysql> SELECT @@SESSION.sql_mode;
mysql> SELECT @@sql_mode;

We tried to login as root and as the mysql user for phpbb, and the above commands all gave empty results.

What else do we need to do to avoid crashing phpbb? We don't mind truncating that kind of symbols.

[AmigoJack]

You could strip any unicode byte sequence having a length of 4 or higher. Open /includes/utf/utf_normalizer.php, find:

Code: Select all

                // Byte at $pos is either a leading byte or a missplaced trailing byte
                if ($utf_len = $utf_len_mask[$c_mask])
                { 

Below, add:

Code: Select all

                    if( $utf_len>= 4 ) {  // Strip for MySQL < 5.3.3
                        $pos+= $utf_len; 
                        $tmp_pos= $pos; 
                        continue; 
                    }
 

Warning: this is only roughly tested and might still lead to other severe problems. Bear in mind that you're dealing with a technical limitation outside the scope of phpBB. Also note that your DBMS does not have to be MySQL.

[Noxwizard]

To clarify, phpBB is not "crashing." The sql server is throwing an error back and phpBB is displaying it since it's a problem that the administrators need to address. If you want to disable that mode, do this:

Open: /includes/db/mysql.php or mysqli.php depending which one you use
Find: (~line 66)

Code: Select all

if (!in_array('STRICT_TRANS_TABLES', $modes))
{
	$modes[] = 'STRICT_TRANS_TABLES';
}

Comment it out or remove it.

[johnjohn2011]

Wonderful! The above worked, although I had to comment all these lines in /includes/db/mysql.php:

// if (!in_array('STRICT_ALL_TABLES', $modes))
// {
// $modes[] = 'STRICT_ALL_TABLES';
// }

// if (!in_array('STRICT_TRANS_TABLES', $modes))
// {
// $modes[] = 'STRICT_TRANS_TABLES';
// }

to get around the problem. (Commenting either part alone still got that error.) Thanks so much!

Now the question is: does disabling the above affect the functionality and security of phpbb in any negative way? Why did phpbb want to enable these modes?

[AmigoJack]

if you disable the strict_trans_table mode, that will make the error go away, but it's probably just going to truncate the value which doesn't solve the problem either. If you really want to use those characters, you're going to have to use a database that fully supports UTF-8.

[johnjohn2011]

That I know, certainly, those stray symbols would be truncated. But that's a hell lot better than throwing that dirty mysql error (to users, that is "crashing").

So apart from that, there is no negative affect in terms of functionality and security?

[Noxwizard]

I really can't say what's going to happen. I don't know enough about those modes or what the side-effects of them are.

[AmigoJack]

Let's have a look to the manuals:

• MySQL 5.5 Reference Manual :: 5.1.7 Server SQL Modes:

  Strict mode controls how MySQL handles input values that are invalid or missing

  So you're only saying to MySQL "I don't care for errors when inserting something". Not having strict mode will result in the following: your text will be inserted from the first character up to the first invalid one (excluding that one, of course), discarding all characters after that invalid one. So if you have a text of 100 characters and the first one is already invalid (which is also considered invalid because of needing 4 bytes or more for UTF-8) then your posting will show up blank, since all the 99 other characters are simply not processed at all.
• MySQL 5.5 Reference Manual :: 9.1.10 Unicode Support:

  MySQL 5.5 supports these Unicode character sets:

  • ucs2, the UCS-2 encoding of the Unicode character set using 16 bits per character
  • utf16, the UTF-16 encoding for the Unicode character set; like ucs2 but with an extension for supplementary characters
  • utf32, the UTF-32 encoding for the Unicode character set using 32 bits per character
  • utf8, a UTF-8 encoding of the Unicode character set using one to three bytes per character
  • utf8mb4, a UTF-8 encoding of the Unicode character set using one to four bytes per character

  Note that prior to 5.5 only UCS-2 and UTF-8 are supported. If you have 5.5 or above you could at least use UTF8mb4 to have 4 byte characters stored.