Connector/J 5.1とServer Side Prepared Statement

ここ数日「MySQL + Connector/J(JDBCドライバ) + プリペアードステートメント」の話題がちらほら出ています。正確に把握はしていないですがSQLインジェクション対策→PreparedStatementという流れできた話のようです。

• 徳丸浩の日記 - JavaとMySQLの組み合わせでUnicodeのU+00A5を用いたSQLインジェクションの可能性
• へぼへぼCTO日記 - useServerPrepStmtsを使うのが根本解決だとはおもう。けど…？
• id:kazuhookuのメモ置き場 - MySQL+Java でサーバサイドプリペアードステートメントを使うべきで「ない」理由

自分は元Connector/J開発メンバ(※インターン生として)でもありとても気になる話題なので、Connector/Jのソース解析も含めた説明をここで行いたいと思う。

プリペアードステートメントとは？

基本的な説明は割愛。RDBMSの世界では元々は毎回のSQL文のパース、コンパイルといった処理を軽くすることで性能向上を実現するために導入された仕組み。最近ではSQLインジェクション対策の面で注目もされている機能。

client-sideとserver-side

Connector/JでのPrepared Statementには2種類のモードが存在する。

• client-side Prepared Statement
• server-side Prepared Statement

client-sideとは、その名の通りMySQLサーバから見たクライアント相当であるConnector/J自身により実現しているPrepared Statement。

Connector/JはJDBC Type4 Driver(=Pure Javaで実装されている)であり、Connector/J自身にはSQL文のパース機能等は無い。従って、client-sideのPrepared Statementでは、単純なプレースホルダ機能のみが実際には提供されている。要するに、"?"の部分に引数を文字列連結させて、それをMySQLサーバに投げているということ。

これは、MySQLサーバがver4.1になるまでPrepared Statement機能を提供していなかったことにも関係している(と推測)。

一方、Connector/Jは(正確な記憶はないが)かなり昔からJDBC 3.0をサポートしており、Prepared StatementはJDBC 2.0の仕様なので、その都合上、MySQLサーバ側にPrepared Statementが無い時期からAPIとして提供するためにclient-side実装を行っていたものと考えられる。

一方、server-sideのPrepared StatementはMySQL 4.1でサポートされた機能であり、サーバ側でSQL文のパースをスキップできる、(他のRDBMSと比べるとかなり簡潔だが)いわゆるPrepared Statementとなっている。

プロトコルの違い

client-side Prepared Statementでは通常のSQL文を実行するのと同じ(Java的に言うと、java.sql.Statementを用いた実行と同じ)プロトコルが用いられる。

server-side Prepared Statementでは専用のプロトコル(正確には専用のコマンド)を使用する。

MySQL Internalsドキュメントにclient-serverプロトコルの解説がある。

• http://forge.mysql.com/wiki/MySQL_Internals_ClientServer_Protocol

SQLの実行にはCommand Packetを使用するが、

• 通常のSQL文(client-side Prepared Statement含む)ではCOM_QUERYコマンドを使用
• server-side Prepared StatementではCOM_STMT_PREPARE、COM_STMT_EXECUTE、COM_STMT_CLOSEなどを使用

といったような違いがある。

ちなみにこうしたSQLCOMコマンドの違いは一般クエリログにも出力されるので、それでも確認ができる。

SQLインジェクション対策

server-side Prepared StatementではSQL文のフォーマットとパラメータ値がこのように別々のPacketを使用して送信される仕組みとなっており、これによりSQLインジェクションを防ぐことが可能になっている(と思われる)。

Connector/Jにおける各機能の実装クラス

次にConnector/JにおけるPrepared Statement関連のクラスを説明する。

関連クラス一覧

• com.mysql.jdbc.Connection
• com.mysql.jdbc.ConnectionImpl
• com.mysql.jdbc.Statement
• com.mysql.jdbc.StatementImpl
• com.mysql.jdbc.PreparedStatement
• com.mysql.jdbc.ServerPreparedStatement

Connection

java.sql.Connectionインタフェースを拡張したインタフェース。

ConnectionImpl

com.mysql.jdbc.Connectionインタフェースを実装したクラス。

Statementオブジェクト(通常のSQL文用)の作成はConnectionオブジェクトを通じて実行する。

Statement stmt = conn.createStatement("SQL文");

PreparedStatementオブジェクトの作成も同様にConnectionオブジェクトを通じて行うことになる。

PreparedStatement pstmt = conn.prepareStatement("SQL文");

