Linux應用集成MySQL數據庫訪問技巧

本文描述了MySQL，一種利用第三方數據庫開發電子貿易和其它復雜、動態網站的有效工具。MySQL 是一種快速、多線程和全功能的 SQL服務器。除了描述MySQL系統的基本體系結構以外，本文還提供了以Tcl和C++編寫的簡單示例，幫助您開發支持數據庫的Web應用。
一個必須存儲或訪問大量信息的應用程序可以從使用第三方數據庫產品中受益匪淺。在對信息的訪問必須在程序的多個實例上進行時更是如此。基於Web的應用包括電子貿易）就是它的良好例證。
為什麼使用獨立數據庫？
Web服務器必須使其處理腳本有辦法來存儲有關供其以後訪問的狀態信息。盡管有可能使用比較原始一些的方法--例如轉儲到文本文件或開發自制的迷你數據庫 --但只有成熟的數據庫應用才能提供更為復雜的Web應用所需的所有服務。因為有一些免費獲得的軟件包可用於該目的，所以編寫定制的特定於應用的數據庫引擎並無太大好處。 另外，使用第三方數據庫還使Web開發者不必投入到開發和維護數據庫的任務中。
MySQL數據庫
通過使用腳本語言和編譯型系統語言例如 C），將數據庫集成到Linux應用就可能相當容易。可免費獲得的MySQL在GNU Public License下發行）數據庫提供了一系列復雜的SQL功能，並易於集成到應用中。MySQL是快速、多線程的，並支持ANSI和ODBC SQL標准。加上第三方軟件，MySQL就支持用於事務處理應用的事務安全的表。
注：什麼是事務處理？
事務是需要以原子方式執行的對數據庫所做的一系列更改。它們要麼必須全部執行，要麼一個都不執行。 例如，在Web上銷售產品時所有必需的數據庫更改組成一個事務。
數據庫需要同時減去客戶帳戶余額和產品庫存，否則失敗並且一個操作都不執行。
無論服務器出於何種原因發生崩潰都不應該引起事務被部分執行。例如帳單多算、產品沒有交付，或者庫存不實等都有可能是部分完成的事務的結果。
支持事務處理的數據庫可以將一組數據庫代碼封裝在一個事務中，在事務執行期間的任何失敗會讓數據庫回滾到事務開始之前的狀態。
這是通過維護所有數據庫操作的日志，以及其原始狀態表的副本來實現的，在失敗後下一次重新啟動服務器時允許回滾操作。 這種時間和空間上的開銷是事務安全數據庫系統所必需的一種折衷。
單一MySQL服務器控制著一系列數據庫，它們都可以通過服務器以類似方式來訪問。每個數據庫實際上都是一組任意數量的表，概念與其它SQL數據庫的用戶類似。每個表都由帶類型的數據列組成。數據可以是整數、實數值、字符串或其它類型，包括原始二進制流。 表中的每一行都是存儲在數據庫中的一個記錄。
MySQL被設計和構造成客戶機／服務器。服務器mysqld可以在能從因特網訪問到的任何機器上運行最好與Web服務器在同一台或最接近的一台機器上，以確保合理的響應時間）。MySQL客戶機使用請求來與MySQL服務器聯系，修改或查詢服務器所擁有的數據庫。在支持數據庫的Web應用程序中，數據庫客戶機是Web服務器或由Web服務器產生的CGI腳本。這些客戶機可以用高級腳本語言或低級系統語言編寫，只要存在這種語言的數據庫API即可。在Linux中，大多數腳本語言是以C 實現的，因為存在MySQL C API，所以要將MySQL支持添加到任何現有的腳本語言或工具應該很容易。絕大部分腳本語言已經完成了這一步。
MySQL API
MySQL API可用於各種語言，包括幾乎所有編寫網站後端所實際使用的語言。 使用這些API，我們可以構建由Web服務器控制的 MySQL客戶機。
API用於數據庫訪問）以基於連接的模式工作。客戶機必須做的第一件事是打開與MySQL服務器的連接。這包括適當地使用服務器認識的用戶名和口令來對連接進行身份認證。建立了連接後，服務器選擇要使用的特定數據庫。確定了初始化後，客戶機應用程序就我們來說是服務器方CGI腳本）就能自由地與數據庫以兩種方式中的一種進行交互：可以運行常規SQL命令，包括添加和刪除表，以及向它們添加記錄；也可以對返回結果的數據庫運行查詢。查詢生成一組與查詢匹配的記錄，然後，客戶機可以逐一訪問記錄，直到查看完所有記錄，或者客戶機取消暫掛的記錄檢索。一旦腳本完成了對數據庫的操作後，與服務器的連接就被關閉。
要構建集成數據庫訪問的網站，需要編寫CGI腳本來根據數據庫狀態生成動態結果。Web服務器啟動CGI腳本，然後將適當格式化的HTML輸出到它們的標准輸出流中。Web服務器捕捉到HTML後將它發送回客戶機，如同請求是對靜態HTML頁面進行的那樣。 在生成 HTML 的過程中，腳本可以修改數據庫，也可以查詢並將結果合並到它們的輸出中。
作為簡單解釋上述過程的一個示例，下面的代碼以C和Tcl編寫）查詢一個包含某公司供銷售的產品清單的數據庫。 這絕沒有使用兩種語言MySQL API的所有特性，但提供了快速、簡易擴展的示例，可以對數據庫內容執行任何SQL命令。 在該例中，腳本顯示了低於特定價格的所有產品。在實踐中，用戶可能在Web浏覽器中輸入該價格，然後將它發給服務器。 我們省去了從環境變量中進行讀取來確定 HTML 表單值的細節，因為它與不支持數據庫的 CGI 腳本中執行的情況沒有什麼差別。 為清晰起見，我們假設事先設置了特定一些參數例如要查詢的價格）。
以下代碼是使用免費獲得的Tcl Generic Database Interface以Tcl實現的。這樣一種接口的好處在於Tcl是解釋型的，可以對代碼進行迅速開發和快速修改。
Tcl示例

