はじめに
この記事は弊社アドベントカレンダー向けの小ネタ記事です。業務で久しぶりにWindowsバッチを書いてハマったことについて書きます。
ここで話すWindowsバッチとは？
プレーンなバッチ、つまりコマンドプロンプトで動作する拡張子.batのファイルのことを話します。現在のWindows10ではコマンドのバッチ処理機能として、PowerShell(拡張子.ps1)やWSLによるLinuxシェル(拡張子.sh)も使えますが、今回は最も古くて不親切な歴史あるコマンドプロンプトのバッチ処理についてです。
余談
今から新規でWindowsのバッチ処理を書くなら、PowerShellあるいは、WSLを使いLinuxシェルで書くことをオススメします。PowerShellはWindows.NETの機能を呼び出せ、高機能です。LinuxシェルならWindows10でも、MaxOSXでも、もちろん、Linuxでも動作するものが書けます。
今回はWindows Server 2008 というWSLが使えない遺物歴史ある環境で動作する必要があったのでLinuxシェルは採用できませんでしたし、私がPowerShellに慣れていない止むに止まれぬ事情もあっての話です。
本編
ある日、「フォルダにあるSQLファイルをすべて実行したい」と考えて、私は以下のようなバッチを書きました。
C:\tmp\sql_filesフォルダ内の.sqlファイルをfor文で処理して、sqlcmdでSQLファイルを実行するものです。sqlファイルの実行と一緒にバックアップ等の別処理もしたかったのでexecute_sqlサブルーチンに分けて書きました。以下がコードです（説明用のモックなのでsqlcmdは実行しません）。
@echo off
set DBHOST=hogeserv
set DBNAME=HOGEDB
set DBUSER=hoge
set PASSWD=hoge!hoge!
echo Start batch.
for %%f in (c:\tmp\sql_files\*.sql) do (
set FILE=%%f
call :execute_sql %DBHOST% %DBNAME% %DBUSER% %PASSWD% %FILE%
)
goto :END
rem --------------------------------------------------------------
:execute_sql
rem --------------------------------------------------------------
set DBHOST=%1
set DBNAME=%2
set DBUSER=%3
set PASSWD=%4
set FILE=%5
echo Run sql file : %FILE% (DBHOST:%DBHOST%, DBNAME:%DBNAME%, DBUSER:%DBUSER%, PASSWD:%PASSWD%)
rem [バックアップ等を含んだSQL実行処理（省略）]
rem 以下のように sqlcmd でファイルやクエリを実行する処理の組み合わせです。
rem sqlcmd -U %DBUSER% -P %PASSWD% -S %DBHOST% -d %DBNAME% -i %FILE%
exit /b 0
rem --------------------------------------------------------------
:END
echo Finsh batch.
それで結果はどうなったかというと問題が発生しました。
問題１：ファイル名がとれない
Start batch.
Run sql file : (DBHOST:hogeserv, DBNAME:HOGEDB, DBUSER:hoge, PASSWD:hoge!hoge!)
Run sql file : (DBHOST:hogeserv, DBNAME:HOGEDB, DBUSER:hoge, PASSWD:hoge!hoge!)
Run sql file : (DBHOST:hogeserv, DBNAME:HOGEDB, DBUSER:hoge, PASSWD:hoge!hoge!)
Finsh batch.
ファイル名が取れません。ターゲットのフォルダ(c:\tmp\sql_file)には、3つのSQLファイル(query1.sql ～ query3.sql)があります。3回サブルーチンが呼ばれているので、ファイルの数と呼び出し回数は一致しますが、肝心のSQLファイルの名前が取れていません。
問題１解決策：遅延変数展開を使う
結論としては遅延変数展開というものを使います。バッチのfor文はfor～do()までが、1つのコマンドです。1つのコマンドということは実行時に全ての変数が確定してしまいます。つまり、forコマンドのdo()でsetしているFILE変数も実行時に値が確定します。forコマンド実行時には%%fは空ですので、結果としてFILE変数には何も入らず、ファイル名が取得できません。
これを解決するために、遅延変数展開を使います。
遅延変数展開はその名の通り、実際に実行するまで変数の値を展開しないで遅らせるというものです。これを使うと、forコマンドのdo()でサブルーチンを呼ぶまで、変数FILEを確定しないので、期待通りにファイル名が取れます。
遅延変数展開を有効にするには有効にしたいコマンドを含むスコープの先頭にsetlocal enabledelayedexpansionを書きます。スコープの終了ではendlocalを実行します。遅延変数展開をしたい変数は%変数名%の代わりに!変数名!を使います。修正したfor文は次の通りです。
setlocal enabledelayedexpansion
for %%f in (c:\tmp\sql_files\*.sql) do (
set FILE=%%f
call :execute_sql %DBHOST% %DBNAME% %DBUSER% %PASSWD% !FILE!
)
endlocal
さて、これでファイル名が取れるようになりました。
Start batch.
Run sql file : c:\tmp\sql_files\query1.sql (DBHOST:hogeserv, DBNAME:HOGEDB, DBUSER:hoge, PASSWD:hoge)
Run sql file : c:\tmp\sql_files\query2.sql (DBHOST:hogeserv, DBNAME:HOGEDB, DBUSER:hoge, PASSWD:hoge)
Run sql file : c:\tmp\sql_files\query3.sql (DBHOST:hogeserv, DBNAME:HOGEDB, DBUSER:hoge, PASSWD:hoge)
Finsh batch.
問題２：パスワードがバグる
という訳でこれでDBに正しくSQLファイルを投げられそうですが・・・、ちょっとまて！これではDBに接続できない！！
見出しバレしていて、若干スベっている気がしますが、パスワードが本来はhoge!hoge!であるところが、hogeになっています。どうやら、パスワードに含まれる「!」が遅延変数展開の!変数名!と勘違いされたようです。つまり、文字列hoge!hoge!はhogeと!hoge!変数に解釈されたということです。!hoge!なんて宣言していないので空になってPASSWD:hogeと表示されました。ナンテコッタ。パスワードに変な記号をいれるのってどうなのよ？それでセキュリティ強度があがると思っ
問題２解決策：遅延変数展開のスコープを正しく設定する
原因は遅延変数展開を使われて欲しくない箇所で使われたことにあります。まず、forコマンドのPASSWD変数です。これが%PASSWD%だと、forコマンド実行時に値が展開されてしまいますので、サブルーチン呼び出しはcall :execute_sql hogesrv HOGEDB hoge hoge!hoge! !FILE!と変数の値を直接書いてあるのと同じになり、hoge!hoge!の後ろが遅延変数とみなされてしまうので、これを!PASSWD!に変更します。これでhoge!hoge!はサブルーチン呼び出し時に解釈され、変数として解釈されることはなくなります。（あわせて、今後、事故が起こると嫌なので、他のサブルーチンに渡す変数も一緒に変えておきます。）
setlocal enabledelayedexpansion
for %%f in (c:\tmp\sql_files\*.sql) do (
set FILE=%%f
call :execute_sql !DBHOST! !DBNAME! !DBUSER! !PASSWD! !FILE!
)
endlocal
続いて、サブルーチンの中ではhoge!hoge!の後ろを遅延変数にしたくないので、遅延変数展開を無効化します。遅延変数展開を無効にするにはスコープの先頭でsetlocal disabledelayedexpansionを書きます。スコープの終了はendlocalです。
サブルーチンは次のようになりました。
rem --------------------------------------------------------------
:execute_sql
rem --------------------------------------------------------------
setlocal disabledelayedexpansion
set DBHOST=%1
set DBNAME=%2
set DBUSER=%3
set PASSWD=%4
set FILE=%5
echo Run sql file : %FILE% (DBHOST:%DBHOST%, DBNAME:%DBNAME%, DBUSER:%DBUSER%, PASSWD:%PASSWD%)
rem [バックアップ等を含んだSQL実行処理（省略）]
rem 以下のように sqlcmd でファイルやクエリを実行する処理の組み合わせです。
rem sqlcmd -U %DBUSER% -P %PASSWD% -S %DBHOST% -d %DBNAME% -i %FILE%
endlocal
exit /b 0
rem --------------------------------------------------------------
さて、これで実行すると、次のような結果になりました。期待通りです！！
Start batch.
Run sql file : c:\tmp\sql_files\query1.sql (DBHOST:hogeserv, DBNAME:HOGEDB, DBUSER:hoge, PASSWD:hoge!hoge!)
Run sql file : c:\tmp\sql_files\query2.sql (DBHOST:hogeserv, DBNAME:HOGEDB, DBUSER:hoge, PASSWD:hoge!hoge!)
Run sql file : c:\tmp\sql_files\query3.sql (DBHOST:hogeserv, DBNAME:HOGEDB, DBUSER:hoge, PASSWD:hoge!hoge!)
Finsh batch.
まとめ
今回はWindowsバッチのforコマンドで遅延変数展開というものについて書きました。バッチの魔境とか呼ばれていたりしているだけあって、なかなかに癖がありますが、それでもバッチ処理は手軽に使える自動化の手段です。業務では例にあげたバッチを使って、新旧DBのデータ同期化を実現して、テストを効率化できました。そもそもパスワードに記号を含めなければ、ここまでハマることはなかったのかもしれませんが・・・
おまけ
遅延変数展開の悪夢のような興味深い動作を学べるように動作するサンプルの全体を書いておきます。動かすと理解がすすみますよ！
@echo off
set DBHOST=hogeserv
set DBNAME=HOGEDB
set DBUSER=hoge
set PASSWD=hoge!hoge!
echo Start batch.
setlocal enabledelayedexpansion
for %%f in (c:\tmp\sql_files\*.sql) do (
set FILE=%%f
call :execute_sql !DBHOST! !DBNAME! !DBUSER! !PASSWD! !FILE!
)
endlocal
goto :END
rem --------------------------------------------------------------
:execute_sql
rem --------------------------------------------------------------
setlocal disabledelayedexpansion
set DBHOST=%1
set DBNAME=%2
set DBUSER=%3
set PASSWD=%4
set FILE=%5
echo Run sql file : %FILE% (DBHOST:%DBHOST%, DBNAME:%DBNAME%, DBUSER:%DBUSER%, PASSWD:%PASSWD%)
rem [バックアップ等を含んだSQL実行処理（省略）]
rem 以下のように sqlcmd でファイルやクエリを実行する処理の組み合わせです。
rem sqlcmd -U %DBUSER% -P %PASSWD% -S %DBHOST% -d %DBNAME% -i %FILE%
endlocal
exit /b 0
rem --------------------------------------------------------------
:END
echo Finsh batch.