CiviCRM Community Forums (archive)

We are moving more and more to supporting machine name columns in our tables, which will facilitate migrating data between instances. Core has a good practice of calling a munge function as a standard way to consistently convert a string so that it is MySQL compatible as a table or field name:
/**
   * given a string, replace all non alpha numeric characters and
   * spaces with the replacement character
   *
   * @param string $name the name to be worked on
   * @param string $char the character to use for non-valid chars
   * @param int    $len  length of valid variables
   *
   * @access public
   *
   * @return string returns the manipulated string
   * @static
   */
  static function munge($name, $char = '_', $len = 63) {
    // replace all white space and non-alpha numeric with $char
    // we only use the ascii character set since mysql does not create table names / field names otherwise
    // CRM-11744
    $name = preg_replace('/[^a-zA-Z0-9]+/', $char, trim($name));

    if ($len) {
      // lets keep variable names short
      return substr($name, 0, $len);
    }
    else {
      return $name;
    }
  }

It would be useful for extensions to have a similar function that could be used for their install and uninstall sql functions. While it is possible to do this by using php to create and run the SQL, it's not perfect.

Wouldn't it be nice to have a MySQL function that could do this for us? I don't think we can rely on being able to have a library installed that provides preg_replace functionality in MySQL (like https://github.com/mysqludf/lib_mysqludf_preg#readme).

However, most of the time there is not a need to convert all characters, just the ones that are commonly found in a label that should be converted to a name. Here is a version that implements a very restricted set of replacements, and could be expanded a bit if this is deemed a useful idea:

mysql> create function CIVICRM_MUNGE (s CHAR(255)) RETURNS CHAR(255) LANGUAGE SQL DETERMINISTIC RETURN LEFT(REPLACE(REPLACE(TRIM(s),' ','_'),'-','_'),63);

mysql> SELECT CIVICRM_MUNGE('hello joe'), CIVICRM_MUNGE('my-variable'), CIVICRM_MUNGE('This doesn''t work!');
+----------------------------+------------------------------+--------------------------------------+
| CIVICRM_MUNGE('hello joe') | CIVICRM_MUNGE('my-variable') | CIVICRM_MUNGE('This doesn''t work!') |
+----------------------------+------------------------------+--------------------------------------+
| hello_joe                  | my_variable                  | This_doesn't_work!                   |
+----------------------------+------------------------------+--------------------------------------+
1 row in set (0.00 sec)

mysql>

A few more REPLACE functions could be added to handle more commonly used characters quite easily. A full MySQL implementation of preg_replace for our simple regex here would be longer but very easy to implement.

This requires the MySQL CREATE ROUTINE privilege. As we already are relying on this privilege for some BETA functionality in logging as well as CREATE VIEW privilege for i18n, I don't think it is out of the question to consider making this a dependency for some extensions, but I would welcome feedback on whether it is commonly available with hosting providers used by CiviCRM installs.

White lists are safer than black lists. Can't the function use RLIKE [_a-z0-9]

If you do this, I suggest writing a test case to ensure that the two munge functions always behave the same way, e.g. pseudocode:

<?php
/**
 * A list of example inputs/outputs for munge()
 * @return array (array(0=>input, 1=>expectedMungeOutput))
 */
function mungeValues() {
  return array(...);
}

/** 
 * @dataProvider mungeValues
 * @param string $input
 * @param string $expected expected munge output
 */
function testMungePHP($input, $expected) {
 $this->assertEquals($expected, CRM_Utils_String::munge($input));
}

/** 
 * @dataProvider mungeValues
 * @param string $input
 * @param string $expected expected munge output
 */
function testMungeSQL($input, $expected) {
 $this->assertEquals($expected, CRM_Core_DAO::singleValueQuery(
    'SELECT civicrm_munge(%1)',
    array(1 => array($input, 'String'))
  ));
}