簡單的php連線mysql類

xwenbin發表於2024-08-19
PHPMySql
<?php

class DB{
private $hostname; //資料庫主機
private $dbname; //資料庫
private $username; //資料庫使用者名稱
private $password; //資料庫密碼
private $port; //資料庫埠
public $db; //連線後的資料庫物件
//建構函式
public function __construct()
{
$this->hostname = 'localhost';
$this->username = 'root';
$this->password = '';
$this->port = '';
$this->dbname = 'user';
$this->connect();
POST_SAFE_CHECK();
}
//連線資料庫
public function connect(){
$hostname = $this->hostname;
if($this->port){
$hostname = $this->hostname . ':' . $this->port;
}

$this->db = mysqli_connect($hostname, $this->username, $this->password, $this->dbname);
if(!$this->db){
die('連線資料庫失敗！：' . mysqli_connect_error());
}

mysqli_set_charset('utf8');
}
//更改資料庫
public function dbChange($db){
$this->db = $db;
$this->connect();
}
//關閉連線
public function dbClose(){
mysqli_close($this->db);
}
//執行查詢
public function execute_query($db='', $sql=''){
if(!$db){
return('無效的資料庫！');
}
if(!$sql){
return('無效的查詢語句！');
}

$this->dbChange($db);

$res = mysqli_query($this->db, $sql);
$row = mysqli_fetch_assoc($res);
$data = mysqli_fetch_all($res);

$jsonData = array();

for($a=0; $a<count($data); $a++){
if(!$data[$a]){ continue; }
$col = array();
$i = 0;
foreach($row as $k => $v){
$col[$k] = $data[$a][$i];
$i++;
}
array_push($jsonData, $col);
}

$this->dbClose();

return json_encode($jsonData);
}
//執行新增、修改、刪除
public function execute_modify($db='', $sql=''){
if(!$db){
return('無效的資料庫！');
}
if(!$sql){
return('無效的查詢語句！');
}

$this->dbChange($db);

$res = mysqli_query($this->db, $sql);

if($res){
return 'success';
}
else{
return 'error';
}
}
}
//驗證post傳參合法性
function POST_SAFE_CHECK(){
foreach($_POST as $key => $value){
$_POST[$key] = remove_post($_POST[$key], $_SERVER['PHP_SELF']);
}
}

function remove_post($val, $url){
$val = remove_xss($val);
inject_check($val, $url);
return $val;
}

function remove_xss($val){
$val = preg_replace('/([\x00-\x08,\x0b-\x0c,\x0e-\x19])/', '', $val);
$search = 'abcdefghijklmnopqrstuvwxyz';
$search .= 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
$search .= '1234567890!@#$%^&*()';
$search .= '~`";:?+/={}[]-_|\'\\';
for($i=0; $i<strlen($search); $i++){
$val = preg_replace('/(&#[xX]0{0,8}'.dechex(ord($search[$i])).':?)/i', $search[$i], $val);
$val = preg_replace('/(&#0{0,8}'.ord($search[$i]).';?)/', $search[$i], $val);
}

$ra1 = array('javascript','vbscript','expression','applet','meta','xml','blink','link','style','script','embed','object','iframe','frame','frameset','ilayer','layer','bgsound','title','base');
$ra2 = array('onabort','onactivate','onafterprint','onafterupdate','onbeforeactivate','onbeforecopy','onbeforecut','onbeforedeactivate','onbeforeeditfocus','onbeforepaste','onbeforeprint','onbeforeunload','onbeforeupdate','onblur','onbounce','oncellchange','onchange','onclick','oncontextmenu','oncontrolselect','oncopy','oncut','ondataavailable','ondatasetchanged','ondatasetcomplete','ondblclick','ondeactivate','ondrag','ondragend','ondragenter','ondragleave','ondragover','ondragstart','ondrop','onerror','onerrorupdate','onfilterchange','onfinish','onfocus','onfocusin','onfocusout','onhelp','onkeydown','onkeypress','onkeyup','onlayoutcomplete','onload','onlosecapture','onmousedown','onmouseenter','onmouseleave','onmousemove','onmouseout','onmouseover','onmouseup','onmousewheel','onmove','onmoveend','onmovestart','onpaste','onpropertychange','onreadystatechange','onreset','onresize','onresizeend','onresizestart','onrowenter','onrowexit','onrowsdelete','onrowsinserted','onscroll','onselect','onselectionchange','onselectstart','onstart','onstop','onsubmit','onunload');
$ra = array_merge($ra1 , $ra2);

$found = true;

while($found == true){
$val_before = $val;
for($i=0; $i<count($ra); $i++){
$pattern = '/';
for($j=0; $j<strlen($ra[$i]); $j++){
if($j>0){
$pattern .= '(';
$pattern .= '(&#[xX]0{0,8}(9ab);)';
$pattern .= '|';
$pattern .= '|(&#0{0,8}([9|10|13]);)';
$pattern .= ')*';
}
$pattern .= $ra[$i][$j];
}
$pattern .= '/i';
$replacement = substr($ra[$i], 0, 2) . '<x>' . substr($a[$i], 2);
$val = preg_replace($pattern, $replacement, $val);

if($val_before == $val){
$found = false;
}
}
}

return $val;
}
//檢測sql注入
function inject_check($sql, $url){
preg_match('/select|insert|update|delete|\'|"|union|into|load_file|outfile|0x|%20|%25| or | |<|>|\)|\(|\*|\||&|;|\$|%|\@|\+|CR|LF|,|script|document|eval|window|=/', $sql, $check);

if($check){
echo "<script>alert('存在非法字元！');window.location='".$url."'</script>";
exit(0);
}
else{
return true;
}
}

?>
使用
<?php

include('DB.php');

$sql = 'select * from users;';
$dbname = 'user';

$DB = new DB();

$res = $DB->execute_query($dbname, $sql);

var_dump($res);

?>

相關文章