//This method must be static, and must return an instance of the object if the object
//does not already exist.
public static function getInstance() {
if (!self::$instance instanceof self) {
self::$instance = new self;
}
return self::$instance;
}
// The clone and wakeup methods prevents external instantiation of copies of the Singleton class,
// thus eliminating the possibility of duplicate objects.
public function __clone() {
trigger_error('Clone is not allowed.', E_USER_ERROR);
}
public function __wakeup() {
trigger_error('Deserializing is not allowed.', E_USER_ERROR);
}
public function get_wisher_id_by_name($name) {
$name = $this->real_escape_string($name);
$wisher = $this->query("SELECT id FROM wishers WHERE name = '"
. $name . "'");
if ($wisher->num_rows > 0){ $row = $wisher->fetch_row(); return $row[0]; } else return null;
}
对于 Oracle 数据库:
public function get_wisher_id_by_name($name) {
$query = "SELECT id FROM wishers WHERE name = :user_bv";
$stid = oci_parse($this->con, $query);
oci_bind_by_name($stid, ':user_bv', $name);
oci_execute($stid);
//Because user is a unique value I only expect one row
$row = oci_fetch_array($stid, OCI_ASSOC); if ($row) return $row["ID"]; else return null;
}
该代码块执行 SELECT ID FROM wishers WHERE name = [variable for name of the wisher] 查询。查询结果是一个数组,其中包含符合查询条件的记录中的 ID。如果该数组不为空,则自动表示它包含一个元素,这是因为在创建表期间将字段名称指定为 UNIQUE。在本示例中,该函数返回 $result 数组的第一个元素(编号为零的元素)。如果数组为空,该函数将返回空值。
安全注意事项:对于 MySQL 数据库,将转义 $name 字符串以防止 SQL 注入攻击。请参见有关 SQL 注入的维基百科和 mysql_real_escape_string 文档。虽然在本教程的上下文中,您不会遇到有害 SQL 注入的风险,但最佳做法是转义存在此类攻击风险的 MySQL 查询中的字符串。Oracle 数据库通过使用绑定变量来避免该问题。
函数 get_wishes_by_wisher_id
该函数要求将许愿者 ID 作为输入参数,并返回为许愿者注册的愿望。
请输入以下代码块:
对于 MySQL 数据库:
public function get_wishes_by_wisher_id($wisherID) { return $this->query("SELECT id, description, due_date FROM wishes WHERE wisher_id=" . $wisherID); }
对于 Oracle 数据库:
public function get_wishes_by_wisher_id($wisherID) {
$query = "SELECT id, description, due_date FROM wishes WHERE wisher_id = :id_bv";
$stid = oci_parse($this->con, $query);
oci_bind_by_name($stid, ":id_bv", $wisherID);
oci_execute($stid);
return $stid;
}
该代码块执行 "SELECT id, description, due_date FROM wishes WHERE wisherID=" . $wisherID 查询并返回一个结果集,这是一个符合查询条件的记录数组。(出于数据库性能和安全考虑,Oracle 数据库使用绑定变量。)数据选择是按 wisherID 执行的,这是 wishes 表的外键。
将连接到数据库并获取许愿者 ID 的代码替换为 get_wisher_id_by_name 函数调用。
对于 MySQL 数据库,替换的代码为:
$con = mysqli_connect("localhost", "phpuser", "phpuserpw");
if (!$con) {
exit('Connect Error (' . mysqli_connect_errno() . ') '
. mysqli_connect_error());
}
//set the default client character set
mysqli_set_charset($con, 'utf-8');
mysqli_select_db($con, "wishlist");
$user = mysqli_real_escape_string($con, $_GET['user']);
$wisher = mysqli_query($con, "SELECT id FROM wishers WHERE name='" . $user . "'");
if (mysqli_num_rows($wisher) < 1) {
exit("The person " . $_GET['user'] . " is not found. Please check the spelling and try again");
}
$row = mysqli_fetch_row($wisher); $wisherID = $row[0];
mysqli_free_result($wisher);
$wisherID = WishDB::getInstance()->get_wisher_id_by_name($_GET["user"]);
if (!$wisherID) {
exit("The person " .$_GET["user"]. " is not found. Please check the spelling and try again" );
}
对于 Oracle 数据库,替换的代码为:
$con = oci_connect("phpuser", "phpuserpw", "localhost/XE", "AL32UTF8");
if (!$con) {
$m = oci_error();
echo $m['message'], "\n";
exit;
}
$query = "SELECT id FROM wishers WHERE name = :user_bv";
$stid = oci_parse($con, $query);
$user = $_GET["user"];
oci_bind_by_name($stid, ':user_bv', $user);
oci_execute($stid);
//Because user is a unique value I only expect one row
$row = oci_fetch_array($stid, OCI_ASSOC);
if (!$row) {
echo("The person " . $user . " is not found. Please check the spelling and try again" ); exit;
}
$wisherID = $row["ID"]; $wisherID = WishDB::getInstance()->get_wisher_id_by_name($_GET["user"]);
if (!$wisherID) {
exit("The person " .$_GET["user"]. " is not found. Please check the spelling and try again" );
}