mysql總結與預先處理

標籤：

 

一、串連到MySQL: 

$dbc = mysqli_connect(host,user,password,databasename); 
等價於：
$dbc = mysqli_connect(host,user,pwd);
mysqli_select_db($dbc,db_name); 

如果發生錯誤，可以調用：mysqli_connect_error() 返回錯誤資訊，不帶參數。
$dbc = @mysqli_connect(host,user,pwd,db) or die(‘無法串連到mysql：‘.mysqli_connect_error()); 

@是錯誤控制運算子，防止在web瀏覽器顯示php錯誤。此外，@也可以放在mysqli_query前面。上面是一種首選做法，因為錯誤將由or die處理。die()會終止指令碼執行。

可以將串連檔案放在路徑外。

設定編碼：mysqli_query("set names gb2312"); 

二、執行查詢： 
不管是select, delete, update,insert 查詢都是用：
$result=mysqli_query($dbc,SQL); 
對於insert ,delete,update等查詢不會返回結果，$result將返回true或false，因此可以用這個來判斷下一步：
$result = mysqli_query($dbc,sql);
if($result) {//susses} 

如果查詢沒有成功，必定發生某種mysql錯誤，可能調用
mysqli_error($dbc)    //注意和mysqli_connect_error()區別

三、關閉串連： 
mysqli_close($dbc) 這不是必需要的，php會在指令碼最後自動關閉，但最好寫上。

四、多條查詢： 
mysqli_multi_query() 允許同時執行多條查詢。但文法更複雜一點。特別是當返回結果時。

五、檢索select查詢結果： 
mysqli_fetch_array($result [, type]) 是最常用的，以數組格式一次返回一行資料。由它來配合while（） 來遍曆返回資料。帶有一個選擇性參數type，用於指定返回的數群組類型：關聯的還是索引的，或二者均可。參數類型如下：
MYSQLI_ASSOC    樣本：$rows[‘columnName‘]
MYSQLI_NUM 樣本：$rows[0] ，這一種效率較高一點。
MYSQLI_BOTH 樣本：$rows[0]或$rows[‘columnName‘]

當使用mysqli_fetch_array($result [, type]) 後，可以採取一個可選步聚的是：一旦查詢結果完成了工作，即可釋放這些資訊，來消除$result佔用的系統記憶體開銷。這一步是可選的，PHP同樣也在會結果時自動清理：
mysqli_free_result($result)    //注意參數是不是$rows!
流程如下：
while($rows=mysqli_fetch_array($result))   //或while($rows=mysqli_fetch_array($result,MYSQLI_ASSOC))
{//遍曆
   .......code do something.......
   echo $rows[0]
   mysqli_free_result($result)
} 
注意： 
mysqli_fetch_array()和mysqli_fetch_array($result,MYSQLI_NUM)等價。
mysqli_fetch_assoc()和mysqli_fetch_array($result,MYSQLI_ASSOC)等價。


六、確保sql安全，使用轉義函數： 
mysqli_real_escape_string($dbc,para) 
該函數接收字串作為參數，用於檢驗使用者提交的並將組合到sql查詢語句的變數值，它將轉義那些有可能無意或帶惡意的字元。如單引號，在外國人的姓名有可能會包含該符號（如O‘Toole），這時就需要用它。
案例：
$name = $_POST[‘name‘];
$name = mysqli_real_escape_string($dbc,$name);
$query = "Select ... From tb where name=‘$name‘"; //這樣可以確保帶入sql 時參數的安全。
注意：如果在使用php6之前的版本，若啟用MAGIC QUOTES魔法引用時，那麼在使用mysqli_real_escape_string前，需要用stripslashes(para)刪除魔法引用添加的任何斜杠，如下：
$fn = mysqli_real_escape_string($dbc,trim(stripslashes($_POST[‘firstName‘])));

 

備忘：

在PHP5.3版本之前， mysqli_real_escape_string()函數存在路徑泄漏問題，遠程攻擊者可以利用漏洞獲得伺服器端指令碼的實際路徑。即如果傳遞的參數值為數組而不是字串的情況下會發出警告，警告訊息中會包含有當前服務端運行指令碼的完整路徑資訊。
 測試方法：
http://localhost/cms/sqlfilter/sqlsanatizer.php?params []= 
Warning: mysqli_real_escape_string() expects parameter 1 to be string, 
array given in /var/www/vhosts/cms/sqlfilter/sqlsanatizer.php 

七、統計select返回的記錄數： 
使用mysqli_num_rows($result)統計select 返回的結果行數。$num=mysqli_num_rows($r)，對於上面所說的while流程，可以更改成以下更嚴謹的寫法，而不只是分析查詢是否成功，因為如果資料庫為空白的話，就不會出錯。
$sql = "select * from tb where id=$id";
$r = @mysqli_query($dbc,$sql);
$num = mysqli_num_rows($r);
if($num>0){ //這樣比if($r)更準確。不是僅僅分析是否成功運行。
     // Do something;
     
     mysqli_free_result($r)
}
mysqli_close($dbc); 

八、返回insert ，update，delete受影響行數： 
和上面不同的是，如果查詢不是select則用mysqli_affected_rows()函數返回受影響行數。用法如下：
$num = mysqli_affected_rows($dbc); //注意參數是$dbc;
如：
$q = "update tb set pass=SHA1(‘$newpassword‘) where id=$row[0]";
$r = @myslqi_query($dbc,$q);
if(mysqli_affected_rows($dbc)==1){
      //Do something
}else{
     echo mysqli_error($dbc);
     exit(); //終止指令碼。
} 
注意：
1、如果使用truncate tb清空表時，則mysqli_affected_rows()會返回0，即使查詢成功執行並且刪除了每一行。
2、如果用update查詢時，但實質上沒有更改任何列的值，比如用相同的密碼代替一箇舊密碼，則也會返回0。

九、批量查詢：預先處理語句（第12章第4節:P311） 
版本：MYSQL 4.1開始添加預先處理。php5可以使用。

預先處理的好處：
1、更大安全性。2、更好效能。3、批量查詢。

對於預先處理語句，只會把查詢本身發送給mysql，並且只會解析一次，然後單獨把值發送給mysql。
$q = ‘Insert into tb(num) values (?)‘;
$stmt = mysqli_prepare($dbc,$q);
mysqli_stmt_bind_param($stmt,‘i‘,$n);
for($n=1;$n<=100;$n++)
{
    mysqli_stmt_execute($stmt);
} 
可以通過insert , update , delete , select 查詢建立預先處理，步驟：
1、定義查詢：
$q = "select firstname,lastname from users where uid = ?";   //(正常則是uid=$id)
2、將查詢傳給mysql預先處理：
$stmt = mysqli_prepare($dbc,$q);   //此時mysql會解析查詢，但不會執行。
3、將變數綁定到查詢預留位置"？"，如下：
mysqli_stmt_bind_param($stmt,‘i‘,$id); 
其中‘i‘的含義是mysql_stmt_bind_param函數期望接收到的值為int類型，共有以下幾種：
--------------------------------------------------------------------------------
字母                    表示綁定的實值型別
d                             Decimal
i                               Integer
b                              Blob (二進位類型)
s                               所有其它類型
----------------------------------------------------------------------------------
如果查詢語句有多個變數，如：
$q = "select uid,firstname from users where email=? AND pass=SHA1(?)"; //注意這裡都沒有對?問號加單引號，即使是字元型。這是和標準查詢的區別。
多個變數直接在綁定時按順序在引號內列出即可。如下：
$stmt = mysqli_prepare($dbc,$q);
mysqli_stmt_bind_param($stmt,‘ss‘,$e,$p);
還需要注意的時，在調用綁定函數前，可以不需要先對變數定義設定，如上面的$e,$p在下面才設定，這不會出錯。
4、完成綁定後，可以給php變數賦值（如果還沒有值的話）。然後執行語句。
$id=15;
mysqli_stmt_execute($stmt);
5、關閉預先處理：
mysqli_stmt_close($stmt);
6、關閉串連
mysqli_close($dbc);
執行預先處理時，如有出錯則用mysqli_stmt_error($stmt)調用。

樣本：
$dbc =mysqli_connect(‘localhost‘,‘username‘,‘pwd‘,‘forum‘);
$q = ‘insert into messages(forumid,parentid,userid,subject,body,forumdate) values(?,?,?,?,?,NOW())‘;
$stmt = mysqli_prepare($dbc,$q);
mysqli_stmt_bind_param($stmt,‘iiiss‘,$forumid,$parentid,$userid,$subject,$body);
$forumid = (int)$_POST[‘forumid‘];
$parentid=(int) $_POST[‘parentid‘];
$user_id =3;
$subject = strip_tags($_POST[‘subject‘]); //strip_tags
$body = strip_tags($_POST[‘body‘]);
mysqli_stmt_execute($stmt);
if(mysqli_stmt_affected_rows($stmt)==1)
{
      //do ....
}else{
     echo mysqli_stmt_error($stmt);
}
mysqli_stmt_close($stmt);
mysqli_close($dbc); 

以上示範了預先處理的一種語句，實際上預先處理有兩種語句：
1、綁定參數（bound parameter）：如上面的樣本
2、綁定結果(bound result)：將查詢結果綁定到php變數。

十、阻止sql注入：（第12章第4節:P311） 
1、驗證在查詢中要使用的資料，如果有可能，就可執行類型強制轉換。如：
$forumid = (int)$_POST[‘forumid‘];
if($forumid>0) ....   //如果強制轉換成int完=0時，則不符資料型要求。
2、使用mysqli_real_escape_string($dbc,para)
3、使用mysqli_real_escape_string($dbc,para)替代辦法：預先處理，參上面。

十一、早先的php和mysql串連方式： 
mysql_connect,在寫法上只差上面一個字母i，但用法差不多。以下簡單樣本：
$conn = mysql_connect("127.0.0.1","mysqltest","123456");
mysql_select_db("shop"); //如果用$selectdb = mysql_select_db("shop");則$selectdb=1

mysql_query("set names gb2312"); //mysql_query("set names utf8"); 
$exec="select * from product";
$result=mysql_query($exec,$conn); //或：$result=mysql_query($exec);

while($rs=mysql_fetch_object($result)) 
{
echo "品名：[".$rs->pname . "] &nbsp;&nbsp;";
echo "價格：".
   $rs->price . "&nbsp;&nbsp;";
echo "入庫時間：".$rs->addTime . "&nbsp;&nbsp;";
echo "<br />";
}
echo $result; 

如果要進行結果判斷有無再輸出，則可以用：
$conn = mysql_connect("127.0.0.1","mysqltest","123456");
mysql_select_db("shop");

mysql_query("set names gb2312"); //mysql_query("set names utf8"); 
$exec="select * from product";
if($result=mysql_query($exec,$conn)){
while($rs=mysql_fetch_object($result))
{
   echo "品名：[".$rs->pname . "] &nbsp;&nbsp;";
   echo "價格：".
    $rs->price . "&nbsp;&nbsp;";
   echo "入庫時間：".$rs->addTime . "&nbsp;&nbsp;";
   echo "<br />";
}
} 

附：
A、在insert後取得最後一條記錄：2種方法： 
1、使用mysql的：last_insert_id() 函數。“insert into ....;select last_insert_id()”
2、使用php 的 mysql_insert_id() 或mysqli_insert_id() 返回同樣的值:
PHP的 mysql_insert_id ( [resource $link_identifier] ) 函數可以返回你需要的ID。 選擇性參數是php串連mysql的控制代碼。 每個串連都有不同的控制代碼。如：
    mysql_query("INSERT INTO mytable (product) values (‘kossu‘)");
    printf ("Last inserted record has id %d\n", mysql_insert_id()); 


B、幾個函數： 
trim() , ltrim(), rtrim()
exit(),
strip_tags()去掉字串中包含的任何 HTML 及 PHP 的標記字串。若是字串的 HTML 及 PHP 標籤原來就有錯，例如少了大於的符號，則也會返回錯誤。而本函數和 fgetss() 有著相同的功能。
$text = ‘<p>Test paragraph.</p><!-- Comment --> <a href="#fragment">Other text</a>‘;
echo strip_tags($text); //結果：Test paragraph. Other text
// 許可用 <p> and <a>
echo strip_tags($text, ‘<p><a>‘); //結果：<p>Test paragraph.</p> <a href="#fragment">Other text</a>

mysql總結與預先處理