程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> 關於MYSQL數據庫 >> 操作mysql數據庫的類

操作mysql數據庫的類

編輯：關於MYSQL數據庫

class MySQL {
private $server = "";
private $user = "";
private $passWord = "";
private $database = "";
private $linkMode = 1;
private $link_id = 0;
private $query_id = 0;
private $query_times = 0;
private $result = array ();
private $fetchMode = MySQL_ASSOC;
private $err_no = 0;
private $err_msg;
private $character;
//======================================
// 函數: MySQL()
// 功能: 構造函數
// 參數: 參數類的變量定義
// 說明: 構造函數將自動連接數據庫
// 如果想手動連接去掉自動連接函數
//======================================
public function __construct($server, $user, $passWord, $database, $character = "UTF8", $linkMode = 0) {
if (empty ( $server ) || empty ( $user ) || empty ( $database ))
$this->halt ( "提交的數據庫信息不完整！請檢查服務器地址，用戶和數據庫是否正確有效" );
$this->server = $server;
$this->user = $user;
$this->password = $passWord;
$this->database = $database;
$this->linkMode = $linkMode;
$this->character = $character;
$this->connect ();
}
//======================================
// 函數: connect($server,$user,$passWord,$database)
// 功能: 連接數據庫
// 參數: $server 主機名, $user 用戶名
// 參數: $passWord 密碼, $database 數據庫名稱
// 返回: 0:失敗
// 說明: 默認使用類中變量的初始值
//======================================
public function connect($server = "", $user = "", $passWord = "", $database = "") {
$server = $server ? $server : $this->server;
$user = $user ? $user : $this->user;
$password = $password ? $password : $this->passWord;
$database = $database ? $database : $this->database;
$this->link_id = $this->linkMode ? mysql_pconnect ( $server, $user, $passWord, $database ) : MySQL_connect ( $server, $user, $passWord, $database );
if (! $this->link_id) {
$this->halt ( "數據庫連接失敗！請檢查各項參數！" );
return 0;
}
if (! MySQL_select_db ( $database, $this->link_id )) {
$this->halt ( "無法選擇數據庫" );
return 0;
}
if ($this->character != "GBK" && $this->character != "UTF8") {
$this->halt ( "輸入的編碼模式不正確！" );
return 0;
}
$this->query ( 'SET NAMES ' . $this->character );
return $this->link_id;
}
//======================================
// 函數: query($sql)
// 功能: 數據查詢
// 參數: $sql 要查詢的SQL語句
// 返回: 0:失敗
//======================================
public function query($sql) {
$this->query_times ++;
$this->query_id = MySQL_query ( $sql, $this->link_id );
if (! $this->query_id) {
$this->halt ( "" . $sql . " 語句執行不成功！" );
return 0;
}
return $this->query_id;
}
//======================================
// 函數: setFetchMode($mode)
// 功能: 設置取得記錄的模式
// 參數: $mode 模式 MYSQL_ASSOC, MYSQL_NUM, MySQL_BOTH
// 返回: 0:失敗
//======================================
public function setFetchMode($mode) {
if ($mode == MYSQL_ASSOC || $mode == MYSQL_NUM || $mode == MySQL_BOTH) {
$this->fetchMode = $mode;
return 1;
} else {
$this->halt ( "錯誤的模式." );
return 0;
}
}
//======================================
// 函數: fetchRow()
// 功能: 從記錄集中取出一條記錄
// 返回: 0: 出錯 record: 一條記錄
//======================================
public function fetchRow() {
$this->record = MySQL_fetch_array ( $this->query_id, $this->fetchMode );
return $this->record;
}
//======================================
// 函數: fetchAll()
// 功能: 從記錄集中取出所有記錄
// 返回: 記錄集數組
//======================================
public function fetchAll() {
$arr [] = array ();
while ( $this->record = MySQL_fetch_array ( $this->query_id, $this->fetchMode ) )
$arr [] = $this->record;
MySQL_free_result ( $this->query_id );
return $arr;
}
//======================================
// 函數: getValue()
// 功能: 返回記錄中指定字段的數據
// 參數: $fIEld 字段名或字段索引
// 返回: 指定字段的值
//======================================
public function getValue($filed) {
return $this->record [$filed];
}
//======================================
// 函數: getquery_id()
// 功能: 返回查詢號
//======================================
public function getquery_id() {
return $this->query_id;
}
//======================================
// 函數: affectedRows()
// 功能: 返回影響的記錄數
//======================================
public function affectedRows() {
return MySQL_affected_rows ( $this->link_id );
}
//======================================
// 函數: recordCount()
// 功能: 返回查詢記錄的總數
// 參數: 無
// 返回: 記錄總數
//======================================
public function recordCount() {
return MySQL_num_rows ( $this->query_id );
}
//======================================
// 函數: getquery_times()
// 功能: 返回查詢的次數
// 參數: 無
// 返回: 查詢的次數
//======================================
public function getquery_times() {
return $this->query_times;
}
//======================================
// 函數: getVersion()
// 功能: 返回MySQL的版本
// 參數: 無
//======================================
public function getVersion() {
$this->query ( "select version() as ver" );
$this->fetchRow ();
return $this->getValue ( "ver" );
}
//======================================
// 函數: getDBSize($database, $tblPrefix=null)
// 功能: 返回數據庫占用空間大小
// 參數: $database 數據庫名
// 參數: $tblPrefix 表的前綴,可選
//======================================
public function getDBSize($database, $tblPrefix = null) {
$sql = "SHOW TABLE STATUS FROM " . $database;
if ($tblPrefix != null) {
$sql .= " LIKE '$tblPrefix%'";
}
$this->query ( $sql );
$size = 0;
while ( $this->fetchRow () )
$size += $this->getValue ( "Data_length" ) + $this->getValue ( "Index_length" );
return $size;
}
//======================================
// 函數: halt($err_msg)
// 功能: 處理所有出錯信息
// 參數: $err_msg 自定義的出錯信息
//=====================================
public function halt($err_msg = "") {
if ($err_msg == "") {
$this->errno = MySQL_errno ();
$this->error = MySQL_error ();
echo "MySQL error: ";
echo $this->errno . ":" . $this->error . " ";
exit ();
} else {
echo "MySQL error: ";
echo $err_msg . " ";
exit ();
}
}
//======================================
// 函數: insertID()
// 功能: 返回最後一次插入的自增ID
// 參數: 無
//======================================
public function insertID() {
return MySQL_insert_id ();
}
//======================================
//函數：close()
//功能：關閉非永久的數據庫連接
//參數：無
//======================================
public function close() {
$link_id = $link_id ? $link_id : $this->link_id;
MySQL_close ( $link_id );
}
//======================================
// 函數: sqlSelect()
// 功能: 返回組合的select查詢值
// 參數: $tbname 查詢的表名
// 參數: $where 條件
// 參數: $fIElds 字段值
// 參數: $orderby 按某字段排序
// 參數: $sort 正序ASC,倒序DESC,$orderby 不為空是有效
// 參數: $limit 取得記錄的條數,0,8
// 返回: 查詢語句
//======================================
function sqlSelect($tbname, $where = "", $limit = 0, $fIElds = "*", $orderby = "", $sort = "DESC") {
$sql = "SELECT " . $fIElds . " FROM " . $tbname . ($where ? " WHERE " . $where : "") . ($orderby ? " ORDER BY " . $orderby . " " . $sort : "") . ($limit ? " limit " . $limit : "");
return $sql;
}
//======================================
// 函數: sqlInsert()
// 功能: Insert插入數據函數
// 參數: $taname 要插入數據的表名
// 參數: $row 要插入的內容 (數組)
// 返回: 記錄總數
// 返回: 插入語句
//======================================
function sqlInsert($tbname, $row) {
foreach ( $row as $key => $value ) {
$sqlfIEld .= $key . ",";
$sqlvalue .= "'" . $value . "',";
}
return "INSERT INTO " . $tbname . "(" . substr ( $sqlfIEld, 0, - 1 ) . ") VALUES (" . substr ( $sqlvalue, 0, - 1 ) . ")";
}
//======================================
// 函數: sqlUpdate()
// 功能: Update更新數據的函數
// 參數: $taname 要插入數據的表名
// 參數: $row 要插入的內容 (數組)
// 參數: $where 要插入的內容的條件
// 返回: Update語句
//======================================
function sqlUpdate($tbname, $row, $where) {
foreach ( $row as $key => $value ) {
$sqlud .= $key . "= '" . $value . "',";
}
return "UPDATE " . $tbname . " SET " . substr ( $sqlud, 0, - 1 ) . " WHERE " . $where;
}
//======================================
// 函數: sqlDelete()
// 功能: 刪除指定條件的行
// 參數: $taname 要插入數據的表名
// 參數: $where 要插入的內容的條件
// 返回: DELETE語句
//======================================
function sqlDelete($tbname, $where) {
if (! $where) {
$this->halt ( "刪除函數沒有指定條件！" );
return 0;
}
return "DELETE FROM " . $tbname . " WHERE " . $where;
}
//======================================
//函數：checkSql SQL語句的過濾
//功能：過濾一些特殊語法
//參數：$db_string 查詢的SQL語句
//參數：$querytype 查詢的類型
//======================================
function checkSql($db_string, $querytype = 'select') {
$clean = '';
$old_pos = 0;
$pos = - 1;
//如果是普通查詢語句，直接過濾一些特殊語法
if ($querytype == 'select') {
$notallow1 = "[^0-9a-z@._-]{1,}(union|sleep|benchmark|load_file|outfile)[^[email protected]]{1,}";
//$notallow2 = "--|/*";
if (eregi ( $notallow1, $db_string )) {
exit ( "Safe Alert: Request Error step 1 !" );
}
}
//完整的SQL檢查
while ( true ) {
$pos = strpos ( $db_string, ''', $pos + 1 );
if ($pos === false) {
break;
}
$clean .= substr ( $db_string, $old_pos, $pos - $old_pos );
while ( true ) {
$pos1 = strpos ( $db_string, ''', $pos + 1 );
$pos2 = strpos ( $db_string, '\', $pos + 1 );
if ($pos1 === false) {
break;
} elseif ($pos2 == false || $pos2 > $pos1) {
$pos = $pos1;
break;
}
$pos = $pos2 + 1;
}
$clean .= '$s$';
$old_pos = $pos + 1;
}
$clean .= substr ( $db_string, $old_pos );
$clean = trim ( strtolower ( preg_replace ( array ('~s+~s' ), array (' ' ), $clean ) ) );
//老版本的MySQL並不支持union，常用的程序裡也不使用union，但是一些黑客使用它，所以檢查它
if (strpos ( $clean, 'union' ) !== false && preg_match ( '~(^|[^a-z])union($|[^[a-z])~s', $clean ) != 0) {
$fail = true;
}
//發布版本的程序可能比較少包括--,#這樣的注釋，但是黑客經常使用它們
elseif (strpos ( $clean, '/*' ) > 2 || strpos ( $clean, '--' ) !== false || strpos ( $clean, '#' ) !== false) {
$fail = true;
}
//這些函數不會被使用，但是黑客會用它來操作文件，down掉數據庫
elseif (strpos ( $clean, 'sleep' ) !== false && preg_match ( '~(^|[^a-z])sleep($|[^[a-z])~s', $clean ) != 0) {
$fail = true;
} elseif (strpos ( $clean, 'benchmark' ) !== false && preg_match ( '~(^|[^a-z])benchmark($|[^[a-z])~s', $clean ) != 0) {
$fail = true;
} elseif (strpos ( $clean, 'load_file' ) !== false && preg_match ( '~(^|[^a-z])load_file($|[^[a-z])~s', $clean ) != 0) {
$fail = true;
} elseif (strpos ( $clean, 'into outfile' ) !== false && preg_match ( '~(^|[^a-z])intos+outfile($|[^[a-z])~s', $clean ) != 0) {
$fail = true;
}
//老版本的MySQL不支持子查詢，我們的程序裡可能也用得少，但是黑客可以使用它來查詢數據庫敏感信息
elseif (preg_match ( '~([^)]*?select~s', $clean ) != 0) {
$fail = true;
}
if (! empty ( $fail )) {
exit ( "Safe Alert: Request Error step 2!" );
} else {
return $db_string;
}
}
//======================================
//函數：析構函數
//功能：釋放類，關閉非永久的數據庫連接
//參數：無
//======================================
public function __destruct() {
$this->close ();
}
}
?>