Statement

java.sql.Statementインタフェースを拡張したインタフェース。

StatementImpl

com.mysql.jdbc.Statementインタフェースを実装したクラス。

PreparedStatement

com.mysql.jdbc.StatementImplクラスを拡張したクラス。これがclient-side Prepared Statementの時に使われる。

ServerPreparedStatement

com.mysql.jdbc.PreparedStatementクラスを拡張したクラス。これがserver-side Prepared Statementの時に使われる。Connector/Jの歴史的にclient-sideの実装が先なので、こういう継承関係になっていると思われる。

抑えておきたいところ

次の話に進む上で抑えておきたいのは、server-sideのPrepared Statementは"Statment←StatementImpl←PreparedStatement←ServerPreparedStatement"という継承関係があるということと、これらのStatement系オブジェクトはConnectionオブジェクトから作られるということである。

Connector/Jのパラメータとclient-side/server-side制御方法

Connector/J 5.0/5.1のデフォルト設定では、server-sideではなくclient-sideのPrepared Statementが使用されるようになっている。

この振る舞いを制御するためのJDBC接続文字列用パラメータがuseServerPrepStmtsである。

通常のJDBC文字列同様、以下のように使用する。

String url = "jdbc:mysql://localhost/test?useServerPrepStmts=false";

もちろん、Propertyオブジェクトにputしても良い。

server-side Prepared Statementの是非とuseServerPrepStmtsパラメータ

このuseServerPrepStmtsパラメータはConnector/J 3.1.0にてデフォルト値はtrueで登場したものの、Connector/J 5.0.5/5.1にてfalseとなった。

