進階 WWW 程式設計 PHP & MySQL

進階 WWW 程式設計PHP & MySQL 靜宜大學資訊管理學系蔡奇偉副教授 2003-2005

內容大綱 • 範例 • 連結至 MySQL 伺服器 • 關閉與 MySQL 伺服器的連結 • 選取資料庫 • 進行資料庫查詢 • 取得資料列的數目 • 取出資料列 • 資料庫安全

範例以下的程式範例以表格方式把某一資料表的內容列在網頁中。 /* Connecting, selecting database */ $link = mysql_connect("mysql_host", "mysql_user", "mysql_password") or die("Could not connect"); print "Connected successfully"; mysql_select_db("my_database“, $link) or die("Could not select database"); /* Performing SQL query */ $query = "SELECT * FROM my_table"; $result = mysql_query($query) or die("Query failed");

/* Printing results in HTML */ • print "<table>\n"; • while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) { • print "\t<tr>\n"; • foreach ($line as $col_value) { • print "\t\t<td>$col_value</td>\n"; • } • print "\t</tr>\n"; • } • print "</table>\n"; • /* Free resultset */ • mysql_free_result($result); • /* Closing connection */ • mysql_close($link);

資料庫程式的架構 • 以上的程式範例展示了典型的資料庫程式架構，即由下面六個步驟所組成： • 連結至資料庫系統主機。 • 選擇資料庫。 • 使用 SQL 敘述來操作資料庫，如查詢資料、加入新資料、刪除舊資料、更新資料、…、等等。 • 處理資料庫的操作結果 • 解除資料庫 • 關閉與資料庫系統的連結。

使用資料庫所需的資訊 • 資料庫主機的網址 • 資料庫系統的帳號與密碼 • 資料庫名稱 • 資料庫的結構 • 查詢或更新的項目 • 查詢或更新所用的 SQL 敘述

連結至 MySQL 伺服器 resource mysql_connect ( [string server [, string username [, string password [, bool new_link [, int client_flags ]]]]] ) Returns a MySQL link identifier on success, or FALSE on failure. mysql_connect() establishes a connection to a MySQL server. The following defaults are assumed for missing optional parameters: server = 'localhost:3306', username = name of the user that owns the server process and password = empty password. The server parameter can also include a port number. eg. "hostname:port" or a path to a socket eg. ":/path/to/socket" for the localhost.

If a second call is made to mysql_connect() with the same arguments, no new link will be established, but instead, the link identifier of the already opened link will be returned. The new_link parameter modifies this behavior and makes mysql_connect() always open a new link, even if mysql_connect() was called before with the same parameters. The client_flags parameter can be a combination of the constants • MYSQL_CLIENT_SSL • MYSQL_CLIENT_COMPRESS • MYSQL_CLIENT_IGNORE_SPACE • MYSQL_CLIENT_INTERACTIVE. • The link to the server will be closed as soon as the execution of the script ends, unless it's closed earlier by explicitly calling mysql_close().

範例 $link = mysql_connect("localhost", "mysql_user", "mysql_password") or die("Could not connect: " . mysql_error()); print ("Connected successfully"); mysql_close($link);

resource mysql_pconnect ( [string server [, string username [, string password [, int client_flags]]]] ) Returns a positive MySQL persistent link identifier on success, or FALSE on error. mysql_pconnect() establishes a connection to a MySQL server. The following defaults are assumed for missing optional parameters: server = 'localhost:3306', username = name of the user that owns the server process and password = empty password. The server parameter can also include a port number. eg. "hostname:port" or a path to a socket eg. ":/path/to/socket" for the localhost.

The client_flags parameter can be a combination of the constants • MYSQL_CLIENT_SSL • MYSQL_CLIENT_COMPRESS • MYSQL_CLIENT_IGNORE_SPACE • MYSQL_CLIENT_INTERACTIVE.

mysql_pconnect() acts very much like mysql_connect() with two major differences. First, when connecting, the function would first try to find a (persistent) link that's already open with the same host, username and password. If one is found, an identifier for it will be returned instead of opening a new connection. Second, the connection to the SQL server will not be closed when the execution of the script ends. Instead, the link will remain open for future use (mysql_close() will not close links established by mysql_pconnect()). The optional client_flags parameter became available in PHP 4.3.0. This type of link is therefore called 'persistent'.

