Python Oracle でバインド変数を使ったSQLの実行方法について投稿します。
バインド変数はSQLインジェクション対策で有効です。
#リテラルSQLとバインド変数を使用したSQLの違い
##リテラルSQL
SQL文のWHERE条件に値が埋め込まれているものです。
リテラルSQL文の例
SELECT * FROM EMP WHERE ENAME ='SMITH'
SELECT * FROM EMP WHERE ENAME ='ALLEN'
SELECT * FROM EMP WHERE ENAME ='MILLER'
など、SQL文に条件が埋め込まれているSQL文
実行例
１．SQLを作成
'SELECT * FROM EMP WHERE ENAME =' '' || pEname || ''' '
２．<変数名> pEname に 値を 設定
３．SQLを実行
--変数にSMITHと設定した場合
SELECT * FROM EMP WHERE ENAME ='SMITH'
--変数にTANAKAと設定した場合
SELECT * FROM EMP WHERE ENAME ='TANAKA'
--変数に '''' or ''A'' = ''A と設定した場合、
以下のSQLが作成されます。
SELECT empno, ename FROM emp WHERE deptno = '' or 'A' = 'A'
⇒★これが問題！ SQLインジェクションができてしまいます。
'A' = 'A' はすべて TRUEとかえすので、全ての行が取得されてしまいます
##バインド変数を使用したSQL
SQLに変数を定義して、SQL実行前に変数に値を入れてSQLを実行するものです。
バインド変数を使用したSQLの例
SELECT * FROM EMP WHERE ENAME = :pEname
(:<変数名>として記載するSQL文)
１．SQLを作成
'SELECT * FROM EMP WHERE ENAME = :pEname '
２．<変数名> :pEname に 値を 設定
３．SQLを実行
-- :pEnameのバインド変数を使用したSQLが実行される
SELECT * FROM EMP WHERE EMPNO = :pEname
pEnameにどんな値が入っても、上記SQLで実行されるため
SQLインジェクションは実行できない
#実際にサンプルで確認してみる
Pythonスクリプトで
リテラルSQL(普通の条件、SQLインジェクションになる条件)、
バインド変数を使用したSQL(普通の条件、SQLインジェクションになる条件)を実行します。
import cx_Oracle
Svr="<DBサーバ名>"
Port=1521
SID="orcl"
USR="scott"
PWD="tiger"
tns = cx_Oracle.makedsn(Svr, Port, SID)
conn = cx_Oracle.connect(USR, PWD, tns)
#カーソルOPEN
cur = conn.cursor()
###########################################
#バインド変数 ケース１ 普通の条件
###########################################
keyword="SMITH"
#SQLをパース
cur.prepare('SELECT empno, ename FROM emp WHERE ename = :p_ename')
cur.execute(None, {'p_ename':keyword})
res = cur.fetchall()
print("")
print("BindSQL NomalSQL")
print("--------------")
for r in res:
print(str(r[0]) + '|' + r[1])
###########################################
#バインド変数 ケース２ SQLインジェクション
###########################################
injection="' or 'A' = 'A"
cur.execute(None, {'p_ename':injection})
res = cur.fetchall()
print("")
print("BindSQL InjectionSQL")
print("--------------")
for r in res:
print(str(r[0]) + '|' + r[1])
###########################################
#リテラルSQL ケース１ 普通の条件
###########################################
keyword="SMITH"
sql="SELECT empno, ename FROM emp WHERE ename = '" + keyword + "'"
cur.execute(sql)
res = cur.fetchall()
print("")
print("LiteralSQL NomalSQL")
print("--------------")
for r in res:
print(str(r[0]) + '|' + r[1])
###########################################
#リテラルSQL ケース２ SQLインジェクション
###########################################
injection="' or 'A' = 'A"
sql="SELECT empno, ename FROM emp WHERE ename = '" + injection + "'"
cur.execute(sql)
res = cur.fetchall()
print("")
print("LiteralSQL InjectionSQL")
print("--------------")
for r in res:
print(str(r[0]) + '|' + r[1])
cur.close()
conn.close()
実行結果
#バインド変数を使用するメリット
SQLインジェクション対策、Oracle観点でいうとwhere条件の値が違うSQLを実行する場合、
ハードパース回数が減り、それに伴いハードパース解析が減るのでCPU使用負荷も減ります。
また共有プールに格納されるSQL関連情報もリテラルSQLに比べると減る事になります。
#Pythonでバインド変数を使用したSQLを実行してみる
pythonでバインド変数を使用する方法はいくつかあるのでサンプルを作成。
import cx_Oracle
Svr="<サーバ名>"
Port=1521
SID="orcl"
USR="scott"
PWD="tiger"
tns = cx_Oracle.makedsn(Svr, Port, SID)
conn = cx_Oracle.connect(USR, PWD, tns)
#カーソルOPEN
cur = conn.cursor()
###################################
#バインド変数 SQL実行方法 その１
###################################
#SQLをパース
cur.prepare('SELECT empno, ename FROM emp WHERE ename = :p_ename')
#バインド変数に値設定してSQLを実行
#SQLが「None」は、cur.prepare で設定されたSQLが実行される
cur.execute(None, {'p_ename': 'SMITH'})
res = cur.fetchall()
print("")
print("BINDSQL CASE1")
print("--------------")
for r in res:
print(str(r[0]) + '|' + r[1])
#バインド変数に値設定してSQLを実行 ※SQL文は直近でcur.prepare もしくは cur.executeしたSQLになります
cur.execute(None, {'p_ename': 'ALLEN'})
res = cur.fetchall()
for r in res:
print(str(r[0]) + '|' + r[1])
###################################
#バインド変数 SQL実行方法 その２
###################################
#ディクショナリ型の変数に バインド変数、設定値を設定
named_params = {'p_ename2':'TURNER'}
cur.execute('SELECT empno, ename FROM emp WHERE ename = :p_ename2', named_params)
res = cur.fetchall()
print("")
print("BINDSQL CASE2")
print("--------------")
for r in res:
print(str(r[0]) + '|' + r[1])
###################################
#バインド変数 SQL実行方法 その３
###################################
#SQLとバインド設定値を設定
cur.execute('SELECT empno, ename FROM emp WHERE ename = :p_ename3', p_ename3='BLAKE')
res = cur.fetchall()
print("")
print("BINDSQL CASE3")
print("--------------")
for r in res:
print(str(r[0]) + '|' + r[1])
cur.close()
conn.close()
実行結果
バインド変数を使用したSQLは、いくつか書き方がありますので、
使用ケースによって使いわけるのがいいと思います。
#参考リンク
・ http://www.oracle.com/technetwork/articles/dsl/prez-python-queries-101587.html
・ http://www.oracle.com/technetwork/articles/dsl/python-091105.html
・ http://www.oracle.com/technetwork/articles/dsl/mastering-oracle-python-1391323.html
・Windows環境のPythonからOracleに接続してみる