MySQL 如何避免 SQL injection

遇到這個問題，大部分的人應該會說：用 ORM (Object-relational mapping) 呀！但我就沒有打算多學一套 >< 一方面也是不清楚它欄位的屬性、資料形態是否完全支援 mysql，加上我執行的 query 也蠻複雜的。

先前有找了幾套：

• SQLAlchemy - 大家比較推薦的
• Quick ORM - fully compatible with the newest SQLAlchemy

ORM 的好處是你只要設定一次 mapping，之後 select、update、insert 都很方便，但我實在是沒有必要為了一個 injection protect 就用了這麼強大的東西。


除了 ORM 以外...
Search 了一下發現 MySQLdb 本身就有支援以參數的方式來執行 query：

cursor.execute('SELECT * FROM `user` WHERE user=%s AND password=%s;', usr, pwd)

所有的參數都會根據其資料型態來取代 format string，特殊字元自然就會被跳脫了。

要注意的是 statement 內的引號 " 要去掉，format string 通通使用 %s，像下面兩個語法就會有問題：

cursor.execute('SELECT * FROM `user` WHERE username="%s";', username)
cursor.execute('SELECT * FROM `user` WHERE id=%d;', my_id)

另外，也不要把 format string 串起來才傳過去，這樣就會真的發生 SQL injection了：

cursor.execute('SELECT * FROM `user` WHERE user=%s AND password=%s;' % (usr, pwd))

至於其他的 database 可以找看看有沒有 prepared statement 可以使用。


參考資料

• SQL Injection Defense in Python

MySQL INSERT 與 UPDATE 的一些特殊情況用法

最近剛好在設計 social network 的 database schema，遇到很多 special query 的需求，在此一一記錄下來。

如果不存在才 INSERT，存在就 skip
舉例來說，如果想記錄某 user 是否去過某國家

-----------------
 user  | country
-------+---------
 tony  | US
 tony  | HK
 kelly | US

Query:

INSERT INTO `trip` (`user`, `country`) 
SELECT 'tony', 'US' FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM `trip` WHERE `user` = "tony" AND `country` = "US" LIMIT 1
);

* 想 INSERT 的 table 可以跟檢查存在性的 table 不同個。

參考資料
mysql - Insert Where Not Exists-Without Primary Key - Stack Overflow



如果不存在才 UPDATE，否則 skip
舉例來說，你想讓 user 更新 email 欄位(unique)，但 email 又不能跟其他人一樣：

------------------------------
 user      | email
-----------+------------------
 olivia    | olivia@email.com
 andy_wang | andy@email.com
 andy_lin  | (NULL)

Query:

UPDATE `profile` SET `email` = "andy@email.com" WHERE `user` = "andy_lin" AND NOT EXISTS
(SELECT 1 FROM `profile` WHERE `email` = "andy@email.com");

但你會發現 mysql execute query 會有下面錯誤：

You can't specify target table 'profile' for update in FROM clause

應該改成：

UPDATE `profile` SET `email` = "andy@email.com" 
WHERE `user` = "andy_lin" AND NOT EXISTS
  (SELECT * FROM (SELECT 1 FROM `profile` WHERE `email` = "andy@email.com") temp);

參考資料
MySQL can’t specify target table for update in FROM clause | VerySimple

SimpleDB - "Too many value tests per predicate in the query expression"

當用Simple下Querey的時候，condition超過20個就會出現下面的錯誤

Too many value tests per predicate in the query expression
或
Client error : Too many value tests per predicate in the query expression.

像這類的語法都不行：

SELECT * FROM domain WHERE id='1' OR id='2' OR ... id='20' OR id='21'

SELECT * FROM domain WHERE id in ('1', '2', ... '20', '21')

SELECT * FROM domain WHERE id in ('1', '2', ... '20') OR id in ('21', ...)

只能考慮分批一次抓20個，然後再合併處理。



2012/05/07 發現一種可以破解20個條件限制的辦法：

SELECT * FROM `domain` WHERE 
  account in ('key1', 'key2', 'key3', 'key4', 'key5', 'key6', 'key7', 'key8', 'key9', 'key10', 
    'key11', 'key12', 'key13', 'key14', 'key15', 'key16', 'key17', 'key18', 'key19', 'key20') OR 
  dummy IS NOT NULL OR 
  account in ('key21', 'key22', 'key23', 'key24', 'key25', 'key26', 'key27', 'key28', 'key29', 'key30', 
    'key31', 'key32', 'key33', 'key34', 'key35', 'key36', 'key37', 'key38', 'key39', 'key40')

沒錯，就是加了"OR dummy IS NOT NULL"這個多餘的條件在中間就可以下達多個條件了！

不過當你有這樣的需求時，就應該想一下是不是設計面有問題，或是該用 relational database了。



參考資料
Amazon SimpleDB » Developer Guide » Amazon SimpleDB Concepts » Limits
Query 101: Building Amazon SimpleDB Queries