一款简单实用的php操作mysql数据库类

2015-01-24信息快讯网

这篇文章主要介绍了一款简单实用的php操作mysql数据库类,不但包含了php针对mysql数据库的常见操作之外,还有针对危险字符的过滤功能,非常具有实用价值,需要的朋友可以参考下

本文实例讲述了一款简单实用的php操作mysql数据库类。分享给大家供大家参考。具体如下：

/* 
本款数据库连接类，他会自动加载sql防注入功能，过滤一些敏感的sql查询关键词，同时还可以增加判断字段 show table status的性质与show table类 获取数据库所有表名等。*/ 
@ini_set('mysql.trace_mode','off'); 
class mysql 
{
 public $dblink; 
 public $pconnect; 
 private $search = array('/union(s*(/*.**/)?s*)+select/i', '/load_file(s*(/*.**/)?s*)+(/i', '/into(s*(/*.**/)?s*)+outfile/i'); 
 private $replace = array('union   select', 'load_file   (', 'into   outfile'); 
 private $rs; 
 
 function __construct($hostname,$username,$userpwd,$database,$pconnect=false,$charset='utf8') 
 { 
  define('allowed_htmltags', '<html><embed><title><meta><body><a><p><br><hr><h1><h2><h3><h4><h5><h6><font><u><i><b><strong><div><span><ol><ul><li><img><table><tr><td><map>');  
  $this->pconnect=$pconnect; 
  $this->dblink=$pconnect?mysql_pconnect($hostname,$username,$userpwd):mysql_connect($hostname,$username,$userpwd); 
  (!$this->dblink||!is_resource($this->dblink)) && fatal_error("connect to the database unsuccessfully!"); 
  @mysql_unbuffered_query("set names {$charset}"); 
  if($this->version()>'5.0.1') 
  { 
   @mysql_unbuffered_query("set sql_mode = ''"); 
  } 
  @mysql_select_db($database) or fatal_error("can not select table!"); 
  return $this->dblink; 
 } 
 
 function query($sql,$unbuffered=false) 
 { 
  //echo $sql.'<br>'; 
  $this->rs=$unbuffered?mysql_unbuffered_query($sql,$this->dblink):mysql_query($sql,$this->dblink); 
  //(!$this->rs||!is_resource($this->rs)) && fatal_error("execute the query unsuccessfully! error:".mysql_error()); 
  if(!$this->rs)fatal_error('在执行sql语句 '.$sql.' 时发生以下错误:'.mysql_error()); 
  return $this->rs; 
 } 
 
 function fetch_one($sql) 
 { 
  $this->rs=$this->query($sql); 
  return dircms_strips教程lashes($this->filter_pass(mysql_fetch_array($this->rs,mysql_assoc))); 
 } 
 
 function get_maxfield($filed='id',$table) // 获取$table表中$filed字段的最大值 
 { 
  $r=$this->fetch_one("select {$table}.{$filed} from `{$table}` order by `{$table}`.`{$filed}` desc limit 0,1"); 
  return $r[$filed]; 
 } 
 
 function fetch_all($sql) 
 { 
  $this->rs=$this->query($sql); 
  $result=array(); 
  while($rows=mysql_fetch_array($this->rs,mysql_assoc)) 
  { 
   $result[]=$rows; 
  } 
   
  mysql_free_result($this->rs); 
  return dircms_stripslashes($this->filter_pass($result));  
 } 
 
 function fetch_all_withkey($sql,$key='id') 
 { 
  $this->rs=$this->query($sql); 
  $result=array(); 
  while($rows=mysql_fetch_array($this->rs,mysql_assoc)) 
  { 
   $result[$rows[$key]]=$rows; 
  } 
   
  mysql_free_result($this->rs); 
  return dircms_stripslashes($this->filter_pass($result));  
 } 
 
 function last_insert_id() 
 { 
  if(($insertid=mysql_insert_id($this->dblink))>0)return $insertid; 
  else //如果 auto_increment 的列的类型是 bigint，则 mysql_insert_id() 返回的值将不正确. 
  { 
   $result=$this->fetch_one('select last_insert_id() as insertid'); 
   return $result['insertid']; 
  } 
 } 
 
