MySQL Table prefix. [Updated Slavica patch]

I found another application which offers prefixing in such a way that the main code need not concern itself with prefixing. The project is 'phpWebsite (see http://res1.stddev.appstate.edu/horde/chora/co.php/phpwebsite/core/Datab...).

Here are 2 functions (slightly modified) from that file which deliver the desired functionality.

These functions should be moved to database.inc in Drupal, and then some instructions for creating tables with prefixes should be developed. Also, this line should replace the corrpsonding ones in _db_query() in database.mysql and database..pgsql

$result = mysql_query(addTablePrefix($query));

/**
   * Adds the table prefix to the front of the table name
   * Prefix is defined as $conf[db_prefix] in conf.php
   * @author Matt McNaney 
   * @param  string $sql_value SQL query statement
   * @return string $sql_value Sql string with added prefix
   * @access public
  */
function addTablePrefix($sql_value){
  $db_prefix = variable_get("db_prefix", "");
  if ($tbl_prefix){
    $tableName = extractTableName($sql_value);
  
    return str_replace($tableName, $db_prefix.$tableName, $sql_value);
  } else return $sql_value;
}// END FUNC addTablePrefix()


function extractTableName($sql_value){
  $temp = explode(" ", trim($sql_value));
  //  $this->dropNulls($temp);
  if (!is_array($temp))
    return NULL;
  foreach ($temp as $whatever)
    $format[] = $whatever;

    switch (trim(strtolower($format[0]))) {
    case "insert":
    if (stristr($format[1], "into"))
  return preg_replace("/(+.*$/", "", $format[2]);
    else
  return preg_replace("/(+.*$/", "", $format[1]);
    break;
  
    case "update":
  return preg_replace("/(+.*$/", "", $format[1]);
    break;
    
    case "select":
    case "show":
  return preg_replace("/(+.*$/", "", $format[3]);
    break;

    case "drop":
  return preg_replace("/;/", "", $format[2]);
    break;

    default:
  return preg_replace("/(+.*$/", "", $format[2]);
    break;
    }
  }// END FUNC extractTableName

Please note that this would break module SQL additions as they currently are:
mysql -u drupal -p

Sorry - forgot about < escaping the last post.

Please note that this would break module SQL additions as they are currently done with:

mysql -u drupal -p < mymodule/mymodule.mysql

This would need to be changed. I was talking on drupal-dev a little while back about making an automated module installer. That becomes somewhat more necessary all of a sudden with this. Will investigate making a patch for the modules section of the admin site.

forgot to mention that the implementer will have a few queries to clean up in various files. those queires are not properly prefixed by the functions below.

I've updated Slavica patch:

-changed replace function, in old one there was high probability to change every { or } in whole query (now it is changing only {__tablename__})

-replace function shuld always replace table names

Here is whole patch:
http://mozillapl.org/miscellaneous/0081.slavica_minor_fixes_by_gmbh.data...

Why? Did you run into a specific problem with Slavica's patch?

Line 127-135:

function _db_real_tabname( &$sql ) {
  global $db_prefix;

  if ( !isset($db_prefix) ) return;         // short-circuit if not used

  $realsql = strtr($sql, array( "{" => $db_prefix, "}" => "" ));
  
  $sql = $realsql;
}

1. We always need to change {TableName} to $db_prefix_TableName, if not we are querying MySQL with {TableName}, so there is no need to use
"if ( !isset($db_prefix) ) return;".

2. strtr changes every {} occurrence in whole query, lets have following query:

"SELECT id FROM {sequences} WHERE name = '{Testing}'";

it will be changed to:

"SELECT id FROM dbprefix_sequences WHERE name = 'dbprefix_Testing'";

so only table name should be changed.

My proposal:

preg_replace("/\{__([a-z_]+)__\}/", "$db_prefix\\1", $query);

Example:

"SELECT id FROM {__sequences__} WHERE name = '{Testing}'";

it will be changed to:

"SELECT id FROM dbprefix_sequences WHERE name = '{Testing}'";

We still may got problems with {__ __} in query but possibility that someone will use {__ and __} in any input data is significant less.

3. Some minor whitespace cleanup in Slavica patch (old patch added double space after each table name)

4. No need to use another function ("_db_real_tabname"), we need only to change $query in one place (line 117, 124-125 and 154, 160-161)

I just committed a slighly modified version of Slavica's table prefix patch. I removed the redundant check, fixed the spacing issues, renamed the newly introduced function, and udpated the CHANGELOG file. The extra underscores and the regex should not be necessary.

(As a side effect, some of the patches in the patch queue might need to be updated.)

Thanks Slavica!

But it still uses "return strtr($sql, array("{" => $db_prefix, "}" => ""));", and what happens when for example in Article body someone uses "{"? It is changed to $db_prefix, this is datalose error. Please review my updates once again.

We only rewrite the SQL query, not the parameters (data) passed to db_query(). Moreover, the data is inserted into the query after the table names have been prefixed.

Hmmm... I don't wan't to argue but if I'm not wrong (for example):

node.module, line 333:
db_query("UPDATE {node} SET ". implode(", ", $q) ." WHERE nid = '$node->nid'");

database.mysql.inc, line 24:

function db_query($query) {
  $args = func_get_args();
  $query = db_prefix_tables($query);

So we are rewriting _whole_ query and not only table prefix...

You're right GmbH. The issue is currently being discussed on the developers mailing list.

I can see a number of ways to fix this, but I really do not like any of the simple fixes. I could recode node.module's node_save() function to use the same method that comment.module uses in comment_post(). That is, instead of programmatically building the SQL, I could just hard-code the list of fields and the their respective substitutions (i.e. %s or %d) in the db_query. To get this done, that is probably what I will do.

But in researching this problem, I wondered why node.module builds the SQL using the _nodeapi() returned field list, but no other module does this. So I looked at the usage of _nodeapi(). I read some of the postings about it in the archives and looked at how it is currently used. (There is nothing useful in the documentation other than a list of arg values.)

These modules use _nodeapi(): comment, node, queue, statistics and taxonomy.
These modules use _node(): blog, book, forum, page, poll, statistics, story.

It looks like things were half converted to nodeapi(). I actually think that having separate functions is better, instead of a single function like _nodeapi() with a big switch statement. _nodeapi() is counter to good OO practice.

Back to the database itself, the database objects should be handled by a class for each object. Using such a database API would solve this prefix problem, too. There would no longer be any SQL in modules and other places. It would be in each database object's class. The downside is that makes it harder to develop plug-in modules that require additional database objects. Another downside is the additional code executed for each database query -- there would be an additional function call between the module and the database interface, although maybe the database interface could be simplified somewhat and gain back the lost efficiency.

--
Slavica

What is wrong with my proposal?

$num = count($k);
$arr = array_fill(0, $num, "%s");
$s = implode(", ", $arr);
$kk = implode(", ", $k);
$vv = implode(", ", $v);

db_query("INSERT INTO {node} (". $s .") VALUES(". $s .")", $kk, $vv);

Nope, you are correct. I did not think about the quotes.