【www.gdgbn.com--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", "<meta><body><p><br><hr><h1><h2><h3><h4><h5><h6><font><u><i><b><strong><ol><img><map>"); <br />   $this->pconnect=$pconnect;<br />   $this->dblink=$pconnect?mysql_pconnect($hostname,$username,$userpwd):mysql_connect($hostname,$username,$userpwd);<br />   (!$this->dblink||!is_resource($this->dblink)) && fatal_error("connect to the database unsuccessfully!");<br />   @mysql_unbuffered_query("set names {$charset}");<br />   if($this->version()>"5.0.1")<br />   {<br />    @mysql_unbuffered_query("set sql_mode = """);<br />   }<br />   @mysql_select_db($database) or fatal_error("can not select table!");<br />   return $this->dblink;<br />  }</p> <p> function query($sql,$unbuffered=false)<br />  {<br />   //echo $sql."<br>";<br />   $this->rs=$unbuffered?mysql_unbuffered_query($sql,$this->dblink):mysql_query($sql,$this->dblink);<br />   //(!$this->rs||!is_resource($this->rs)) && fatal_error("execute the query unsuccessfully! error:".mysql_error());<br />   if(!$this->rs)fatal_error("在执行sql语句 ".$sql." 时发生以下错误:".mysql_error());<br />   return $this->rs;<br />  }</p> <p> function fetch_one($sql)<br />  {<br />   $this->rs=$this->query($sql);<br />   return dircms_stri<a href="http://www.gdgbn.com/blist-15-1.html" target="_blank" class="keylink">ps教程</a>lashes($this->filter_pass(mysql_fetch_array($this->rs,mysql_assoc)));<br />  }</p> <p> function get_maxfield($filed="id",$table) // 获取$table表中$filed字段的最大值<br />  {<br />   $r=$this->fetch_one("select {$table}.{$filed} from `{$table}` order by `{$table}`.`{$filed}` desc limit 0,1");<br />   return $r[$filed];<br />  }</p> <p> function fetch_all($sql)<br />  {<br />   $this->rs=$this->query($sql);<br />   $result=array();<br />   while($rows=mysql_fetch_array($this->rs,mysql_assoc))<br />   {<br />    $result[]=$rows;<br />   }<br />   <br />   mysql_free_result($this->rs);<br />   return dircms_stripslashes($this->filter_pass($result)); <br />  }</p> <p> function fetch_all_withkey($sql,$key="id")<br />  {<br />   $this->rs=$this->query($sql);<br />   $result=array();<br />   while($rows=mysql_fetch_array($this->rs,mysql_assoc))<br />   {<br />    $result[$rows[$key]]=$rows;<br />   }<br />   <br />   mysql_free_result($this->rs);<br />   return dircms_stripslashes($this->filter_pass($result)); <br />  }</p> <p> function last_insert_id()<br />  {<br />   if(($insertid=mysql_insert_id($this->dblink))>0)return $insertid;<br />   else //如果 auto_increment 的列的类型是 bigint，则 mysql_insert_id() 返回的值将不正确.<br />   {<br />    $result=$this->fetch_one("select last_insert_id() as insertid");<br />    return $result["insertid"];<br />   }<br />  }</p> <p> function insert($tbname,$varray,$replace=false)<br />  {<br />   $varray=$this->escape($varray);<br />   $tb_fields=$this->get_fields($tbname); // mb.111cn.net 升级一下，增加判断字段是否存在<br />   <br />   foreach($varray as $key => $value)<br />   {<br />    if(in_array($key,$tb_fields))<br />    {<br />     $fileds[]="`".$key."`";<br />     $values[]=is_string($value)?""".$value.""":$value;<br />    }<br />   }</p> <p>  if($fileds)<br />   {<br />    $fileds=implode(",",$fileds);<br />    $fileds=str_replace(""","`",$fileds);<br />    $values=implode(",",$values);<br />    $sql=$replace?"replace into {$tbname}({$fileds}) values ({$values})":"insert into {$tbname}({$fileds}) values ({$values})";<br />    $this->query($sql,true);<br />    return $this->last_insert_id();<br />   }<br />   else return false;<br />  }</p> <p> function update($tbname, $array, $where = "")<br />  {<br />   $array=$this->escape($array);<br />   if($where)<br />   {<br />    $tb_fields=$this->get_fields($tbname); // www.111cn.net，增加判断字段是否存在<br />    <br />    $sql = "";<br />    foreach($array as $k=>$v)<br />    {<br />     if(in_array($k,$tb_fields))<br />     {<br />      $k=str_replace(""","",$k);<br />      $sql .= ", `$k`="$v"";<br />     }<br />    }<br />    $sql = substr($sql, 1);<br />    <br />    if($sql)$sql = "update `$tbname` set $sql where $where";<br />    else return true;<br />   }<br />   else<br />   {<br />    $sql = "replace into `$tbname`(`".implode("`,`", array_keys($array))."`) values("".implode("","", $array)."")";<br />   }<br />   return $this->query($sql,true);<br />  }<br />  <br />  function mysql_delete($tbname,$idarray,$filedname="id")<br />  {<br />   $idwhere=is_array($idarray)?implode(",",$idarray):intval($idarray);<br />   $where=is_array($idarray)?"{$tbname}.{$filedname} in ({$idwhere})":" {$tbname}.{$filedname}={$idwhere}";</p> <p>  return $this->query("delete from {$tbname} where {$where}",true);<br />  }</p> <p> function get_fields($table)<br />  {<br />   $fields=array();<br />   $result=$this->fetch_all("show columns from `{$table}`");<br />   foreach($result as $val)<br />   {<br />    $fields[]=$val["field"];<br />   }<br />   return $fields;<br />  }</p> <p> function get_table_status($database)<br />  {<br />   $status=array();<br />   $r=$this->fetch_all("show table status from `".$database."`"); /////// show table status的性质与show table类似，不过，可以提供每个表的大量信息。<br />   foreach($r as $v)<br />   {<br />    $status[]=$v;<br />   }<br />   return $status;<br />  }</p> <p> function get_one_table_status($table)<br />  {<br />   return $this->fetch_one("show table status like "$table"");<br />  }</p> <p> function create_fields($tbname,$fieldname,$size=0,$type="varchar") // 2010-5-14 修正一下<br />  {  <br />   if($size)<br />   {<br />    $size=strtoupper($type)=="varchar"?$size:8;<br />    $this->query("alter table `{$tbname}` add `$fieldname` {$type}( {$size} )  not null",true);<br />   }<br />   else $this->query("alter table `{$tbname}` add `$fieldname` mediumtext  not null",true);<br />   return true;<br />  }</p> <p> function get_tables() //获取所有表表名<br />  {<br />   $tables=array();<br />   $r=$this->fetch_all("show tables");<br />   foreach($r as $v)<br />   {<br />    foreach($v as $v_)<br />    {<br />     $tables[]=$v_;<br />    }<br />   }<br />   return $tables;<br />  }</p> <p> function create_model_table($tbname) //创建一个内容模型表（start：初始只有字段contentid int(20)，用于内容表，/////////////////////// update：2010-5-20     默认加入`content` mediumtext not null,字段）<br />  {<br />   if(in_array($tbname,$this->get_tables())) return false;  ///////////////////// 当表名已经存在时，返回 false<br />   if($this->query("create table `{$tbname}` (<br /> `contentid` mediumint(8) not null ,<br /> `content` mediumtext not null,<br /> key ( `contentid` ) <br /> ) engine = myisam default charset=utf8",true))return true;   ////////////////////  成功则返回 true<br />   return false; //////////////失败返回 false<br />  }</p> <p> function create_table($tbname) //创建一个会员模型空表（初始只有字段userid int(20)，用于会员表，2010-4-26）<br />  {<br />   if(in_array($tbname,$this->get_tables())) return false;<br />   if($this->query("create table `{$tbname}` (<br /> `userid` mediumint(8) not null ,<br /> key ( `userid` ) <br /> ) engine = myisam default charset=utf8",true))return true;<br />   return false;<br />  }</p> <p> function escape($str) // 过滤危险字符<br />  {<br />   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));<br />   foreach($str as $key=>$val) $str[$key] = $this->escape($val);<br />   return $str;<br />  }</p> <p> 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"))<br />  {<br />   if(is_array($string))<br />   {<br />    foreach($string as $key => $val) $string[$key] = $this->filter_pass($val, allowed_htmltags);<br />   }<br />   else<br />   {<br />    $string = preg_replace("/s(".implode("|", $disabledattributes).").*?([s>])/", "", preg_replace("/<(.*?)>/ie", ""<".preg_replace(array("/<a href="http://www.gdgbn.com/blist-18-1.html" target="_blank" class="keylink">网页特效</a>:[^""]*/i", "/(".implode("|", $disabledattributes).")[ ]*=[ ]*[""][^""]*[""]/i", "/s+/"), array("", "", " "), stripslashes("")) . ">"", strip_tags($string, $allowedtags)));<br />   }<br />   return $string;<br />  }</p> <p> function drop_table($tbname)<br />  {<br />   return $this->query("drop table if exists `{$tbname}`",true);<br />  }</p> <p> function version()<br />  {<br />   return mysql_get_server_info($this->dblink);<br />  }<br /> }</p> </map></ol></strong></b></i></u></font></body>

本文来源：http://www.gdgbn.com/shujuku/27815/