顯示具有 MySQL 標籤的文章。 顯示所有文章
顯示具有 MySQL 標籤的文章。 顯示所有文章

2012/06/11

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 可以使用。


參考資料

2012/05/31

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