常用操作 / 資料庫操作

資料庫操作

資料庫連線一開始是通過tool/init.php線上配置的，或直接手改檔案 php/conf.user.php 檔案的相關配置如：

putenv("P_DB=localhost/jdcloud");
putenv("P_DBCRED=test:test123");

如果想稍稍隱蔽一下登入賬號，也可以用base64編碼，如：

putenv("P_DBCRED=ZGVtbzpkZW1vMTIz");

資料庫查詢的常用函式是queryOne和queryAll，用來執行SELECT查詢。 queryOne只返回首行資料，特別地，如果返回行中只有一列，則直接返回首行首列值：

// 查到資料時，返回首行 [$id, $dscr]，例如 [100, "使用者100"]
// 沒有查到資料時，返回 false
$rv = queryOne("SELECT id, dscr FROM Ordr WHERE userId={$uid}");
list($id,$dscr) = $rv;

// 查到資料時，由於SELECT語句只有一個欄位id，因而返回值即是$id，例如返回100
$rv = queryOne("SELECT id FROM Ordr WHERE userId={$uid}");
$id = $rv;

如果欄位較多，常加引數PDO::FETCH_ASSOC要求返回關聯陣列以增加可讀性：

// 操作成功時，返回關聯陣列，例如 ["id" => 100, "dscr" => "使用者100"]
$rv = queryOne("SELECT id, dscr FROM Ordr WHERE userId={$uid}", PDO::FETCH_ASSOC);

如果要查詢所有行的資料，可以用queryAll函式：

// 有資料時，返回二維陣列 [[$id, $dscr], ...]
// 沒有資料時，返回空陣列 []，而不是false
$rv = queryAll("SELECT id, dscr FROM Ordr WHERE userId={$uid}");

執行非查詢語句可以用包裝函式execOne如：

execOne("DELETE ...");
execOne("UPDATE ...");
execOne("INSERT INTO ...");

對於insert語句，可以取到執行後得到的新id值：

$newId = execOne("INSERT INTO ...", true);

[防備SQL隱碼攻擊]

要特別注意的是，所有外部傳入的字串引數都不應直接用來拼接SQL語句， 下面登入介面的實現就包含一個典型的SQL隱碼攻擊漏洞：

$uname = mparam("uname");
$pwd = mparam("pwd");
$id = queryOne("SELECT id FROM User WHERE uname='$uname' AND pwd='$pwd'");
if ($id === false)
    throw new MyException(E_AUTHFAIL, "bad uname/pwd", "使用者名稱或密碼錯誤");
// 登入成功
$_SESSION["uid"] = $id;

如果黑客精心準備了引數 uname=a&pwd=a' or 1=1，這樣SQL語句將是

SELECT id FROM User WHERE uname='a' AND pwd='a' or 1=1

總可以查詢出結果，於是必能登入成功。 修復方式很簡單，可以用Q函式進行轉義：

$sql = sprintf("SELECT id FROM User WHERE uname=%s AND pwd=%s", Q($uname), Q($pwd));
$id = queryOne($sql);

因為很常用，所以使用了一個超級簡單的名字叫Q(quote)，它一般的實現就是：

global $DBH;
$DBH->quote($str);

[SQL編譯優化]

全域性變數$DBH是預設的資料庫連線物件，即PDO物件，在程式中也可以直接使用，比如要插入大量資料，為優化效能，可以先對SQL語句進行編譯(prepare)後再執行：

global $DBH;
$tm = date(FMT_DT);
$sth = $DBH->prepare("INSERT INTO ApiLog (tm, addr) VALUES ('$tm', ?)");
foreach ($addrList as $addr) {
    $sth->execute([$addr]);
}

上面用到的常量FMT_DT是框架定義的標準日期格式，常用於格式化日期欄位傳到資料庫。

[支援資料庫事務]

假如有一個使用者用帳戶餘額給訂單付款的介面，先更新訂單狀態，再更新使用者帳戶餘額：

function api_payOrder()
{
    execOne("UPDATE Ordr SET status='已付款'...");
    ...
    execOne("UPDATE User SET balance=...");
    ...
}

在更新之後，假如因故丟擲了異常返回，訂單狀態或使用者餘額會不會狀態錯誤？

有經驗的開發者知道應使用資料庫事務，讓多條資料庫查詢要麼全部執行(事務提交/commit)，要麼全部取消掉(事務回滾/rollback)。 而筋斗雲已經幫我們自動使用了事務確保資料一致性。

筋斗雲一次介面呼叫中的所有資料庫查詢都在一個事務中。 開發者一般不必自行使用事務，除非為了優化併發和資料庫鎖。