Execute SQL(1), which is created by SQL(2), which is created by script, with mysql command in that script.

改めてやめようと思った出来事でした。
とあるバッチ処理をメンテナンスしていて、もうやめてくれ、と思った事案です。

予め断っておくと、他にやりようがあるのはわかっています。
でも出会ってしまったんだもの。
あと説明のためにあえて他の手段を取ってないというのもあります。

要件としてはinformation_schemaを使って、SQLでSQLを作るというものです。
サンプルとしてWordPressのデータベースを使います。

賛否はあると思いますが、自分もこういうスクリプト書くことがあるので、責めることができません。
いい方法があれば、いいのですが。

順を追って説明します。

スクリプトで作られた、SQLを作るSQLを、スクリプト内でCLI実行する

第1段階：SQLでSQLを作る

SELECT 
    CONCAT('SELECT `', COLUMN_NAME, '` AS \'', COLUMN_NAME, '\' FROM \`wordpress\`;') AS COLUMN_NAME
FROM
    `information_schema`.`COLUMNS`
WHERE
    `TABLE_SCHEMA` = 'wordpress'
    AND `TABLE_NAME` = 'wp_options';

実行結果は以下の通り。

+-----------------------------------------------------------+
| COLUMN_NAME                                               |
+-----------------------------------------------------------+
| SELECT `option_id` AS 'option_id' FROM `wordpress`;       |
| SELECT `option_name` AS 'option_name' FROM `wordpress`;   |
| SELECT `option_value` AS 'option_value' FROM `wordpress`; |
| SELECT `autoload` AS 'autoload' FROM `wordpress`;         |
+-----------------------------------------------------------+
4 rows in set (0.00 sec)

この結果をSQLファイルに書き込んでだりとか。
自分も昔散々書きますし、なんなら今でもExcelとかでINSERT文を作ります。

第2段階：第1段階をスクリプト化してSQLファイルに書き込む

私が見たのはPythonでしたが、今回はPHPで失礼します。

step2.php

<?php
$sql = "
SELECT 
    CONCAT('SELECT \`', COLUMN_NAME, '\` AS \'', COLUMN_NAME, '\' FROM \`wordpress\`;') AS COLUMN_NAME
FROM
    \`information_schema\`.\`COLUMNS\`
WHERE
    \`TABLE_SCHEMA\` = 'wordpress'
    AND \`TABLE_NAME\` = 'wp_options';
";

$command = "MYSQL_PWD=wp_pwd mysql -u wp_user wordpress -s -e \"$sql\" > step2.sql";
exec($command, $output);

実行結果は以下の通りです。

step2.sql

SELECT `option_id` AS 'option_id' FROM `wordpress`;
SELECT `option_name` AS 'option_name' FROM `wordpress`;
SELECT `option_value` AS 'option_value' FROM `wordpress`;
SELECT `autoload` AS 'autoload' FROM `wordpress`;

これはなんの問題もない。そう、ここまでは。

第3段階：第2段階で文字列置換するSQL作りたい

みなさん、どうしますか？

step3.php

<?php
$sql = "
SELECT 
    CONCAT('SELECT REPLACE(\`', COLUMN_NAME, '\` \'\\\'\', \'\') AS \'', COLUMN_NAME, '\' FROM \`wordpress\`;') AS COLUMN_NAME
FROM
    \`information_schema\`.\`COLUMNS\`
WHERE
    \`TABLE_SCHEMA\` = 'wordpress'
    AND \`TABLE_NAME\` = 'wp_options';
";
$command = "MYSQL_PWD=wp_pwd mysql -u wp_user wordpress -s -e \"$sql\" > step3.sql";
exec($command, $output);

実行結果は以下の通りです。

step3.sql

SELECT REPLACE(`option_id` ''', '') AS 'option_id' FROM `wordpress`;
SELECT REPLACE(`option_name` ''', '') AS 'option_name' FROM `wordpress`;
SELECT REPLACE(`option_value` ''', '') AS 'option_value' FROM `wordpress`;
SELECT REPLACE(`autoload` ''', '') AS 'autoload' FROM `wordpess`;

あれ？　シングルクォーテーションがエスケープされていない。
ちょっといじるか。

step3_1.php

<?php
$sql = "
SELECT 
    CONCAT('SELECT REPLACE(\`', COLUMN_NAME, '\` \'\\\\\'\', \'\') AS \'', COLUMN_NAME, '\' FROM \`wordpress\`;') AS COLUMN_NAME
FROM
    \`information_schema\`.\`COLUMNS\`
WHERE
    \`TABLE_SCHEMA\` = 'wordpress'
    AND \`TABLE_NAME\` = 'wp_options';
";
$command = "MYSQL_PWD=wp_pwd mysql -u wp_user wordpress -s -e \"$sql\" > step3_1.sql";
exec($command, $output);

$ php test3_1.php
ERROR at line 2: Unknown command '\''.

あーエスケープしたから、でもエスケープ入れないとSQLなりたたない。
でも、エスケープするとコマンドラインでのエスケープになって。

そして私は考えるのをやめた。

なにがまずいか

この手法自体が間違っているとは言いませんが、正攻法でもないと思います。
私もこの手法で何本もスクリプト作ったことがあるし、色々考えた末にたどり着くものだと思います。

mysqlの-eオプションはSQLを実行するためのものです。
そしてスクリプト内のリテラルはエスケープが必要なものがあります。
そしてさらにコマンドライン引数にもエスケープが必要です。
この2つのエスケープが干渉し合うことでうまく動かない。

そしていつしかバッククォートだらけのスクリプトになって、何をやってるのかわからなくなる。
いつしか考えることをやめます。

このパターンの解決方法（例）

すべてをスクリプト内で実行しようとするからまずいのです。
一旦「SQLを作るSQL」をSQLファイルにして、それをmysqlコマンドに食わせるようにします。

proposal.php

$sql = "
SELECT 
    CONCAT('SELECT REPLACE(`', COLUMN_NAME, '`, \\\"\'\\\", \'\') AS \'', COLUMN_NAME, '\' FROM \`wordpress\`;') AS COLUMN_NAME
FROM
    `information_schema`.`COLUMNS`
WHERE
    `TABLE_SCHEMA` = 'wordpress'
    AND `TABLE_NAME` = 'wp_options';
";
$path = __DIR__ . '/tmp_proposal.sql';
file_put_contents($path, $sql);
$command = "MYSQL_PWD=wp_pwd mysql -u wp_user wordpress -s < $path > proposal.sql";
exec($command, $output);

出来上がるtmp_proposal.sqlは以下のような感じです。

tmp_proposal.sql

SELECT 
    CONCAT('SELECT REPLACE(`', COLUMN_NAME, '`, \"\'\", \'\') AS \'', COLUMN_NAME, '\' FROM \`wordpress\`;') AS COLUMN_NAME
FROM
    `information_schema`.`COLUMNS`
WHERE
    `TABLE_SCHEMA` = 'wordpress'
    AND `TABLE_NAME` = 'wp_options';

教訓

混ぜるな危険（開発が）

もう考えるのがツライ。
みなさんもお気をつけください。