PHP ADOdb/PDO equivalent of Perl DBI quote_identifier?

At my current job, the standard practice has been to use mysql_query() and friends directly. When constructing large queries, since our standard deployment is on MySQL, table names just get inserted and surrounded by backticks (partial, fake example):

$sql .= "from `$tablename`";

I'm trying to get away from MySQL-ism's, and as part of that, moving toward PD开发者_开发问答O and/or ADOdb. But, I'm more familiar with Perl than PHP, and I was surprised I couldn't easily find the equivalent of DBI's quote_identifier, which takes either a sole table name, or the whole set of identifying information (catalog, schema, table). Am I overlooking something obvious?

Unfortunately there's nothing in PHP land that compares to the awesome of DBI. PDO is a worthwhile starting point.

Your best bet is not going to be trying to create DB-specific identifier quoting, but to tell the database to follow the standards. Turn on ANSI quotes, meaning you can use double quotes to identify columns and table names. This standard-specified format is accepted by most other databases, including Postgres and SQLite. Some (like MSSQL) also have similar settings to switch to double quotes from a non-standard default.

As a caveat, it means that you'll always have to use single quotes when quoting a string literal value instead of doubles. Also, most identifiers do not have to be quoted, unless they are a SQL keyword or are otherwise reserved by the database.

There are lots of other steps required to make SQL portable. You might want to look at going one step further and actually using an SQL builder or an ORM.

/**
 * @param string|string[]$identifiers
 * @param string $tableName
 * @param string $dbName
 * @return string[]|string
 */
static public function quoteIdentifiers($identifiers, $tableName='', $dbName='' ){
    if( is_array($identifiers) ){
        $result = array();
        foreach( $identifiers as $identifier ){
            $result[] = self::quoteIdentifiers($identifier, $tableName, $dbName);
        }
    }else{
        $result = '`'.str_replace('`','``',$identifiers).'`'; // escape backtick with backtick
        if( $tableName ){
            $result = '`'.$tableName.'`.'.$result;
        }
        if( $dbName ){
            $result = '`'.$dbName.'`.'.$result;
        }
    }
    return $result;
}

usage:

$columns = quoteIdentifiers(array('my col1', 'my col2'), 'table');
$sql = 'SELECT '.join(',', $columns);
$sql=.' FROM '.quoteIdentifiers('table');
=> SELECT `table`.`my col1`,`table`.`my col2` FROM `table`

Bonus (smart quote values, no connection needed!):

/**
 * quote a value or values
 * @param string|string[]|int|int[] $value
 * @return string[]|string
 */
static public function quoteValues($value) {
    if( is_array($value) ){
        $result = array_map(__METHOD__, $value);
    }elseif( $value===true ){
        $result = 'TRUE';
    }elseif( $value===false ){
        $result = 'FALSE';
    }elseif( $value===null ){
        $result = 'NULL';
    }elseif( is_int($value) OR is_float($value) OR  ( is_string($value) AND $value===strval($value*1) ) ){
        $result = strval($value); // no quote needed
    }else{
        $result =  "'".str_replace(
                        array('\\',     "\0",   "\n",   "\r", "'",      '"',    "\x1a"),
                        array('\\\\',   '\\0',  '\\n', '\\r', "\\'",    '\\"',  '\\Z'),
                        strval($value)). "'";
    }
    return $result;
}