 function insert($tbname,$varray,$replace=false) 
 { 
  $varray=$this->escape($varray); 
  $tb_fields=$this->get_fields($tbname); // 升级一下，增加判断字段是否存在 
   
  foreach($varray as $key => $value) 
  { 
   if(in_array($key,$tb_fields)) 
   { 
    $fileds[]='`'.$key.'`'; 
    $values[]=is_string($value)?'''.$value.''':$value; 
   } 
  } 
 
  if($fileds) 
  { 
   $fileds=implode(',',$fileds); 
   $fileds=str_replace(''','`',$fileds); 
   $values=implode(',',$values); 
   $sql=$replace?"replace into {$tbname}({$fileds}) values ({$values})":"insert into {$tbname}({$fileds}) values ({$values})"; 
   $this->query($sql,true); 
   return $this->last_insert_id(); 
  } 
  else return false; 
 } 
 
 function update($tbname, $array, $where = '') 
 { 
  $array=$this->escape($array); 
  if($where) 
  { 
   $tb_fields=$this->get_fields($tbname); // 增加判断字段是否存在 
    
   $sql = ''; 
   foreach($array as $k=>$v) 
   { 
    if(in_array($k,$tb_fields)) 
    { 
     $k=str_replace(''','',$k); 
     $sql .= ", `$k`='$v'"; 
    } 
   } 
   $sql = substr($sql, 1); 
    