關閉與 MySQL 伺服器的連結 bool mysql_close ( [resource link_identifier ] ) Returns TRUE on success or FALSE on failure. mysql_close() closes the connection to the MySQL server that's associated with the specified link identifier. If link_identifier isn't specified, the last opened link is used. Using mysql_close() isn't usually necessary, as non-persistent open links are automatically closed at the end of the script's execution. Note: mysql_close() will not close persistent links created by mysql_pconnect().

範例 $link = mysql_connect("localhost", "mysql_user", "mysql_password") or die("Could not connect: " . mysql_error()); print ("Connected successfully"); mysql_close($link);

選取資料庫 bool mysql_select_db ( string database_name [, resource link_identifier ] ) Returns TRUE on success or FALSE on failure. mysql_select_db() sets the current active database on the server that's associated with the specified link identifier. If no link identifier is specified, the last opened link is assumed. If no link is open, the function will try to establish a link as if mysql_connect() was called without arguments, and use it. Every subsequent call to mysql_query() will be made on the active database.

進行資料庫查詢 resource mysql_query ( string query [, resource link_identifier [, int result_mode]] ) mysql_query() sends a query to the currently active database on the server that's associated with the specified link identifier. If link_identifier isn't specified, the last opened link is assumed. If no link is open, the function tries to establish a link as if mysql_connect() was called with no arguments, and use it. The optional result_mode parameter can be MYSQL_USE_RESULT and MYSQL_STORE_RESULT. It defaults to MYSQL_STORE_RESULT, so the result is buffered.

Only for SELECT, SHOW, EXPLAIN, or DESCRIBE statements mysql_query() returns a resource identifier or FALSE if the query was not executed correctly. For other type of SQL statements, mysql_query() returns TRUE on success and FALSE on error. A non-FALSE return value means that the query was legal and could be executed by the server. It does not indicate anything about the number of rows affected or returned. It is perfectly possible for a query to succeed but affect no rows or return no rows. mysql_query() will also fail and return FALSE if you don't have permission to access the table(s) referenced by the query.

範例 • The following query is syntactically invalid, so mysql_query() fails and returns FALSE: • $result = mysql_query("SELECT * WHERE 1=1") • or die("Invalid query: " . mysql_error()); 範例 • The following query is semantically invalid if my_col is not a column in the table my_tbl, so mysql_query() fails and returns FALSE: • $result = mysql_query("SELECT my_col FROM my_tbl WHERE 1=1") • or die("Invalid query: " . mysql_error());

若 SQL 敘述包含引號，要注意是否造成 PHP 字串的混淆，譬如：底下的敘述會造成 PHP 語法錯誤 • $query = “select name, age from user where type=“student””; • 必須改成 • $query = “select name, age from user where type= \“student\””; • 或使用單引號 • $query = ‘select name, age from user where type=“student”’; • 或 • $query = “select name, age from user where type=‘student’”;

若SQL 敘述中包含字串資料，我們可以用下列函式把字串中的特殊字元換成對應的逸出碼，以避免資料庫被惡意地破壞： string mysql_real_escape_string ( string unescaped_string [, resource link_identifier] ) mysql_real_escape_string 把 unescaped_string 中的 NULL, \x00, \n, \r, \, ‘, “, 和 \x1a 等字元前加上反斜線字元（\）

永遠為真 • 舉例來說，以下程式依據表單傳回的帳號密碼來查詢資料庫： • $query = "SELECT * FROM users WHERE user='{$_POST['username']}' • AND password='{$_POST['password']}'";mysql_query($query); • 如果瀏覽者填入的帳號密碼如下： • username: alice • password: ' OR ''= ' • 則 SQL 敘述變成 • SELECT * FROM users WHERE name=‘alice' AND password='' OR ''='' • 使得 alice 不需要密碼即可進入資料庫。

