第８回 2014.03.14
前回の大雪で延期された第８回のリベンジマッチが開催されました。
今回は
- MySQLのかる〜い説明
って流れでお話させていただきました。
全体についてはアップしたスライドを参照していただくとして、SQLインジェクションについては実際にWebアプリケーションを作ってみて気づいたりするところなのかも知れませんが、やはり間違った方向で覚えてしまってもいけないのかな…なんて思いまして、この機会に行った次第です。
実際のサンプルも用意しておりますので、色々攻撃をしてもらって(笑)気付いていただければと思います。
基本はプリペアードステートメントを使うって事なんですけど、、、
この機能はSQLインジェクション用にある機能ではなく、SQL文のリテラルを変数に置き換えて準備しておく事によって、リテラルの内容が変更されてもRDBMSが同一のSQL文として取り扱っていただけるという機能なのですよね…。
この機能を使わないと、リテラルが置き換わったSQL文はRDBMSでは別なSQL文として取り扱っちゃうので、何度もSQL解析が走っちゃってサーバ負荷、レスポンス低下を招いちゃうんですよね…。
と言う事で、質問で出てきた事なんですけど、プリペアードステートメントの本来の
用途にスポットを当てた解説を少々…
サンプルがOracle+Javaで今回のと真逆？になっちゃいますけどご了承くださいw
1000件のデータのSELECT文でプリペアードステートメントを使った場合と
使わなかった場合のOracleのSQLトレースの違いをみてみます。
プリペアードステートメントを使わない場合…
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class TestNotBind { public static void main(String[] args) { String strPara = null; try { Class.forName("oracle.jdbc.OracleDriver"); Connection con = DriverManager.getConnection("jdbc:oracle:thin:@111.111.111.111:1521:HOGE",+ "scott", "tiger"); //SQLTRACE取得用 Statement alt = con.createStatement(); alt.execute("ALTER SESSION SET SQL_TRACE=TRUE"); //SQL実行 Statement st = con.createStatement(); for(int i=0;i<1001;i++){ strPara = String.valueOf(i); String sql = "SELECT EMP_NAME FROM EMP WHERE EMP_NO = '"+ strPara+"'"; ResultSet rs = st.executeQuery(sql); while(rs.next()){ System.out.println(param+":"+rs.getString("EMP_NAME")); } rs.close(); } st.close(); alt.execute("ALTER SESSION SET SQL_TRACE=FALSE"); alt.close(); con.close(); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } } }
プリペアードステートメントを使った場合…
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class TestBind { public static void main(String[] args) { String strPara2 = null; try { Class.forName("oracle.jdbc.OracleDriver"); Connection con = DriverManager.getConnection("jdbc:oracle:thin:@111.111.111.111:1521:HOGE",+ "scott", "tiger"); //SQLTRACE取得用 Statement alt = con.createStatement(); alt.execute("ALTER SESSION SET SQL_TRACE=TRUE"); //SQL実行 String binds = "SELECT EMP_NAME FROM EMP WHERE EMP_NO = ?"; PreparedStatement ps = con.prepareStatement(binds); for(int j=0;j<1001;j++){ strPara2 = String.valueOf(j); ps.setString(1, strPara2); ResultSet rs2 = ps.executeQuery(); while(rs2.next()){ System.out.println(param2+":"+rs2.getString("EMP_NAME")); } rs2.close(); } ps.close(); alt.execute("ALTER SESSION SET SQL_TRACE=FALSE"); alt.close(); con.close(); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } } }
んでSQLトレースの違い
プリペアードステートを使わなかった場合
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1002 1.07 0.83 0 0 0 0
Execute 1003 0.00 0.08 0 0 0 0
Fetch 1001 0.01 0.01 0 3003 0 1001
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3006 1.09 0.94 0 3003 0 1001
Misses in library cache during parse: 1002
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
1003 user SQL statements in session.
0 internal SQL statements in session.
1003 SQL statements in session.
********************************************************************************プリペアードステートメントを使った場合
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 1003 0.03 0.19 0 0 0 0
Fetch 1001 0.00 0.01 0 3003 0 1001
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2006 0.03 0.20 0 3003 0 1001
Misses in library cache during parse: 0
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
3 user SQL statements in session.
0 internal SQL statements in session.
3 SQL statements in session.
********************************************************************************特に最初のParse(解析)のcountの箇所に注目して欲しいんですけど、
プリペアードステートメント使わない場合は、1000回以上解析されてますけど、
プリペアードステートメント使った場合は、２回しか解析がされてませんよね。
これが、プリペアードステートメントの本来の目的なんですよ。
でもって、変数化される事で、変数に入る値がリクエストパラメータで悪意のある値がわたったとしても
もうSQL文として解釈してるから、それ以上の解釈をしない…
つまり、別の意味のSQL文が実行される事がなくなるってことになり、
結果として、SQLインジェクション対策になるって事になります。
エスケープ関数などを使っても完璧でないと脆弱性が入り込んだままになってしまいます。
リテラルについては、もっとも簡単で且つほぼ間違いなく防げるのがプリペアードステートメントなんですね。
※識別子などではエスケープも必要な場合がありますので！
んで、今回は飛び込みでの参加もあり、色々ためになる話も聞けました。
てか、プレゼンが予定時間の半分で終了してしまい、焦りましたが…。
そのときにPHPでリクエストパラメータの汚染チェックtaintなるものもあるって知りました。
今後、htmlspecialcharsやPDO::quoteみたいな、エスケープ系のことも必要であるとは思っていたのですが、
こういったのが漏れたときのチェックとして使えるんですね、、、
やっぱり経験値が高い人とのぶつかり稽古？は楽しい。
では、次回４月１１日…
どうしましょ？俺だ俺だ俺だって人がいなかったら、大人のSQL講座にしようかな…