   if($sql)$sql = "update `$tbname` set $sql where $where"; 
   else return true; 
  } 
  else 
  { 
   $sql = "replace into `$tbname`(`".implode('`,`', array_keys($array))."`) values('".implode("','", $array)."')"; 
  } 
  return $this->query($sql,true); 
 } 
  
 function mysql_delete($tbname,$idarray,$filedname='id') 
 { 
  $idwhere=is_array($idarray)?implode(',',$idarray):intval($idarray); 
  $where=is_array($idarray)?"{$tbname}.{$filedname} in ({$idwhere})":" {$tbname}.{$filedname}={$idwhere}"; 
 
  return $this->query("delete from {$tbname} where {$where}",true); 
 } 
 
 function get_fields($table) 
 { 
  $fields=array(); 
  $result=$this->fetch_all("show columns from `{$table}`"); 
  foreach($result as $val) 
  { 
   $fields[]=$val['field']; 
  } 
  return $fields; 
 } 
 
 function get_table_status($database) 
 { 
  $status=array(); 
  $r=$this->fetch_all("show table status from `".$database."`"); /////// show table status的性质与show table类似，不过，可以提供每个表的大量信息。 
  foreach($r as $v) 
  { 
   $status[]=$v; 
  } 
  return $status; 
 } 
 
 function get_one_table_status($table) 
 { 
  return $this->fetch_one("show table status like '$table'"); 
 } 
 
 function create_fields($tbname,$fieldname,$size=0,$type='varchar') // 2010-5-14 修正一下 
 {   
  if($size) 
  { 
   $size=strtoupper($type)=='varchar'?$size:8; 
   $this->query("alter table `{$tbname}` add `$fieldname` {$type}( {$size} )  not null",true); 
  } 
  else $this->query("alter table `{$tbname}` add `$fieldname` mediumtext  not null",true); 
  return true; 
 } 
 
 function get_tables() //获取所有表表名 
 { 
  $tables=array(); 
  $r=$this->fetch_all("show tables"); 
  foreach($r as $v) 
  { 
   foreach($v as $v_) 
   { 
    $tables[]=$v_; 
   } 
  } 
  return $tables; 
 } 
 
 function create_model_table($tbname) //创建一个内容模型表（start：初始只有字段contentid int(20)，用于内容表，/////////////////////// update：2010-5-20     默认加入`content` mediumtext not null,字段） 
 { 
  if(in_array($tbname,$this->get_tables())) return false;  ///////////////////// 当表名已经存在时，返回 false 
  if($this->query("create table `{$tbname}` ( 
`contentid` mediumint(8) not null , 
`content` mediumtext not null, 
key ( `contentid` )  
) engine = myisam default charset=utf8",true))return true;   ////////////////////  成功则返回 true 
  return false; //////////////失败返回 false 
 } 
 
 function create_table($tbname) //创建一个会员模型空表（初始只有字段userid int(20)，用于会员表，2010-4-26） 
 { 
  if(in_array($tbname,$this->get_tables())) return false; 
  if($this->query("create table `{$tbname}` ( 
`userid` mediumint(8) not null , 
key ( `userid` )  
) engine = myisam default charset=utf8",true))return true; 
  return false; 
 } 
 
 function escape($str) // 过滤危险字符 
 { 
  if(!is_array($str)) return str_replace(array('n', 'r'), array(chr(10), chr(13)),mysql_real_escape_string(preg_replace($this->search,$this->replace, $str), $this->dblink)); 
  foreach($str as $key=>$val) $str[$key] = $this->escape($val); 
  return $str; 
 } 
 
 function filter_pass($string, $allowedtags = '', $disabledattributes = array('onabort', 'onactivate', 'onafterprint', 'onafterupdate', 'onbeforeactivate', 'onbeforecopy', 'onbeforecut', 'onbeforedeactivate', 'onbeforeeditfocus', 'onbeforepaste', 'onbeforeprint', 'onbeforeunload', 'onbeforeupdate', 'onblur', 'onbounce', 'oncellchange', 'onchange', 'onclick', 'oncontextmenu', 'oncontrolselect', 'oncopy', 'oncut', 'ondataavaible', 'ondatasetchanged', 'ondatasetcomplete', 'ondblclick', 'ondeactivate', 'ondrag', 'ondragdrop', 'ondragend', 'ondragenter', 'ondragleave', 'ondragover', 'ondragstart', 'ondrop', 'onerror', 'onerrorupdate', 'onfilterupdate', 'onfinish', 'onfocus', 'onfocusin', 'onfocusout', 'onhelp', 'onkeydown', 'onkeypress', 'onkeyup', 'onlayoutcomplete', 'onload', 'onlosecapture', 'onmousedown', 'onmouseenter', 'onmouseleave', 'onmousemove', 'onmoveout', 'onmouseo教程ver', 'onmouseup', 'onmousewheel', 'onmove', 'onmoveend', 'onmovestart', 'onpaste', 'onpropertychange', 'onreadystatechange', 'onreset', 'onresize', 'onresizeend', 'onresizestart', 'onrowexit', 'onrowsdelete', 'onrowsinserted', 'onscroll', 'onselect', 'onselectionchange', 'onselectstart', 'onstart', 'onstop', 'onsubmit', 'onunload')) 
 { 
  if(is_array($string)) 
  { 
   foreach($string as $key => $val) $string[$key] = $this->filter_pass($val, allowed_htmltags); 
  } 
  else 
  { 
   $string = preg_replace('/s('.implode('|', $disabledattributes).').*?([s>])/', '', preg_replace('/<(.*?)>/ie', "'<'.preg_replace(array('/网页特效:[^"']*/i', '/(".implode('|', $disabledattributes).")[ ]*=[ ]*["'][^"']*["']/i', '/s+/'), array('', '', ' '), stripslashes('')) . '>'", strip_tags($string, $allowedtags))); 
  } 
  return $string; 
 } 
 
 function drop_table($tbname) 
 { 
  return $this->query("drop table if exists `{$tbname}`",true); 
 } 
 
 function version() 
 { 
  return mysql_get_server_info($this->dblink); 
 } 
}

希望本文所述对大家的PHP程序设计有所帮助。

php+mysql实现无限分类实例详解

php在linux下检测mysql同步状态的方法

php+mysql删除指定编号员工信息的方法

php导入excel文件到mysql数据库的方法

php+mysql查询优化简单实例

thinkphp中ajax与php响应过程详解

php表单敏感字符过滤类

Yii实现MySQL多数据库和读写分离实例分析

php的mssql数据库连接类实例

PHP中mysql_field_type()函数用法

thinkphp区间查询、统计查询与SQL直接查询实例分析

php中addslashes函数与sql防注入

php中使用session_set_save_handler()函数把session保存到MySQL数据库实例

PHP操作MySQL事务实例

PHP通过插入mysql数据来实现多机互锁实例