如果把程式改為： • $username =mysql_real_escape_string($_POST['username']); • $password =mysql_real_escape_string($_POST['password']); • $query = "SELECT * FROM users WHERE user=‘$username' • AND password=‘$password’“;mysql_query($query); • 就可以避免前述的問題，因為產生的 SQL 敘述變成： • SELECT * FROM users WHERE name=‘alice' AND • password='\' OR \'\'=\''

我們也可以運用以下的方式： // Quote variable to make safefunction quote_smart($value){// Stripslashes if (get_magic_quotes_gpc()) { $value = stripslashes($value); }// Quote if not integer if (!is_numeric($value)) { $value = "'" . mysql_real_escape_string($value) . "'"; } return $value;}// Make a safe query $query = sprintf("SELECT * FROM users WHERE user=%s AND password=%s", quote_smart($_POST['username']), quote_smart($_POST['password'])); mysql_query($query);

Assuming the query succeeds, you can call mysql_num_rows() to find out how many rows were returned for a SELECT statment or mysql_affected_rows() to find out how many rows were affected by a DELETE, INSERT, REPLACE, or UPDATE statement. Only for SELECT,SHOW,DESCRIBE or EXPLAIN statements, mysql_query() returns a new result identifier that you can pass to mysql_fetch_array() and other functions dealing with result tables. When you are done with the result set, you can free the resources associated with it by calling mysql_free_result(). Although, the memory will automatically be freed at the end of the script's execution.

resource mysql_unbuffered_query ( string query [, resource link_identifier [, int result_mode]] ) mysql_unbuffered_query() sends a SQL query query to MySQL, without fetching and buffering the result rows automatically, as mysql_query() does. On the one hand, this saves a considerable amount of memory with SQL queries that produce large result sets. On the other hand, you can start working on the result set immediately after the first row has been retrieved: you don't have to wait until the complete SQL query has been performed. When using multiple DB-connects, you have to specify the optional parameter link_identifier.

The optional result_mode parameter can be MYSQL_USE_RESULT and MYSQL_STORE_RESULT. It defaults to MYSQL_USE_RESULT, so the result is not buffered. See also mysql_query() for the counterpart of this behaviour. Note: The benefits of mysql_unbuffered_query() come at a cost: You cannot use mysql_num_rows() on a result set returned from mysql_unbuffered_query(). You also have to fetch all result rows from an unbuffered SQL query, before you can send a new SQL query to MySQL.

取得資料列的數目 int mysql_num_rows ( resource result ) mysql_num_rows() returns the number of rows in a result set. This command is only valid for SELECT statements. To retrieve the number of rows affected by a INSERT, UPDATE or DELETE query, use mysql_affected_rows(). 範例 $link = mysql_connect("localhost", "mysql_user", "mysql_password"); mysql_select_db("database", $link); $result = mysql_query("SELECT * FROM table1", $link); $num_rows = mysql_num_rows($result); echo "$num_rows Rows\n";

int mysql_affected_rows ( [resource link_identifier ] ) mysql_affected_rows() returns the number of rows affected by the last INSERT, UPDATE or DELETE query associated with link_identifier. If the link identifier isn't specified, the last link opened by mysql_connect() is assumed. Note: If you are using transactions, you need to call mysql_affected_rows() after your INSERT, UPDATE, or DELETE query, not after the commit. If the last query was a DELETE query with no WHERE clause, all of the records will have been deleted from the table but this function will return zero.

Note: When using UPDATE, MySQL will not update columns where the new value is the same as the old value. This creates the possiblity that mysql_affected_rows() may not actually equal the number of rows matched, only the number of rows that were literally affected by the query. mysql_affected_rows() does not work with SELECT statements; only on statements which modify records. To retrieve the number of rows returned by a SELECT, use mysql_num_rows(). If the last query failed, this function will return -1.

範例 Delete-Query /* connect to database */ mysql_pconnect("localhost", "mysql_user", "mysql_password") or die("Could not connect: " . mysql_error()); /* this should return the correct numbers of deleted records */ mysql_query("DELETE FROM mytable WHERE id < 10"); printf ("Records deleted: %d\n", mysql_affected_rows()); /* without a where clause in a delete statement, it should return 0 */ mysql_query("DELETE FROM mytable"); printf ("Records deleted: %d\n", mysql_affected_rows());

