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に代入します。