Connection createCommand replaces brackets

Yii 2.0 General Discussions
1

Hi
I am using createCommand function to import from sql files and I have a problem with it replacing [[ and ]] with `
For example:

$comm = Yii::$app->db->createCommand('Test [[x]] test');
echo $comm->rawSql;  // Test `x` test

how can I change this?

2

Hi @eago,

This behavior of changing [[x]] to `x` is meant to be used for quoting a column name.

https://www.yiiframework.com/doc/guide/2.0/en/db-dao#quoting-table-and-column-names

If you want [[x]] as a literal string, I think you could simply quote it.

'[[x]]'
or
"[[x]]"
3

Hi @softark
Ok I see. But it is replacing in values so it breaks the database content. Quoting doesn’t work

$comm = Yii::$app->db->createCommand('INSERT INTO tbl (id, value) VALUES (1, "[[x]]")');
echo $comm->rawSql; //INSERT INTO tbl (id, value) VALUES (1, "`x`")
4

Did you try ‘[[x]]’ ?

6

yes

$comm = Yii::$app->db->createCommand('INSERT INTO tbl (id, value) VALUES (1, "[[x]]")');
echo $comm->rawSql; //INSERT INTO tbl (id, value) VALUES (1, "`x`")

$comm = Yii::$app->db->createCommand("INSERT INTO tbl (id, value) VALUES (1, '[[x]]')");
echo $comm->rawSql; //INSERT INTO tbl (id, value) VALUES (1, '`x`')

$comm = Yii::$app->db->createCommand('INSERT INTO tbl (id, value) VALUES (1, \'[[x]]\')');
echo $comm->rawSql; //INSERT INTO tbl (id, value) VALUES (1, '`x`')
7

Sounds like a bug or a limitation of usage.

A workaround could be value binding.

$comm = Yii::$app->db->createCommand('INSERT INTO tbl (id, value) VALUES (:id, :value)')
$comm->bindValues([ ':id' => 1, ':value' => '[[x]]' ]);
8

this won’t work for me as I am importing several GB sql files. I will just use mysqli for now. But I think there should be a way to execute raw sql without any filter

9

I see.

Probably you have to use yii\db\Command::setRawSql() as a last resort.

$comm = Yii::$app->db->createCommand();
$comm->setRawSql('INSERT INTO tbl (id, value) VALUES (1, "[[x]]")');

https://www.yiiframework.com/doc/api/2.0/yii-db-connection#createCommand()-detail

10

setRawSql worked! thank you
for some reason yii is changing http status to 500 even if everything seems to work fine and I get the right result from the call