#This code prints out all products in the database
# that are below a specified price (assumed to have been determined
# beforehand, and stored in the variable targetPrice)
# The output is in HTML table format, appropriate for CGI output

#load the SQL shared object library. the Tcl interpreter could also
#have been compiled with the library, making this line unnecessary
load /home/aroetter/tcl-sql/sql.so

#these are well defined beforehand, or they could
#be passed into the script
set DBNAME "clientWebSite";
set TBLNAME "products";
set DBHOST "backend.company.com"
set DBUSER "mysqluser"
set DBPASSWD "abigsecret"

set targetPrice 200;

#connect to the database
set handle [sql connect $DBHOST $DBUSER $DBPASSWD]
sql selectdb $handle $DBNAME ;# get test database

#run a query using the specified sql code
sql query $handle "select * from $TBLNAME where price <= $targetPrice"


#print out html table header
puts "<table border=4>"
puts "<th>Product Id <th width=200>Description <th>Price (\$)"

#output table rows - each fetchrow retrieves one result
#from the sql query
while {[set row [sql fetchrow $handle]] != ""} {
    set prodid [lindex $row 0]
    set descrip [lindex $row 1]
    set price [lindex $row 2]
    puts "<tr><td>$prodid <td align=center>$descrip <td>$price"
}

puts "</table>"

#empty the query result buffer - should already be empty in this case
sql endquery $handle
#close the db connection - in practice this same connection
#is used for multiple queries
sql disconnect $handle

下面的代碼是使用正式MySQL C++ API MySQL++以C++編寫的等價腳本。該版本的優勢在於它是編譯型的，因此比解釋語言更快。經常用在特定站點的數據庫代碼應該以C或C++編寫，然後由腳本或直接由Web服務器訪問，以改進整體運行時間。
C++示例

#include 
#include 
#include 

const char *DBNAME = "clientWebSite";
const char *DBTABLE = "products";
const char *DBHOST = "backend.company.com";
const char *DBUSER = "mysqluser";
const char *DBPASSWD = "abigsecret":

