使用bind variable執行SQL

在寫sql的時候，一開始總是習慣帶入一支完整的sql，遇到要替換的地方就在程式用取代來處理。例如下面這個範例，為了insert不用的資料，用format來作字串替代完成一支sql

1
2
3

>>> sql = "INSERT INTO ALBUM(TITLE, ARTIST_ID) VALUES ('{0}',{1})"
>>> sql = sql.format('The Archive', '1')
INSERT INTO ALBUM(TITLE, ARTIST_ID) VALUES ('The Archive',1)

可以看到要insert的資料，如果是char型態會用’’括起來。但是當今天要取代的字串有特殊符號或是有引號處理起來就會很麻煩，像下面的例子在insert到db時就會造成syntax error

1
2
3

>>> sql = "INSERT INTO ALBUM(TITLE, ARTIST_ID) VALUES ('{0}',{1})"
>>> sql = sql.format("It's Time", '2')
INSERT INTO ALBUM(TITLE, ARTIST_ID) VALUES ('It's Time',2) # syntax error

所以再來要介紹一下用bind variable下SQL的好處，下面是一個oracle使用bind variable的方式，在執行SQL時，會將變數直接帶入:1和:2的位置，所以也不用考慮任何原本要處理的跳脫字元問題

1 2	sql = "INSERT INTO ALBUM (TITLE, ARTIST_ID) VALUES (:1, :2)" cursor.execute(sql, ("It's Time", 2))

使用bind variable不止在處理SQL很方便之外，也可以避免有SQL injection的風險，甚至對操作DB的performance也有影響。

例如對Oracle DB下SQL時，oracle首先會在share pool找到這支SQL是否之前曾執行過，有的話會取得這支SQL之前處理過的執行計畫(Execute Plan)並開始執行這支SQL；如果找不到的話oracle會hard parsing這支SQL，並找到最佳的執行計畫後才執行SQL。所以如果能在share pool找到之前有執行過的執行計畫，SQL執行前就不用再經過hard parsing。

但是oracle在share pool尋找的時候，只有完全相同的SQL才會被認為有對應到。所以當我們在程式使用取代字串來完成SQL，在執行時每一支SQL都會被視為不同的SQL，自然每一次的SQL執行前都必須要經過hard parsing

1 2	INSERT INTO ALBUM(TITLE, ARTIST_ID) VALUES ('The Archive',1) # 兩支SQL會被oracle視為不同的SQL INSERT INTO ALBUM(TITLE, ARTIST_ID) VALUES ('Hear Me',3)

為了避免這個問題，可以使用bind variable來下SQL，因為採用bind variable的SQL會被視為同一支SQL

1
2
3

sql = 'INSERT INTO ALBUM(TITLE, ARTIST_ID) VALUES (:1, :2)'
cursor.execute(sql, ("It's Time", 2))  # 兩支SQL會被oracle視為相同的SQL
cursor.execute(sql, ("Hear Me", 3))

因此採用bind variable在操作SQL上不止處理起更方便(不用特別處理跳脫字元)，也更安全(避免SQL injection)，甚至更快速(重使用最佳化過後的執行計畫)