PDO（PHP Data Object），Mysqli，以及對sql注入等問題的解決

標籤：函數   state   版本   自動   mode   string   color   ble   對象   

這篇是上一篇 http://www.cnblogs.com/charlesblc/p/5987951.html 的續集。

看有的文章提到mysqli和PDO都支援多重查詢，所以下面的url會造成表資料被刪。

http://localhost:8080/test.php?id=3;delete%20from%20users

可是我在mysql版本的函數，上面的sql都不能執行。是不是不支援多重查詢了？

 

這篇文章 http://www.runoob.com/php/php-mysql-connect.html 對mysqli, PDO的方式有一些介紹，不詳細。

主要用的這篇文章：http://blog.csdn.net/yipiankongbai/article/details/17277477

三種串連方式：

// PDO  $pdo = new PDO("mysql:host=localhost;dbname=database", ‘username‘, ‘password‘);     // mysqli, procedural way  $mysqli = mysqli_connect(‘localhost‘,‘username‘,‘password‘,‘database‘);     // mysqli, object oriented way  $mysqli = new mysqli(‘localhost‘,‘username‘,‘password‘,‘database‘);  

 

先看mysqli過程型：

<?phpheader(‘Content-Type: text/html; charset=utf-8‘);echo "PHP version:" . PHP_VERSION . "<br/>";$con = mysqli_connect(‘10.117.146.21:8306‘, ‘root‘, ‘[password]‘);mysqli_select_db($con, ‘springdemo‘);$input_id = trim($_GET[‘id‘]);$sql = ‘select nickname from user where id = ‘ . $input_id;print_r(‘SQL is:‘ . $sql . ‘<br/>‘);$result = mysqli_query($con, $sql);if ($result != null) {  print_r(‘rows:‘ . mysqli_num_rows($result) . ‘<br/>‘);  while ($row = mysqli_fetch_array($result)) {    print_r($row[‘nickname‘] . ‘<br/>‘);  }}mysqli_close($con);?>

測試：

http://localhost:8080/test.php?id=3PHP version:5.5.30SQL is:select nickname from user where id = 3rows:1microhttp://localhost:8080/test.php?id=3%20or%201=1PHP version:5.5.30SQL is:select nickname from user where id = 3 or 1=1rows:4abcmicrohelloworld你好

加上real_escape函數：

?phpheader(‘Content-Type: text/html; charset=utf-8‘);echo "PHP version:" . PHP_VERSION . "<br/>";$con = mysqli_connect(‘10.117.146.21:8306‘, ‘root‘, ‘[password]‘);mysqli_select_db($con, ‘springdemo‘);$input_id = mysqli_real_escape_string($con, $_GET[‘id‘]);$sql = ‘select nickname from user where id = ‘ . $input_id;print_r(‘SQL is:‘ . $sql . ‘<br/>‘);$result = mysqli_query($con, $sql);if ($result != null) {  print_r(‘rows:‘ . mysqli_num_rows($result) . ‘<br/>‘);  while ($row = mysqli_fetch_array($result)) {    print_r($row[‘nickname‘] . ‘<br/>‘);  }}mysqli_close($con);?>

測試：

http://localhost:8080/test.php?id=3%20or%201=1PHP version:5.5.30SQL is:select nickname from user where id = 3 or 1=1rows:4abcmicrohelloworld你好註：仍然有問題，因為沒有在url裡面加引號！

採用推薦的mysqli的PreparedStatement方式：

?phpheader(‘Content-Type: text/html; charset=utf-8‘);echo "PHP version:" . PHP_VERSION . "<br/>";$con = new mysqli(‘10.117.146.21:8306‘, ‘root‘, ‘[password]‘, ‘springdemo‘);//mysqli_select_db($con, ‘springdemo‘);$query = $con->prepare(‘SELECT nickname FROM user WHERE id = ?‘);$query->bind_param(‘s‘, $_GET[‘id‘]);$query->execute();$result = $query->get_result();if ($result != null) {  print_r(‘rows:‘ . mysqli_num_rows($result) . ‘<br/>‘);  while ($row = mysqli_fetch_array($result)) {    print_r($row[‘nickname‘] . ‘<br/>‘);  }}mysqli_close($con);?>

測試：

http://localhost:8080/test.php?id=3PHP version:5.5.30rows:1microhttp://localhost:8080/test.php?id=3%20or%201=1PHP version:5.5.30rows:1micro

 

PDO與mysqli的對比：

效能PDO和MySQLi都有非常好的效能。在非prepared statements的基準測試下，MySQLi略快2.5%，而prepared statements下是6.5%，可以說對於效能無關緊要。

 

	PDO	MySQLi