int main() {
  try {
    //open the database connection and query
    Connection con(DBNAME, DBHOST, DBUSER, DBPASSWD);
    Query query = con.query();

    //write valid sql code to the query object
    query << "select * from " << DBTABLE;

    //run the query and store the results
    Result res = query.store();

    //write out the html table header
    cout << "<table border=4>\n";
    cout << "<th>Product Id <th width=200>Description"
 << "<th>Price ($)" << endl;

    Result::iterator curResult;
 Row row;

    //iterate over each result and put it into an html table
    for (curResult = res.begin(); curResult != res.end(); curResult++) {
      row = *curResult;
      cout << "<tr><td align=center>" << row[0]
         << "<td>" << row[1]
         << "<td>" << row[2] << endl;

    }
    cout << "</table>" << endl;

  } catch (BadQuery er) {
    // handle a bad query (usually caused by a sql syntax error)
    cerr << "Error: " << er.error <<  endl;
    return -1;

  } catch (BadConversion er) {
//handle conversion errors out of the database as well
    cerr << "Error: Can't convert \"" << er.data << "\" to a \""
 << er.type_name << "\"." << endl;
    return -1;
  }
  return 0;
}

安全性
在Web上創建以Web支持的應用有一些開發者需要考慮的問題。所有有關Web服務器上CGI程序的問題，例如Web服務器處理許可權和腳本方的輸入檢查，也仍然需要考慮。
除此之外，維護數據庫系統的安全性也很有必要。這涉及保護數據庫服務器的許可權系統，以及使從數據庫客戶機到服務器的連接安全。
MySQL提供了深入的安全性系統，有人形容它是“高級但不標准”。MySQL允許根據用戶名、客戶機主機和要訪問的數據庫對客戶機進行訪問。要創建安全的系統，讓所有用戶使用強口令，不要給他們任何他們不是絕對需要的訪問權。這包括表面上無害的特權，例如可以讓用戶查看所有正在運行的進程包括更改其他用戶口令的那些）的處理特權。最好的辦法是以無特權的Unix用戶運行服務器進程本身，這樣如果一個數據庫被洩露，也不至於擊垮整個系統。這與以用戶nobody而非root用戶運行httpd類似。 描述系統訪問的表是作為單獨的MySQL數據庫存儲的，可以由MySQL root用戶更新。 請注意，MySQL服務器根據MySQL用戶名授予特權，這些用戶名與Unix用戶名不同。不過，有一個MySQL root用戶名，它對數據庫有全部權限。一旦服務器確定了連接客戶機是誰，以及它們在嘗試連接什麼之後，就根據給定的一組權限來控制訪問權。要防止訪問表中主機名被DNS電子欺騙，可以輸入所有主機的IP地址，或請求服務器將IP地址解析回原始主機名來使其他人截獲DNS請求和回答更困難。
除了服務器訪問表以外，與服務器的通信也必須很安全。從客戶機登錄到服務器上時，口令不以純文本方式發送；不過所有後續 SQL 命令將以純文本方式發送。為達到更高的安全性，使用ssh來設置端口轉發。它將服務器和客戶機之間的所有通信進行加密，防止有人在傳輸中觀察它。來自客戶機的數據發送到客戶機本地機器中本地ssh服務器所偵聽的端口上。它由本地ssh服務器使用，加密後發送給遠程ssh服務器，由它進行解密並轉發到 MySQL服務器端口。
在實際中，最安全的方法是在 Web 服務器所在的機器上運行數據庫服務器，並讓由Web服務器產生的CGI腳本通過UNIX本地）套接字與MySQL服務器進行通信。該設置可以讓數據庫管理員禁用所有與MySQL服務器的遠程連接。如果Web和數據庫服務器必須位於不同的機器上，加密它們之間的所有通信，或者將兩台機器通過其自己專用的、物理上隔離的網絡連接。只創建一個由Web服務器使用的用戶帳戶除 root 用戶外）以登錄到數據庫服務器。
由數據庫驅動的網站是一些功能強大的工具，可以讓開發者創建提供更新信息的動態站點，並讓由客戶機發起的更改在多個會話之間持續。後端數據庫的使用對於管理電子貿易和其它應用的用戶來說必不可少。通過使用可免費獲得的軟件，有可能建立由數據庫驅動的站點，安全地將數據庫連通性集成到站點現有的CGI體系結構中。