範例 Update-Query /* connect to database */ mysql_pconnect("localhost", "mysql_user", "mysql_password") or die("Could not connect: " . mysql_error()); /* Update records */ mysql_query("UPDATE mytable SET used=1 WHERE id < 10"); printf ("Updated records: %d\n", mysql_affected_rows()); mysql_query("COMMIT");

結果資料表 row pointer 取出資料列使用 mysql_query() 與 SELECT 敘述所得的查詢結果是一個資料表。我們必須一筆一筆地取出其中的資料來處理。PHP 提供好幾種取出資料的函式，你可以依需要選用這些函式。此外，對每一個這類資料表，PHP 內部有一個「資料列指標（row pointer）」指到下一筆取出的資料列。你可以用 mysql_data_seek() 函式來設定此指標的位址，從而挑選下一筆取取出的資料列。

array mysql_fetch_row ( resource result ) Returns an array that corresponds to the fetched row, or FALSE if there are no more rows. mysql_fetch_row() fetches one row of data from the result associated with the specified result identifier. The row is returned as an array. Each result column is stored in an array offset, starting at offset 0. Subsequent call to mysql_fetch_row() would return the next row in the result set, or FALSE if there are no more rows.

範例 • <?php$result = mysql_query("SELECT id,email FROM people WHERE id = 42");if (!$result) { echo 'Could not run query: ' . mysql_error(); exit;} • $row = mysql_fetch_row($result);if ($row) { • echo $row[0]; // 42 (id) • echo $row[1]; // the email value • } • ?>

範例我們可以用 list 指令把取出的結果直接存入變數中，譬如： <?php$result = mysql_query("SELECT id,email FROM people WHERE id = 42");if (!$result) { echo 'Could not run query: ' . mysql_error(); exit;} list($id, $email) = mysql_fetch_row($result);echo $id; // 42 (id) echo $email; // the email value } ?>

array mysql_fetch_assoc ( resource result ) Returns an associative array that corresponds to the fetched row, or FALSE if there are no more rows. mysql_fetch_assoc() is equivalent to calling mysql_fetch_array() with MYSQL_ASSOC for the optional second parameter. It only returns an associative array. This is the way mysql_fetch_array() originally worked. If you need the numeric indices as well as the associative, use mysql_fetch_array(). If two or more columns of the result have the same field names, the last column will take precedence. To access the other column(s) of the same name, you either need to access the result with numeric indices by using mysql_fetch_row() or add alias names. See the example at the mysql_fetch_array() description about aliases. An important thing to note is that using mysql_fetch_assoc() is not significantly slower than using mysql_fetch_row(), while it provides a significant added value.

範例 An expanded mysql_fetch_assoc() example $conn = mysql_connect("localhost", "mysql_user", "mysql_password"); if (!$conn) { echo "Unable to connect to DB: " . mysql_error(); exit; } if (!mysql_select_db("mydbname")) { echo "Unable to select mydb: " . mysql_error(); exit; } $sql = "SELECT id as userid, fullname, userstatus FROM sometable WHERE userstatus = 1"; $result = mysql_query($sql);

if (!$result) { echo "Could not successfully run query ($sql) from DB: " . mysql_error(); exit; } if (mysql_num_rows($result) == 0) { echo "No rows found, nothing to print so am exiting"; exit; } // While a row of data exists, put that row in $row as an associative array // Note: If you're expecting just one row, no need to use a loop // Note: If you put extract($row); inside the following loop, you'll // then create $userid, $fullname, and $userstatus while ($row = mysql_fetch_assoc($result)) { echo $row["userid"]; echo $row["fullname"]; echo $row["userstatus"]; } mysql_free_result($result);

array mysql_fetch_array ( resource result [, int result_type ] ) Returns an array that corresponds to the fetched row, or FALSE if there are no more rows. mysql_fetch_array() is an extended version of mysql_fetch_row(). In addition to storing the data in the numeric indices of the result array, it also stores the data in associative indices, using the field names as keys. If two or more columns of the result have the same field names, the last column will take precedence. To access the other column(s) of the same name, you must use the numeric index of the column or make an alias for the column. For aliased columns, you cannot access the contents with the original column name.

An important thing to note is that using mysql_fetch_array() is not significantly slower than using mysql_fetch_row(), while it provides a significant added value. The optional second argument result_type in mysql_fetch_array() is a constant and can take the following values: MYSQL_ASSOC, MYSQL_NUM, and MYSQL_BOTH. This feature was added in PHP 3.0.7. MYSQL_BOTH is the default for this argument. By using MYSQL_BOTH, you'll get an array with both associative and number indices. Using MYSQL_ASSOC, you only get associative indices (as mysql_fetch_assoc() works), using MYSQL_NUM, you only get number indices (as mysql_fetch_row() works).

範例 mysql_fetch_array with MYSQL_NUM mysql_connect("localhost", "mysql_user", "mysql_password") or die("Could not connect: " . mysql_error()); mysql_select_db("mydb"); $result = mysql_query("SELECT id, name FROM mytable"); while ($row = mysql_fetch_array($result, MYSQL_NUM)) { printf ("ID: %s Name: %s", $row[0], $row[1]); } mysql_free_result($result);

範例 mysql_fetch_array with MYSQL_ASSOC mysql_connect("localhost", "mysql_user", "mysql_password") or die("Could not connect: " . mysql_error()); mysql_select_db("mydb"); $result = mysql_query("SELECT id, name FROM mytable"); while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) { printf ("ID: %s Name: %s", $row["id"], $row["name"]); } mysql_free_result($result);

