db_query() calls db_prefix_tables() in order to automagically change {$foo} to $prefix.$foo in a query string without special case hacks. Unfortunately, db_prefix_tables is called before the preg_replace_callback() call is made. At the time db_prefix_tables is called, the query string can look like:

'SELECT * FROM {%s} WHERE bar LIKE %s'

db_prefix_tables will do its best to find the appopriate prefix for '%s', but because it does not have the full table name to work with, the substitution will always be "$defaultprefix%s". After the regex substitution, this will become "$defaultprefix$foo", instead of "$fooprefix$foo" .

To correct this, in function db_query() of database.inc, move
$query = db_prefix_tables($query);
from line 194 (approximate)
to after
$query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);
(approximately line 200)

This exposes an issue with the menu cache code, about which I will open a separate ticket.

CommentFileSizeAuthor
#1 database.inc__0.diff2.24 KBFetch

Comments

Fetch’s picture

Fetch commented
Title: db_query() in database.inc performs table prefix substitution at logicall incorrect time » db_query() in database.inc performs table prefix substitution at incorrect time, db_prefix_tables has common edge cases, FIXES
Priority: Normal » Critical
StatusFileSize
new database.inc__0.diff2.24 KB

There is a degenerate usage of db_query when variable data is serialized and cached in the database. Serialized data can have rogue { and } characters which should not trigger table prefix expansion.

I solved this problem by moving from using strtr to preg_replace statements in db_prefix_tables(). I set "/ \{([^}])*\}/" to be the regex used (in the general case, more specific searches are used during hash loops), thereby only necessitating a space before the table macro.

I cannot think of a suitable architectural-level fix that would not require refactoring large amounts of the db abstraction, but this fix seems to resolve all of the problems I had with having two sites share all but a few tables (cache*, variable, watchdog). Attached is my version of the database.inc file, with fixes to db_query() as well (see http://drupal.org/node/152747 for this other, related issue).

I apologize for not changing this into a diff form, but I worked on this for 8 hours or so, spacing and debug lines probably abound.

heine’s picture

heine commented
Status: Active » Closed (duplicate)

Thanks.

Marking this a duplicate of http://drupal.org/node/126867, http://drupal.org/node/136837