数据库操作类

php代码    

<?php 
/*
 * 数据库操作类
 * 
 */


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 ( &#39;SET NAMES &#39; . $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 ( "<font color=red>" . $sql . "</font> 语句执行不成功！" ); 
            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 &#39;$tblPrefix%&#39;"; 
        } 
        $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 "<b>mysql error:<b><br>"; 
            echo $this->errno . ":" . $this->error . "<br>"; 
            exit (); 
        } else { 
            echo "<b>mysql error:<b><br>"; 
            echo $err_msg . "<br>"; 
            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 .= "&#39;" . $value . "&#39;,"; 
        } 
        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 . "= &#39;" . $value . "&#39;,"; 
        } 
        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 = &#39;select&#39;) { 
        $clean = &#39;&#39;; 
        $old_pos = 0; 
        $pos = - 1; 
         
        //如果是普通查询语句，直接过滤一些特殊语法 
        if ($querytype == &#39;select&#39;) { 
            $notallow1 = "[^0-9a-z@\._-]{1,}(union|sleep|benchmark|load_file|outfile)[^0-9a-z@\.-]{1,}"; 
             
            //$notallow2 = "--|/\*"; 
            if (eregi ( $notallow1, $db_string )) { 
                exit ( "<font size=&#39;5&#39; color=&#39;red&#39;>Safe Alert: Request Error step 1 !</font>" ); 
            } 
        } 
         
        //完整的SQL检查 
        while ( true ) { 
            $pos = strpos ( $db_string, &#39;\&#39;&#39;, $pos + 1 ); 
            if ($pos === false) { 
                break; 
            } 
            $clean .= substr ( $db_string, $old_pos, $pos - $old_pos ); 
            while ( true ) { 
                $pos1 = strpos ( $db_string, &#39;\&#39;&#39;, $pos + 1 ); 
                $pos2 = strpos ( $db_string, &#39;\\&#39;, $pos + 1 ); 
                if ($pos1 === false) { 
                    break; 
                } elseif ($pos2 == false || $pos2 > $pos1) { 
                    $pos = $pos1; 
                    break; 
                } 
                $pos = $pos2 + 1; 
            } 
            $clean .= &#39;$s$&#39;; 
            $old_pos = $pos + 1; 
        } 
        $clean .= substr ( $db_string, $old_pos ); 
        $clean = trim ( strtolower ( preg_replace ( array (&#39;~\s+~s&#39; ), array (&#39; &#39; ), $clean ) ) ); 
         
        //老版本的Mysql并不支持union，常用的程序里也不使用union，但是一些黑客使用它，所以检查它 
        if (strpos ( $clean, &#39;union&#39; ) !== false && preg_match ( &#39;~(^|[^a-z])union($|[^[a-z])~s&#39;, $clean ) != 0) { 
            $fail = true; 
        } 

        //发布版本的程序可能比较少包括--,#这样的注释，但是黑客经常使用它们 
        elseif (strpos ( $clean, &#39;/*&#39; ) > 2 || strpos ( $clean, &#39;--&#39; ) !== false || strpos ( $clean, &#39;#&#39; ) !== false) { 
            $fail = true; 
        } 

        //这些函数不会被使用，但是黑客会用它来操作文件，down掉数据库 
        elseif (strpos ( $clean, &#39;sleep&#39; ) !== false && preg_match ( &#39;~(^|[^a-z])sleep($|[^[a-z])~s&#39;, $clean ) != 0) { 
            $fail = true; 
        } elseif (strpos ( $clean, &#39;benchmark&#39; ) !== false && preg_match ( &#39;~(^|[^a-z])benchmark($|[^[a-z])~s&#39;, $clean ) != 0) { 
            $fail = true; 
        } elseif (strpos ( $clean, &#39;load_file&#39; ) !== false && preg_match ( &#39;~(^|[^a-z])load_file($|[^[a-z])~s&#39;, $clean ) != 0) { 
            $fail = true; 
        } elseif (strpos ( $clean, &#39;into outfile&#39; ) !== false && preg_match ( &#39;~(^|[^a-z])into\s+outfile($|[^[a-z])~s&#39;, $clean ) != 0) { 
            $fail = true; 
        } 

        //老版本的MYSQL不支持子查询，我们的程序里可能也用得少，但是黑客可以使用它来查询数据库敏感信息 
        elseif (preg_match ( &#39;~\([^)]*?select~s&#39;, $clean ) != 0) { 
            $fail = true; 
        } 
        if (! empty ( $fail )) { 
            exit ( "<font size=&#39;5&#39; color=&#39;red&#39;>Safe Alert: Request Error step 2!</font>" ); 
        } else { 
            return $db_string; 
        } 
    } 
    //====================================== 
    //函数：析构函数 
    //功能：释放类，关闭非永久的数据库连接 
    //参数：无 
    //====================================== 
    public function __destruct() { 
        $this->close (); 
    } 
} 
?>