範例 mysql_fetch_array with MYSQL_BOTH mysql_connect("localhost", "mysql_user", "mysql_password") or die("Could not connect: " . mysql_error()); mysql_select_db("mydb"); $result = mysql_query("SELECT id, name FROM mytable"); while ($row = mysql_fetch_array($result, MYSQL_BOTH)) { printf ("ID: %s Name: %s", $row[0], $row["name"]); } mysql_free_result($result);

bool mysql_data_seek ( resource result_identifier, int row_number ) mysql_data_seek() moves the internal row pointer of the MySQL result associated with the specified result identifier to point to the specified row number. The next call to mysql_fetch_row() would return that row. Returns TRUE on success or FALSE on failure. row_number starts at 0. The row_number should be a value in the range from 0 to mysql_num_rows() - 1. However if the result set is empty (mysql_num_rows() == 0), a seek to 0 will fail with a E_WARNING and mysql_data_seek() will return FALSE. Note: The function mysql_data_seek() can be used in conjunction only with mysql_query(), not with mysql_unbuffered_query().

範例 $query = 'SELECT last_name, first_name FROM friends'; $result = mysql_query($query); if (!$result) { die('Query failed: ' . mysql_error()); } /* fetch rows in reverse order */ for ($i = mysql_num_rows($result) - 1; $i >= 0; $i--) { if (!mysql_data_seek($result, $i)) { echo "Cannot seek to row $i: " . mysql_error() . "\n"; continue; } if (!($row = mysql_fetch_assoc($result))) { continue; } echo $row['last_name'] . ' ' . $row['first_name'] . "<br />\n"; }

資料庫安全 Many web developers are unaware of how SQL queries can be tampered with, and assume that an SQL query is a trusted command. It means that SQL queries are able to circumvent access controls, thereby bypassing standard authentication and authorization checks, and sometimes SQL queries even may allow access to host operating system level commands. Direct SQL Command Injection is a technique where an attacker creates or alters existing SQL commands to expose hidden data, or to override valuable ones, or even to execute dangerous system level commands on the database host. This is accomplished by the application taking user input and combining it with static parameters to build a SQL query. The following examples are based on true stories, unfortunately. Owing to the lack of input validation and connecting to the database on behalf of a superuser or the one who can create users, the attacker may create a superuser in your database. （參閱 PHP 手冊所舉的範例）

進階 WWW 程式設計 PHP & MySQL