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

社交網路設計：在地圖中找尋附近的人

很多社交網路App都會用到地圖，以自己為中心，找尋附近的店家、朋友等等。
用程式的邏輯來表達就是：

以 (x, y) 座標為中心，找尋方圓 n 公里以內的點

資料庫不外乎就是儲存經緯度 (longitude, latitude)

-----------------------------
 id | lat       | lng
----+-----------+------------
 1  | 25.151000 | 121.549000
 2  | 25.010000 | 121.574000
 3  | 25.070000 | 121.589000

今天我想找 User1 附近 10km 以內的人
方法一
來看"距離"跟"經緯度"的關係是什麼，從 Decimal degrees - Wikipedia 知道，其實他們並非線性的關係，而是一堆三角函數算出來的，因為隨著緯度越高，單位經緯度所對應的距離就越短。從 別搗蛋 歸納出的對應表：

台灣地區：
兩地經緯度相差 0.5度：距離相差約 50公里
兩地經緯度相差 0.1度：距離相差約 10公里
兩地經緯度相差 0.05度：距離相差約 5公里
兩地經緯度相差 0.01度：距離相差約 1公里

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

社交網路資料庫設計：怎麼找共同朋友、朋友的朋友

在 facebook 內當你點朋友時，你可以知道彼此共同朋友有哪些，另外在網頁右欄有個"你可能認識"的專區，這個功能是怎麼做的？

先來分析這兩個語意：
1. 共同的朋友："自己的朋友"跟"朋友的朋友"重疊的部分
2. 你可能認識的人：把朋友的朋友全部列出來，依據重疊次數來排序，越高的表示你越可能認識的人，如果是你還沒加到名單的，facebook 就會主動推薦給你


在 stackoverflow 找到兩篇參考文章：

• database design - how facebook calculates mutual friends? - Stack Overflow
• database - Wondering how Facebook does the "Mutual friends" feature - Stack Overflow 

Postgresql：建立master-slave database

架構圖

主要參考資料

• Postgres 9.1: Setup a synchronous stand-by server in 5 minutes | Michael Paquier

假定我們的兩台DB分別在不同機器

• Master 10.0.0.2
• Slave 10.0.0.3

Postgresql建立索引

有沒有建索引的查詢速度真的差很多，預設是使用binary-tree來實作index，時間複雜度是O(logN)。從N變成logN，是有指數級的差距！

建index的方法可以參考

• PostgreSQL: Documentation: Manuals: PostgreSQL 9.0: CREATE INDEX

以下舉幾個例子，假如我們的table叫account，email這個欄位很常用來當查詢條件，那我們會為email建立index:

postgres=> CREATE INDEX account_email_idx ON account (email);
postgres=> \d account;
Indexes:
    "account_pkey" PRIMARY KEY, btree (id)   # Primary key預設就會建立index了
    "account_email_idx" btree (email)

Composite index?

Composite indexes are used when two or more columns are best searched as a unit or if many queries reference only the columns specified in the index.
All the columns in a composite index must be in the same table.

我自己把它解釋成當一個query同時帶多個條件時，composite index可以加快查詢的速度。

postgres=> CREATE INDEX account_composite_idx ON account (name, email);
postgres=> \d account;
Indexes:
    "account_pkey" PRIMARY KEY, btree (id)
    "account_composite_idx" btree (name, email)

除了建索引，定期清理(VACUUM) database 也可以加快存取速度

postgres=> VACUUM VERBOSE ANALYZE;

Postgresql 小記

常用指令

# Start
su postgres -c "/usr/local/pgsql/bin/pg_ctl start -D /usr/local/pgsql/data"

# Stop
su postgres -c "/usr/local/pgsql/bin/pg_ctl stop -D /usr/local/pgsql/data"

# restart
su postgres -c "/usr/local/pgsql/bin/pg_ctl restart -D /usr/local/pgsql/data"

# Init postgres
sudo mkdir /usr/local/pgsql/data
chown postgres:postgres /usr/local/pgsql/data
su postgres -c "/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data"

# Create user
/usr/local/pgsql/bin/createuser -U postgres -P userA

# Drop user
/usr/local/pgsql/bin/dropuser -U postgres userA

# Create db
/usr/local/pgsql/bin/createdb -O userA -U postgres testdb

# Drop db
/usr/local/pgsql/bin/dropdb -U postgres testdb

# Dump db
/usr/local/pgsql/bin/pg_dump -U postgres testdb > dump.sql

# Restore db
/usr/local/pgsql/bin/psql -U postgres -d testdb < dump.sql

# Grant all privileges
/usr/local/pgsql/bin/psql -U postgres -c "GRANT ALL PRIVILEGES ON DATABASE testdb TO userA;"

# Enter postgresql command line
/usr/local/pgsql/bin/psql -U postgres -d poesgres -h localhost -p 5432

基本設定

• /usr/local/pgsql/data/postgres.conf

listen_addresses = '192.168.0.3'   # 設定listen IP

• /usr/local/pgsql/data/pg_hba.conf

host    all             all             127.0.0.1/32              trust
host    all             all             192.168.0.0/16            trust
host    all             all             10.10.1.5/32              password

Log分析
推薦使用pgFouine - a PostgreSQL log analyzer，它可以分析出某段時間內，做了幾次query，處理時間最久的，次數最多的...可以看一下Sample reports長怎樣。


效能測試:
pgbench
使用 pgbench 进行数据库压力测试

pgbench -U postgres -i -s 50 postgres 
pgbench -U postgres -c 100 -t 100 -S postgres

Performance Tuning PostgreSQL