実は簡単にできるのでした〜
MyBatisというか、ORM経由でLOAD DATA等の特殊なDMLを実行したかったり、複数行のSQLを1定義で実行したい時って結構あったりしますよね。今回はそれに対応してみます。
情報のおさらい
解説の前に情報が混乱しないよういくつか整理しておきます。
MySQLのLOAD DATAとLOAD DATA LOCALの違い
| 種別 | 挙動 |
|---|---|
| LOAD DATA INFILE | 「リモートに有る」CSV・TSV等をMySQLに直接読み込む |
| LOAD DATA LOCALLOAD DATA LOCAL INFILE | 「ローカルに有る」CSV・TSV等をMySQLに転送して読み込む |
Amazon RDSやCloud SQL等のマネージドサービスの場合はそのDBサーバのファイルシステムに触れないため、基本的に「LOAD DATA」は使用できないので、ほとんどの場合「LOAD DATA LOCAL」使用する事になります。
LOAD DATAはオプション未設定では実行できなくなった
いろいろ書かれていますが、要は以下です。
- 今まで認可処理が無かったから、参照権限さえ持っていればリモートのファイルを読み取れて危なかったぞ。
- クライアントとサーバの両方でLOAD DATA LOCALを許可するオプションを付けないと実行できないようにしたぞ。
- クライアントかサーバのどちらかのオプションが未設定の場合は「ERROR 1148: The used command is not allowed with this MySQL version」エラーを返すぞ。
という事です。サーバ側の設定としては、サーバ側のmy.cnfに「local-infile=1」を設定します。
クライアント側は「mysql --local-infile=1 -hホスト名 -uユーザ名 -p DB名」というようにオプション設定して接続します。
しかしここで一つ問題が生じます。
mysqlクライアントを介さないアプリケーションからLOAD DATAする場合に「--local-infile=1」を指定するにはどうするか？という問題が起きます。
実際のユースケースとしては、単純にJDBCドライバからLOAD DATAするには？という事ですね。
未設定状態のアプリケーションでLOAD DATAすると発生するエラー
以下は、Java, Spring boot, MyBatisのアプリケーションからLOAD DATA LOCALを実行した場合のStackTraceの一部です。
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: The used command is not allowed with this MySQL version at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:93) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73) at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446) at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:294) at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:67) at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:58) Caused by: java.sql.SQLSyntaxErrorException: The used command is not allowed with this MySQL version at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:955) at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:372)
エラーのポイントは「ClientPreparedStatement.execute」でしょうか。「クライアント側でPreparedStatementを実行しようとして失敗した」と言っているわけです。これが正に「--local-infile=1」オプションが無いから発生したエラーなのです。
解決策
では解決策を調べていきます。
結論から言うと、「MyBatisで対応するのではなくJDBCドライバで対応する」形になります。
アプリケーションからはJDBCドライバを使用してMySQLサーバに接続しており、mysqlクライアントを介していません。従って、JDBCドライバで何とかするしかありません。
setLocalInfileInputStreamを使う
ピンポイントでいきなりメソッドが出てきましたが、要はこれが使われれば、「--local-infile=1」を指定したのと同じ状態でMySQLサーバに接続した事になります。
しかしこのご時世、直接JDBCドライバのメソッドを呼びたくありません。
対応するJDBCのオプションが有る
JDBCドライバにはズバリそれに該当するオプションがあるのです。
allowLoadLocalInfile
Should the driver allow use of 'LOAD DATA LOCAL INFILE...'?
Default: false
Since version: 3.0.3
allowUrlInLocalInfile
Should the driver allow URLs in 'LOAD DATA LOCAL INFILE' statements?
Default: false
Since version: 3.1.4
LOAD DATAをファイルパス指定する場合は「allowLoadLocalInfile」を、URL指定する場合は「allowUrlInLocalInfile」を有効にするだけで対応できます。
このオプションは以下のようにURL属性で指定します。
jdbc:mysql://${host}:${port}/${schema}?allowLoadLocalInfile=trueこれで無事、mysqlクライアントを介さずにアプリケーションから直接LOAD DATA LOCAL INFILEを実行できるようになります。
allowLoadLocalInfileは何故デフォルト値がfalseで無効なのか？と一瞬思いますが、冒頭のMySQLの公式リファレンスのセキュリティ問題に合わせ、初期値は無効にしていると思われます。
おまけ
MyBatisのmapperのxml内で複数のステートメントを記述したい
例えば以下のようにセミコロンが複数ある、つまり複数ステートメントをMyBatisのmapperのxmlで、1度のSQLで実行してみます。
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="hoge"> <select id="hoge"> select 1; select 2; </select> </mapper>
すると以下のエラーが発生します。
### Cause: java.sql.SQLSyntaxErrorException: 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 'select 2' at line 2 ; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: 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 'select 2' at line 2 at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:234) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73) at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446) Caused by: java.sql.SQLSyntaxErrorException: 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 'select 2' at line 2 at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:955) at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:372)
これもJDBCドライバのオプションが問題で、「allowMultiQueries」の初期値がfalseなので、シンタックスエラー扱いになっています。
allowMultiQueries
Allow the use of ';' to delimit multiple queries during one statement (true/false). Default is 'false', and it does not affect the addBatch() and executeBatch() methods, which rely on rewriteBatchStatements instead.
Default: false
Since version: 3.1.1
allowMultiQueries=trueをJDBCドライバのURLに指定すれば複数ステートメントの実行は可能になります。
しかしこれを有効にすると、SQLインジェクションを行う際に複数ステートメントが実行できてしまい、最悪沢山の攻撃を1度に受けてしまう可能性があります。もしallowMultiQueries=falseであれば、仮に1度に複数ステートメントを実行しようとしても、syntax error扱いにする事ができ、多少安全になります。
また、複数ステートメントを許可してしまうと、便利だからと使いがちになり、1定義の責務を増やしてしまうのと同時に共通化の妨げにもなります。
以上を考慮したうえでallowMultiQueriesを有効にするか決めたいですね。
雑感
この問題は他のORMを使っても起き得る問題なので、覚えておくと便利そうです。
なお、LOAD DATA LOCAL INFILEは暗黙的なコミットを発生させずトランザクションが有効になるので、バッチ処理等で積極的に使っていきたいですね！
dev.mysql.com
