CDbConnection and braces

Question

Do a small component for my Yii app. Code

$connection = Yii::app()->getComponent('db');
$sql = 'SELECT * FROM {{settings}}';
$command = $connection->createCommand($sql);

raises error

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '{settings}}' at line 1. The SQL statement executed was: SELECT * FROM {{settings}}

Tell me what went wrong. I can't use braces to escape the table name?

Thanks

UPDATE:

It seem this is Yii Query Bulduer bug.

if I use this configuration

'db'=>array(
    'connectionString' => '...',
    'emulatePrepare' => ...,
    'username' => '...',
    'password' => '...',
    'charset' => '...',
    'tablePrefix' => ''
),

pay attention to the empty tablePrefix — all works fine.

Removing this key I get the problem described above. But I don't need table prefix. I just want to use a component that can work with prefixes, not only in my application.

The ability to use empty table prefixes was raised in [issue 1931](https://code.google.com/p/yii/issues/detail?id=1931): this was supposedly fixed in [revision 2780](https://code.google.com/p/yii/source/detail?r=2780), which was [included in v1.1.6](https://github.com/yiisoft/yii/blob/1.1.6/CHANGELOG#L41). I can't explain why it isn't working for you, as `CDbCommand::setText()` [should replace non-null prefixes](https://github.com/yiisoft/yii/blob/1.1.14/framework/db/CDbCommand.php#L172). Are you *absolutely sure* you're using 1.1.14? — eggyal, May 10 '14 at 19:49
maybe a weird question, but why you don't use ``` to escape the table name? like ususally done in MySQL ? — Asped, May 14 '14 at 09:19
@Asped: This is Yii feature: see [Using Table Prefix](http://www.yiiframework.com/doc/guide/1.1/en/database.dao#using-table-prefix). — eggyal, May 14 '14 at 09:29

score 2 · Accepted Answer · edited May 23 '17 at 12:06

Sorry, I've just realised what your update actually says.

if I use this configuration
'db'=>array(
    'connectionString' => '...',
    'emulatePrepare' => ...,
    'username' => '...',
    'password' => '...',
    'charset' => '...',
    'tablePrefix' => ''
),
pay attention to the empty tablePrefix — all works fine.

Yes, of course it does: you have defined tablePrefix to be an empty string (and, as I already pointed out, empty-string prefixes have been supported since v.1.1.6).

Removing this key I get the problem described above. But I don't need table prefix. I just want to use a component that can work with prefixes, not only in my application.

If you don't define tablePrefix, then comparing it against null will be true (and as I also pointed out before, CDbCommand::setText() replaces non-null prefixes):

if($this->_connection->tablePrefix!==null && $value!='')
    $this->_text=preg_replace('/{{(.*?)}}/',$this->_connection->tablePrefix.'\1',$value);

Thus you must either replace braces yourself or else explicitly set tablePrefix to be the empty string. If you have no control over the configuration file, but want to ensure that tablePrefix is set, you could manually override it:

if (!isset($connection->tablePrefix)) $connection->tablePrefix = '';

CDbConnection and braces

1 Answers1