この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
RDBを操作する際には、SQL文の組み立てが発生します。自前で文字列操作を駆使してSQL文を組み立てると、バグやセキュリティホールの温床となりかねません。
今回はMySQL Connector/Pythonからお行儀よくSQLを投げる方法を紹介します。
事前準備:MySQL Connector/Python のインストール
MySQL Connector/Python のインストールは次の記事を参照して下さい。
今回は 2.1.6 を利用します
(非推奨)自前でSQLを組み立てる
ナイーブにSQL文を自分で組み立て、データベースに投げてみましょう
import mysql.connector
config = {
'user': 'USER',
'password': 'PASSWORD',
'host': 'HOST',
'database' : 'DBNAME',
}
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
stmt = "select * from company where name = '%s'"
cursor.execute(stmt % "classmethod")
... # read data
cursor.close()
cnx.close()
name が "classmethod" だと正しく実行出来ました。 "McDonald's" だとどうなるでしょうか?
>>> "select * from company where name = '%s'" % "McDonald's"
"select * from company where name = 'McDonald's'"
セッションからわかるように、生成された SQL はシングルクオートのシンタックスエラーがあります。
エスケープ処理を入れ忘れたために、今回の様なことが発生します。
様々な入力パターンに対しても正しくSQLが実行されるように、以降で紹介するようにプレースホールダーを使ってSQLを実行します。
プレースホルダーを使ってSQLを投げる
Python には DB エンジンや DB ドライバーによらずにシームレスかついい感じにDB操作するための仕様「DB-API 2.0」があります。
PEP 249 -- Python Database API Specification v2.0
MySQL Connector/Python もこの仕様に準拠しているため、この API を素直に活用します。
まずは DB-API 2.0 に準拠していることを確認します。
>>> import mysql.connector
>>> mysql.connector.apilevel
'2.0'
>>> mysql.connector.threadsafety
1
>>> mysql.connector.paramstyle
'pyformat'
先程のようにSQL文を自前で組み立てず、テンプレートとパラメーターを分離して、API に渡します
import mysql.connector
config = {
'user': 'USER',
'password': 'PASSWORD',
'host': 'HOST',
'database' : 'DBNAME',
}
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
stmt = "select * from company where name = %s"
cursor.execute(stmt, ("McDonald's", ))
... # read data
cursor.close()
cnx.close()
- SQLのテンプレートではプレースホールダー
%s
の前後でクオートしません - テンプレートに渡すパラメーターはタプルで渡します
このようにするだけで、文字列をクオートで囲んだり、エスケープ処理した SQL文("select * from company where name = "McDonald\\'s"
)が DB サーバーに送信されます
プリペアドステートメントを利用する
MySQL Connector/Pythonはプリペアドステートメントにも対応しています。
カーソル作成時に引数(prepared=True
)を追加するだけです。
import mysql.connector
config = {
'user': 'USER',
'password': 'PASSWORD',
'host': 'HOST',
'database' : 'DBNAME',
}
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor(prepared=True) # 修正
stmt = "select * from company where name = ?" # 修正
cursor.execute(stmt, ("McDonald's", ))
... # read data
cursor.close()
cnx.close()
プリペアドステートメントの違いは以下です
- 同じSQL文の場合、2回目以降のSQL文の解析が不要
%s
(format/pyformat) だけでなく?
(qmark) もパラメーターのプレースホルダーとして利用可能
動的要素のないSQLに対しては有用です。
エスケープ関数を使う
様々な理由により、自前でSQLを組み立てざるを得ないケースもあります。
そのような場合は、エスケープ処理は自前で実装せず、コネクションオブジェクトの converter.escape
関数を利用します。
>>> cnx.converter.escape("a'b")
"a\\'b"
カーソルではなくコネクションからSQLを投げる
コネクションオブジェクトには cmd_query
というメソッドが存在し、SQL を直接投げることが出来ます。
import mysql.connector
config = {
'user': 'USER',
'password': 'PASSWORD',
'host': 'HOST',
'database' : 'DBNAME',
}
cnx = mysql.connector.connect(**config)
stmt = "select * from company where name = '%s'"
cnx.cmd_query(stmt, (cnx.converter.escape("McDonald's"), ))
... # read data
cnx.close()
カーソルオブジェクトとは異なり
- プレースホールダー
%s
をクオートで囲むなど、型の応じた対応が必要 - テンプレートに渡すパラメーターはエスケープ処理を済ませておく
など、記述が煩雑になります。
そのため、積極的には使わない方が良いでしょう。
まとめ
今回はMySQL Connector/PythonからSQLを投げる方法を紹介しました。
- 文字列操作を駆使してSQL文を組み立てるのは極力さける
- プレースホールダーを活用する
- プリペアドステートメントも使える
- 必要に応じてパラメーターをユーティリティ関数でエスケープ
それでは。
参考
- PEP 249 -- Python Database API Specification v2.0
- MySQL :: MySQL Connector/Python Developer Guide :: 5 Connector/Python Coding Examples
- MySQL :: MySQL Connector/Python Developer Guide :: 10.6.8 cursor.MySQLCursorPrepared Class
- MySQL :: MySQL Connector/Python Developer Guide :: 10.2.13 MySQLConnection.cmd_query() Method