Database support	12 different drivers	MySQL only
API	OOP	OOP + procedural
Connection	Easy	Easy
Named parameters	Yes	No
Object mapping	Yes	Yes
Prepared statements  (client side)	Yes	No
Performance	Fast	Fast
Stored procedures	Yes	Yes

 PDO方式：

<?phpheader(‘Content-Type: text/html; charset=utf-8‘);echo "PHP version:" . PHP_VERSION . "<br/>";$pdo = new PDO("mysql:host=10.117.146.21:8306;dbname=springdemo", ‘root‘, ‘[password]‘);class Name {   public $nickname;   public function info()   {      return ‘#‘.$this->nickname;   }}$input_id = $_GET[‘id‘];$sql = ‘select nickname from user where id = ‘ . $input_id;print_r(‘SQL is:‘ . $sql . ‘<br/>‘);$result = $pdo->query($sql);$result->setFetchMode(PDO::FETCH_CLASS, ‘Name‘);if ($result != null) {  while ($row = $result->fetch()) {    print_r($row->info() . ‘<br/>‘);  }}$pdo=null;?>

測試：

http://localhost:8080/test.php?id=3PHP version:5.5.30SQL is:select nickname from user where id = 3#microhttp://localhost:8080/test.php?id=3%20or%201=1PHP version:5.5.30SQL is:select nickname from user where id = 3 or 1=1#abc#micro#helloworld#你好

加上轉碼：

$input_id = $pdo->quote($_GET[‘id‘]);

測試：

http://localhost:8080/test.php?id=3PHP version:5.5.30SQL is:select nickname from user where id = ‘3‘#microhttp://localhost:8080/test.php?id=3%20or%201=1PHP version:5.5.30SQL is:select nickname from user where id = ‘3 or 1=1‘#micro

注意，pdo的quote自動加了引號，解決了這個問題。

http://localhost:8080/test.php?id=3%27%20or%201=%271PHP version:5.5.30SQL is:select nickname from user where id = ‘3\‘ or 1=\‘1‘#micro

並且，嘗試自己加引號去做侵入也沒有用的。引號被轉碼了，所以成功防住攻擊。

 

PDO的prepared statement：

<?phpheader(‘Content-Type: text/html; charset=utf-8‘);echo "PHP version:" . PHP_VERSION . "<br/>";$pdo = new PDO("mysql:host=10.117.146.21:8306;dbname=springdemo", ‘root‘, ‘[password]‘);$prepared = $pdo->prepare(‘select nickname from user where id = :id‘);$prepared->execute(array(‘:id‘ => $_GET[‘id‘]));while ($results = $prepared->fetch(PDO::FETCH_ASSOC)) {    print_r($results[‘nickname‘] . ‘<br/>‘);}$pdo=null;?>

實驗：

http://localhost:8080/test.php?id=3PHP version:5.5.30microhttp://localhost:8080/test.php?id=3%20or%201=1PHP version:5.5.30micro

都不再出現sql注入的威脅。

PDO裡面多次用到fetch：

值	說明
PDO::FETCH_ASSOC	關聯陣列形式。
PDO::FETCH_NUM	數字索引數組形式。
PDO::FETCH_BOTH	兩者數組形式都有，這是預設的。
PDO::FETCH_OBJ	按照對象的形式，類似於以前的mysql_fetch_object()函數。
PDO::FETCH_BOUND	以布爾值的形式返回結果，同時將擷取的列值賦給bindParam()方法中指定的變數。
PDO::FETCH_LAZY	以關聯陣列、數字索引數組和對象3種形式返回結果。

把上面程式給prepared statement傳參數的過程改了一下：

<?phpheader(‘Content-Type: text/html; charset=utf-8‘);echo "PHP version:" . PHP_VERSION . "<br/>";$pdo = new PDO("mysql:host=10.117.146.21:8306;dbname=springdemo", ‘root‘, ‘[password]‘);$prepared = $pdo->prepare(‘select nickname from user where id = :id‘);$prepared->bindParam(‘:id‘, $_GET[‘id‘]);$prepared->execute();while ($results = $prepared->fetch(PDO::FETCH_ASSOC)) {    print_r($results[‘nickname‘] . ‘<br/>‘);}$pdo=null;?>

實驗之後，結果對於上面哪些url，都能得到正確的結果。

效能方面：

PDO和MySQLi都有非常好的效能。在非prepared statements的基準測試下，MySQLi略快2.5%，而prepared statements下是6.5%，可以說對於效能無關緊要。
如果你真的非常介意這一點點效能的話，而內建的MySQL擴充比兩者都快，你可以考慮下它。

 

上面部分來自這篇：http://blog.csdn.net/yipiankongbai/article/details/17277477 《PDO vs. MySQLi 選擇哪一個？》

PDO（PHP Data Object），Mysqli，以及對sql注入等問題的解決