このデフォルト値変更の理由はConnector/J開発者Mark Matthews氏がConnector/J 5.0.5をリリースした際のRelease Noteに記述されている。(2007年3月　http://lists.mysql.com/java/9035)

Important change: Due to a number of issues with the server's implementation of
server-side prepared statements, Connector/J 5.0.5 has disabled their use by defa

The disabling of server-side prepared statements does not affect the operation of the
connector in any way, except for the case where the connection was configured with
"useTimezone=true". If so, see the bugfix note for "useSSPSCompatibleTimezoneShift".

To enable server-side prepared statements you must add the following configuration
property to your connector string: 

useServerPrepStmts=true

The default value of this property is false (i.e. Connector/J does not use server-side
prepared statements, and won't until they've matured). If you have an application that
already works well with server-side prepared statements, it is reasonable to enable them.

簡単にまとめると、

• いろいろサーバ側のPrepared Statement実装には問題がある
• 枯れてくるまでデフォルト値=falseにしておく
• 既にserver-side Prepared Statementを使っていて問題がでてないならtrueにして使ってもOK

という内容。

「サーバ側実装に問題がある」の具体的な内容が触れられていないので、そのバグがMySQLサーバのバージョンで既に直ったのかとかは不明。

またMySQLのアーキテクチャ担当責任者であるBrian Aker氏は、Drizzleの機能説明において以下のように触れている。（2008年6月 http://krow.livejournal.com/599921.html）※DrizzleではPrepared Statementはサポートされない予定。

So why are prepared statements a problem?

Because users do not clean up/close unused prepared statements. 
Multiply the number of prepared statements times the number of 
open connections and you begin to see the problem.

What do you do about this?

Turn them off. The Java driver and a few other drivers turn them off automatically.

• Prepared Statementが問題となるのはプログラマがclean up/closeをしてくれないから
• clean up/closeしないコードが多数のConnectionによって実行されると問題が出る(補足：メモリリークやmysqldダウン)
• Connector/Jとか、デフォルトでOFFにしているものもあるし

Drizzleは"文字コードはutf8のみ対応、他は無し"にしたりするような、シンプルさを追求するRDBMSなので「Prepared Statementを切る」というのは理解できる。

しかしBrianが言うところの理由は一言で言うと「プログラマがちゃんとcloseしてくれないから」ということであり、

• つまり利用者が悪い
• とは言うものの製品提供側はトラブルの種を極力少なくしたいので機能OFFで回避(MySQLではちゃんと使える人だけONにすべし)

ということなのではと思う。

PreparedStatement.close()に関連するソースを読んでみる

Connectionオブジェクトのみならず、Statementオブジェクト、PreparedStatementオブジェクトについても、はたまたResultSetオブジェクトについても、不要となったらclose()を呼ぶのはJDBCプログラミングでは基本だと思うが(新入社員研修で習った記憶あり)、それがなかなか徹底されないと言うことらしい。

となると「PreparedStatement.close()を必ず呼べば解決するはず」というのも難しいので、いくつか調べてみた。

先に結論を述べると、

• Connection.close()が呼ばれれば、ServerPreparedStatement.close()も呼ばれ、MySQLサーバにCOM_STMT_CLOSEコマンドが送信され、メモリリークは発生しない

ということが判明した。

以下、結論に至る説明を記す。

JDBCプログラミングではPreparedStatemtを利用するためには以下のようにConnectionクラスのprepareStatementメソッドを使用する必要がある。

java.sql.PreparedStatement pstmt = conn.prepareStatement("XXXX");

このConnectionオブジェクト生成時にJDBC接続文字列に"useServerPrepStmts=true"が指定されていれば、prepareStatementメソッドの戻り値はcom.mysql.jdbc.ServerPreparedStatementオブジェクトとなる。

ServerPreparedStatementはインスタンス化される際、コンストラクタの中で、親クラスであるPreparedStatementのコンストラクタを呼び出す。

PreparedStatementのコンストラクタは、さらにその親のStatementImplクラスのコンストラクタを呼び出す。

StatementImplのコンストラクタは、dontTrackOpenResourcesパラメータがfalse(=デフォルト値)の場合、ConnectionクラスのregisterStatemet()メソッドを使用して、Connectionオブジェクトに自身を登録する。

    if (!this.connection.getDontTrackOpenResources()) {
        this.connection.registerStatement(this);
    }

このようにして、Connectionオブジェクトには生成済みのStatement/PreparedStatement/ServerPreparedStatementが登録されている。

ここで、Connectionがclose()されるとどうなるか。

1. Connection.close()
2. Connection.realClose()
3. Connection.closeAllOpenStatements()

という順序で呼び出しが行われ、closeAllOpenStatements()内でIterator経由でServerPreparedStatement.close()も呼ばれる仕組みになっている。

for (Iterator iter = this.openStatements.keySet().iterator(); iter.hasNext();) {
    currentlyOpenStatements.add(iter.next());
}
int numStmts = currentlyOpenStatements.size();
for (int i = 0; i < numStmts; i++) {
    StatementImpl stmt = (StatementImpl) currentlyOpenStatements.get(i);
    try {
        stmt.realClose(false, true);
    } catch (SQLException sqlEx) {
        postponedException = sqlEx; // throw it later, cleanup all
        // statements first
    }
}		

そしてServerPreparedStatement.close()にて、MySQLサーバにCOM_CLOSE_STATEMENTコマンドを送信、となる。

MysqlIO mysql = this.connection.getIO();
Buffer packet = mysql.getSharedSendPacket();
packet.writeByte((byte) MysqlDefs.COM_CLOSE_STATEMENT);
packet.writeLong(this.serverStatementId);
mysql.sendCommand(MysqlDefs.COM_CLOSE_STATEMENT, null,
    packet, true, null, 0);

結論:ではJava+MySQLではServerSidePreparedStatementを使うべきか

というわけでだらだら書いてしまったがここいらで結論。

使うべきか使うべきでないかのYes/Noで言うと「微妙」としか言えない。

使うべき理由

• 上記で引用したBrianのエントリにも書いてあるように、ServerPreparedStatementはBlobを使用している場合の性能向上、Blob以外の場合も少しだけ性能向上が期待できる
• http://d.hatena.ne.jp/mir/20060213/p1でも書いたけどServerPreparedStatementは大量データ送信時に相対的にプロトコルが軽い(上記と同じ話かも)
• Connection.close()できていればメモリリークの心配はないはず(サーバ自身に問題がある場合を除く)
• SQLインジェクション対策になる

使うべきでない理由

• MySQLサーバのPreparedStatementはSQLパーサーのオーバーヘッドを軽くするが、オプティマイザ結果のキャッシュとかはしてくれないのであまり大した物ではない
• BrianやMarkがあまり推奨していない(理由は前述の通り「プログラマがミスる」だけど、他にもある？)という事実
• Connectionプーリング(java.sql.DataSource)を使っていると、ds.close()してもconn.close()されるとは限らない。

という感じでしょうか。SQLインジェクションをアプリ側で完璧にできるなら、性能面で困っていない限り、使わなくても良いかも。でもSQLインジェクション対策になるのは捨てがたいかも・・と結論がでません。

というわけでおしまいおしまい。