Drupal 7.76 introduced MySQL 8 support via https://www.drupal.org/project/drupal/issues/2978575.
Before this change, we were able to query custom tables outside of drupal's scope that had special characters in their column names (e.g. spaces, umlauts, brackets). now, all calls to db_insert()/db_update()/db_merge() enforces escapeField() on all column names, stripping every character that is not alphanumeric-plus-underscore, leading to SQLSTATE[42S22]: Column not found: 1054 Unknown column errors for queries that did work before 7.76 (column names in questions were always escaped with backticks in these queries).

meanwhile, doing a db_query() with an INSERT or UPDATE statement still works for the same columns.

I see that most effort was in order to get reserved keywords handled properly with MySQL 8, but I am not sure if abovementioned change was an intended change as well. If someone could clarify it would help a lot, thanks.

Comments

marrrc created an issue. See original summary.

marrrc’s picture

marrrc commented
Issue summary: View changes
marrrc’s picture

marrrc commented
Issue summary: View changes
cilefen’s picture

cilefen commented

Is this a duplicate of #3194119: 7.76 broke db_select, db_insert, db_delete, db_update on non-default databases?

marrrc’s picture

marrrc commented

it is similar, although in that case it revolves around querying tables from straight other databases, and there were issues with escaping when using a prefix.

In our case, the respective tables reside in the same DB, but cant be queries with db_select, db_insert, db_delete, db_update, db_merge as soon as at least one column referenced in the query contains any other chars than alphanumeric-plus-underscore.
e.g. a query that once was built to say SELECT * FROM xyz WHERE `My Column Name (Old)` = 1; is now translated to SELECT * FROM xyz WHERE MyColumnNameOld = 1; because of the new strict escaping, leading to not found SQL errors.

We always already escaped these column names before passing them to e.g. db_select in order to make it work in the first place. Now, since spaces and anything besides the standard alphabet are stripped, it is not a matter of plain escaping anymore, so $conf['mysql_identifier_quote_character'] = ''; (or any other value) does nothing in this case.

Status: Active » Closed (outdated)

Automatically closed because Drupal 7 security and bugfix support has ended as of 5 January 2025. If the issue verifiably applies to later versions, please reopen with details and update the version.