Bug: double prefixing with db_prefix array about drupal HOT 5 CLOSED

Hello,

The same problem occured on a website I maintain during an update. Sharing the user table, the impact is important as nobody can authenticate after the update.

As this kind of settings (mutliple prefix) is not so often met, I understand it's just a regression introduced while making the CMS compatible with MySQL 8+ which ask to escape its keywords.

Here is a patch I developed and that works for the website I maintain:
double_table_name_prefixing_fix.zip

from drupal.

Now it's removing pipes from some string arguments in a query of mine. Not the best way to solve this. ;D

I've replaced the lower part of the function with the following, which I believe fixes the original issue, while not breaking pipes in the rest of the query.

  // Add prefixes and transform curly braces into brace/pipes in order to mark already done operations
  // while preserving table name encapsulation for the next step.
  foreach ($db_prefix_local as $table_name => $prefix) {
    $sql = strtr($sql, array('{' . $table_name . '}' => '{|' . $prefix . $table_name . '|}'));
  }
  $sql = strtr($sql, array('{' => '{|' . $default_replacement, '}' => '|}'));

  // For MySQL 8, escape table names corresponding to reserved keywords.
  if (db_version_compare('mysql', '8.0.0')) {
    foreach ($db_mysql8_reserved_keywords as $keyword) {
      $sql = str_replace('{|' . $keyword . '|}', '`' . $keyword . '`', $sql);
    }
  }

  // Remove curly brace/pipes.
  return strtr($sql, array('{|' => '', '|}' => ''));

from drupal.

Sorry for the regression.

For the fix, something totally different from/not including curly braces must be used as a replacement. If no, the second strtr will also modify previously modified curly braces.

Maybe triple pipes instead of single pipes? A quick search let me think it's not used in SQL (while double pipes may).
Other possibilities:

1. find another good character to replace the single pipes
2. (use regexp to match well the strings to replace - too heavy for that treatment, isn't it?)
3. introduce a first strtr to double curly braces and then, reduce them to single curly braces in the other calls to strtr

from drupal.

Fix tried with the third solution proposed:

  // Double curly braces in order to be able to mark already done operations
  // while preserving table name encapsulation for the next step,
  // adding prefixes and then, escaping MySQL 8 reserved keywords.
  $sql = strtr($sql, array('{' => '{{' , '}' => '}}'));

  // Add prefixes.
  foreach ($db_prefix_local as $table_name => $prefix) {
    $sql = strtr($sql, array('{{' . $table_name . '}}' => '{' . $prefix . $table_name . '}'));
  }
  $sql = strtr($sql, array('{{' => '{' . $default_replacement, '}}' => '}'));

  // For MySQL 8, escape table names corersponding to reserved keywords.
  if (db_version_compare('mysql', '8.0.0')) {
    foreach ($db_mysql8_reserved_keywords as $keyword) {
      $sql = str_replace('{' . $keyword . '}', '`' . $keyword . '`', $sql);
    }
  }

  // Remove curly braces.
  return str_replace(array('{', '}'), '', $sql);

And the corresponding patch:
db_prefix_tables_fix.zip

Haven't tried it but seems good...

from drupal.

from drupal.