Python MySQL Connector エスケープについて
PythonでMySQLに接続する際に使用するライブラリはいくつかあるかと思います。
ここではOracle純正のライブラリ、MySQL Connectorのエスケープ処理についてまとめます。
SQLインジェクション
SQLインジェクションとはSQLに悪意ある値を入力することで不正にDBを操作することです。
簡単な例で言えば、
sql = 'SELECT * FROM Users WHERE Name ="' + name + '" AND Pass ="' + pass + '"'
通常name
にYamada
、pass
に12345
などが入力されます。
しかし、name
に" OR ""=""
と入力した場合、クエリは以下のようになります。
sql = 'SELECT * FROM Users WHERE Name ="" OR ""="" " AND Pass ="12345"'
名前とパスワードの判定に使われていたWHERE句にOR句が追加されたことがわかります。
当然""=""
はTRUE
なので、WHERE句は常にTRUE
となり、Usersテーブルの中身を全て返してしまいます。
ざっくりですがこれがSQLインジェクションの一例です。
エスケープ処理
前項のようなSQLインジェクションは特殊文字をエスケープすることで防ぐことができます。
例えば、先ほどの例では"
を\"
とエスケープ処理することで悪意あるSQLインジェクションおよび予期せぬSQLエラーを防ぐことができます。
MySQL Connectorでのエスケープ処理
MySQL Connectorではexecute()メソッドを使えばエスケープ処理を自動で行ってくれます。
pip
を使ってインストールしておきます。
pip install mysql-connector
これが通常のクエリです。
import mysql.connector conn = mysql.connector.connect(user=DB_USER, password=DB_PASSWORD, host=HOST, database=DATABASE) cur = conn.cursor() query = 'INSERT INTO table (col1) VALUES ("hoge")' cur.execute(query) conn.commit()
次に、hoge
の代わりにho"ge
を使う場合はho\"ge
のようにエスケープすることも可能です。
しかし、MySQL Connectorのexecute()
メソッドには自動でエスケープする機能がついています。
オプション引数のparams
を使います。params
はタプルもしくはディクショナリが許容されます。
書き方を少し変更して、以下のようにします。
%演算子をクォーテーションで括る必要がなくなっていることがわかります。
query = 'INSERT INTO table (col1) VALUES (%s)' data = ('ho"ge') cur.execute(query, data)
複数のデータをINSERTしたい場合
executemany()メソッドを使うことで解決します。
dataをリスト形式にするだけで、自動でエスケープ処理されたものがバルクインサートされます。
query = 'INSERT INTO table (col1, col2) VALUES (%s, %s)' data = [('hoge', 'fuga'), ('piyo', 'moge')] cur.executemany(query, data)
複数の条件をWHERE句に使いたい場合
例えば、リストに含まれるものを探したい場合、
残念ながらexecutemany()にWHERE句のクエリとlistのデータを代入するだけではうまくいきません。
以下のように工夫します。
id_list = [1, 2, 3] format_strings = ','.join(['%s'] * len(id_list)) query = "SELECT * FROM tables WHERE id in (%s)" % format_strings cur.execute(query, tuple(id_list))
以上のように、id_listの要素の数(ここでは3つ)の%s
すなわち%s, %s, %s
をWHERE句の%s
に代入します。
そして改めてタプルを3つの%s
に代入します。
参考
- SQL Injection
- MySQL :: MySQL Connector/Python Developer Guide :: 10.5.4 MySQLCursor.execute() Method
- MySQL :: MySQL Connector/Python Developer Guide :: 10.5.5 MySQLCursor.executemany() Method
- mysql/mysql-connector-python: MySQL Connector/Python is implementing the MySQL Client/Server protocol completely in Python. No MySQL libraries are needed, and no compilation is necessary to run this Python DB API v2.0 compliant driver. Documentation & Download: http://dev.mysql.com/doc/connector-python/en
- mysql – imploding a list for use in a python MySQLDB IN clause – Stack Overflow