当前位置：Gxlcms > 数据库问题 > PDO（PHP Data Object），Mysqli，以及对sql注入等问题的解决

PDO（PHP Data Object），Mysqli，以及对sql注入等问题的解决

时间：2021-07-01 10:21:17 帮助过：13人阅读

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

三种连接方式：

  1. <span style="color: #008000">//</span><span style="color: #008000"> PDO  </span>
  2. $pdo = <span style="color: #0000ff">new</span> PDO("mysql:host=localhost;dbname=database", ‘username‘, ‘password‘<span style="color: #000000">);  
  3.    
  4. </span><span style="color: #008000">//</span><span style="color: #008000"> mysqli, procedural way  </span>
  5. $mysqli = mysqli_connect(‘localhost‘,‘username‘,‘password‘,‘database‘<span style="color: #000000">);  
  6.    
  7. </span><span style="color: #008000">//</span><span style="color: #008000"> mysqli, object oriented way  </span>
  8. $mysqli = <span style="color: #0000ff">new</span> mysqli(‘localhost‘,‘username‘,‘password‘,‘database‘);

 

先看mysqli过程型：

  1. <?<span style="color: #000000">php
  2. header(</span>‘Content-Type: text/html; charset=utf-8‘<span style="color: #000000">);
  3. echo </span>"PHP version:" . PHP_VERSION . "<br/>"<span style="color: #000000">;
  4. $con </span>= mysqli_connect(‘10.117.146.21:8306‘, ‘root‘, ‘[password]‘<span style="color: #000000">);
  5. mysqli_select_db($con, </span>‘springdemo‘<span style="color: #000000">);
  6. $input_id </span>= trim($_GET[‘id‘<span style="color: #000000">]);
  7. $sql </span>= ‘select nickname from user where id = ‘<span style="color: #000000"> . $input_id;
  8. print_r(</span>‘SQL is:‘ . $sql . ‘<br/>‘<span style="color: #000000">);
  9. $result </span>=<span style="color: #000000"> mysqli_query($con, $sql);
  10. </span><span style="color: #0000ff">if</span> ($result != <span style="color: #0000ff">null</span><span style="color: #000000">) {
  11.   print_r(</span>‘rows:‘ . mysqli_num_rows($result) . ‘<br/>‘<span style="color: #000000">);
  12.   </span><span style="color: #0000ff">while</span> ($row =<span style="color: #000000"> mysqli_fetch_array($result)) {
  13.     print_r($row[</span>‘nickname‘] . ‘<br/>‘<span style="color: #000000">);
  14.   }
  15. }
  16. mysqli_close($con);
  17. </span>?>

测试：

  1. http:<span style="color: #008000">//</span><span style="color: #008000">localhost:8080/test.php?id=3</span>
  2. <span style="color: #000000">
  3. PHP version:</span>5.5.30<span style="color: #000000">
  4. SQL is:select nickname from user where id </span>= 3<span style="color: #000000">
  5. rows:</span>1<span style="color: #000000">
  6. micro
  7. http:</span><span style="color: #008000">//</span><span style="color: #008000">localhost:8080/test.php?id=3%20or%201=1</span>
  8. <span style="color: #000000">
  9. PHP version:</span>5.5.30<span style="color: #000000">
  10. SQL is:select nickname from user where id </span>= 3 or 1=1<span style="color: #000000">
  11. rows:</span>4<span style="color: #000000">
  12. abc
  13. micro
  14. helloworld
  15. 你好</span>

加上real_escape函数：

  1. ?<span style="color: #000000">php
  2. header(</span>‘Content-Type: text/html; charset=utf-8‘<span style="color: #000000">);
  3. echo </span>"PHP version:" . PHP_VERSION . "<br/>"<span style="color: #000000">;
  4. $con </span>= mysqli_connect(‘10.117.146.21:8306‘, ‘root‘, ‘[password]‘<span style="color: #000000">);
  5. mysqli_select_db($con, </span>‘springdemo‘<span style="color: #000000">);
  6. $input_id </span>= mysqli_real_escape_string($con, $_GET[‘id‘<span style="color: #000000">]);
  7. $sql </span>= ‘select nickname from user where id = ‘<span style="color: #000000"> . $input_id;
  8. print_r(</span>‘SQL is:‘ . $sql . ‘<br/>‘<span style="color: #000000">);
  9. $result </span>=<span style="color: #000000"> mysqli_query($con, $sql);
  10. </span><span style="color: #0000ff">if</span> ($result != <span style="color: #0000ff">null</span><span style="color: #000000">) {
  11.   print_r(</span>‘rows:‘ . mysqli_num_rows($result) . ‘<br/>‘<span style="color: #000000">);
  12.   </span><span style="color: #0000ff">while</span> ($row =<span style="color: #000000"> mysqli_fetch_array($result)) {
  13.     print_r($row[</span>‘nickname‘] . ‘<br/>‘<span style="color: #000000">);
  14.   }
  15. }
  16. mysqli_close($con);
  17. </span>?>

测试：

  1. http:<span style="color: #008000">//</span><span style="color: #008000">localhost:8080/test.php?id=3%20or%201=1</span>
  2. <span style="color: #000000">
  3. PHP version:</span>5.5.30<span style="color: #000000">
  4. SQL is:select nickname from user where id </span>= 3 or 1=1<span style="color: #000000">
  5. rows:</span>4<span style="color: #000000">
  6. abc
  7. micro
  8. helloworld
  9. 你好
  10. <span style="color: #ff0000">注：仍然有问题，因为没有在url里面加引号！</span></span>

采用推荐的mysqli的PreparedStatement方式：

  1. ?<span style="color: #000000">php
  2. header(</span>‘Content-Type: text/html; charset=utf-8‘<span style="color: #000000">);
  3. echo </span>"PHP version:" . PHP_VERSION . "<br/>"<span style="color: #000000">;
  4. $con </span>= <span style="color: #0000ff">new</span> mysqli(‘10.117.146.21:8306‘, ‘root‘, ‘[password]‘, ‘springdemo‘<span style="color: #000000">);
  5. </span><span style="color: #008000">//</span><span style="color: #008000">mysqli_select_db($con, ‘springdemo‘);</span>
  6. <span style="color: #000000">
  7. $query </span>= $con->prepare(‘SELECT nickname FROM user WHERE id = ?‘<span style="color: #000000">);
  8. <span style="color: #ff0000">$query</span></span><span style="color: #ff0000">->bind_param(‘s‘, $_GET[‘id‘</span><span style="color: #000000"><span style="color: #ff0000">]);</span>
  9. $query</span>-><span style="color: #000000">execute();
  10. <span style="color: #ff0000">$result </span></span><span style="color: #ff0000">= $query->get_result();
  11. </span><span style="color: #0000ff">if</span> ($result != <span style="color: #0000ff">null</span><span style="color: #000000">) {
  12.   print_r(</span>‘rows:‘ . mysqli_num_rows($result) . ‘<br/>‘<span style="color: #000000">);
  13.   </span><span style="color: #0000ff">while</span> ($row =<span style="color: #000000"> mysqli_fetch_array($result)) {
  14.     print_r($row[</span>‘nickname‘] . ‘<br/>‘<span style="color: #000000">);
  15.   }
  16. }
  17. mysqli_close($con);
  18. </span>?>

测试：

  1. http:<span style="color: #008000">//</span><span style="color: #008000">localhost:8080/test.php?id=3</span>
  2. <span style="color: #000000">
  3. PHP version:</span>5.5.30<span style="color: #000000">
  4. rows:</span>1<span style="color: #000000">
  5. micro
  6. http:</span><span style="color: #008000">//</span><span style="color: #008000">localhost:8080/test.php?id=3%20or%201=1</span>
  7. <span style="color: #000000">
  8. PHP version:</span>5.5.30<span style="color: #000000">
  9. rows:</span>1<span style="color: #000000">
  10. micro</span>

 

PDO与mysqli的对比：

  1. <span style="color: #000000">性能
  2. PDO和MySQLi都有非常好的性能。在非prepared statements的基准测试下，MySQLi略快2.</span>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方式：

  1. <?<span style="color: #000000">php
  2. header(</span>‘Content-Type: text/html; charset=utf-8‘<span style="color: #000000">);
  3. echo </span>"PHP version:" . PHP_VERSION . "<br/>"<span style="color: #000000">;
  4. $pdo </span>= <span style="color: #0000ff">new</span> PDO("mysql:host=10.117.146.21:8306;dbname=springdemo", ‘root‘, ‘[password]‘<span style="color: #000000">);
  5. </span><span style="color: #0000ff">class</span><span style="color: #000000"> Name {
  6.    </span><span style="color: #0000ff">public</span><span style="color: #000000"> $nickname;
  7.    </span><span style="color: #0000ff">public</span><span style="color: #000000"> function info()
  8.    {
  9.       </span><span style="color: #0000ff">return</span> ‘#‘.$<span style="color: #0000ff">this</span>-><span style="color: #000000">nickname;
  10.    }
  11. }
  12. $input_id </span>= $_GET[‘id‘<span style="color: #000000">];
  13. $sql </span>= ‘select nickname from user where id = ‘<span style="color: #000000"> . $input_id;
  14. print_r(</span>‘SQL is:‘ . $sql . ‘<br/>‘<span style="color: #000000">);
  15. $result </span>= $pdo-><span style="color: #000000">query($sql);
  16. $result</span>->setFetchMode(PDO::FETCH_CLASS, ‘Name‘<span style="color: #000000">);
  17. </span><span style="color: #0000ff">if</span> ($result != <span style="color: #0000ff">null</span><span style="color: #000000">) {
  18.   </span><span style="color: #0000ff">while</span> ($row = $result-><span style="color: #000000">fetch()) {
  19.     print_r($row</span>->info() . ‘<br/>‘<span style="color: #000000">);
  20.   }
  21. }
  22. $pdo</span>=<span style="color: #0000ff">null</span><span style="color: #000000">;
  23. </span>?>

测试：

  1. http:<span style="color: #008000">//</span><span style="color: #008000">localhost:8080/test.php?id=3</span>
  2. <span style="color: #000000">
  3. PHP version:</span>5.5.30<span style="color: #000000">
  4. SQL is:select nickname from user where id </span>= 3<span style="color: #000000">
  5. #micro
  6. http:</span><span style="color: #008000">//</span><span style="color: #008000">localhost:8080/test.php?id=3%20or%201=1</span>
  7. <span style="color: #000000">
  8. PHP version:</span>5.5.30<span style="color: #000000">
  9. SQL is:select nickname from user where id </span>= 3 or 1=1<span style="color: #000000">
  10. #abc
  11. #micro
  12. #helloworld
  13. #你好</span>

加上转码：

  1. <span style="color: #ff0000">$input_id = $pdo->quote($_GET[‘id‘]);</span>

测试：

  1. http:<span style="color: #008000">//</span><span style="color: #008000">localhost:8080/test.php?id=3</span>
  2. <span style="color: #000000">
  3. PHP version:</span>5.5.30<span style="color: #000000">
  4. SQL is:select nickname from user where id </span>= ‘3‘<span style="color: #000000">
  5. #micro
  6. http:</span><span style="color: #008000">//</span><span style="color: #008000">localhost:8080/test.php?id=3%20or%201=1</span>
  7. <span style="color: #ff0000">
  8. PHP version:5.5.30
  9. SQL is:select nickname from user where id = ‘3 or 1=1‘
  10. #micro</span>

注意，pdo的quote自动加了引号，解决了这个问题。

  1. http:<span style="color: #008000">//</span><span style="color: #008000">localhost:8080/test.php?id=3%27%20or%201=%271</span>
  2. <span style="color: #ff0000">
  3. PHP version:5.5.30
  4. SQL is:select nickname from user where id = ‘3\‘ or 1=\‘1‘
  5. #micro</span>

并且，尝试自己加引号去做侵入也没有用的。引号被转码了，所以成功防住攻击。

 

PDO的prepared statement：

  1. <?<span style="color: #000000">php
  2. header(</span>‘Content-Type: text/html; charset=utf-8‘<span style="color: #000000">);
  3. echo </span>"PHP version:" . PHP_VERSION . "<br/>"<span style="color: #000000">;
  4. $pdo </span>= <span style="color: #0000ff">new</span> PDO("mysql:host=10.117.146.21:8306;dbname=springdemo", ‘root‘, ‘[password]‘<span style="color: #000000">);
  5. </span><span style="color: #ff0000">
  6. $prepared = $pdo->prepare(‘select nickname from user where id = :id‘);
  7. $prepared->execute(array(‘:id‘ => $_GET[‘id‘]));
  8. while ($results = $prepared-></span><span style="color: #000000"><span style="color: #ff0000">fetch(PDO::FETCH_ASSOC)) {</span>
  9.     print_r($results[</span>‘nickname‘] . ‘<br/>‘<span style="color: #000000">);
  10. }
  11. $pdo</span>=<span style="color: #0000ff">null</span><span style="color: #000000">;
  12. </span>?>

实验：

  1. http:<span style="color: #008000">//</span><span style="color: #008000">localhost:8080/test.php?id=3</span>
  2. <span style="color: #000000">
  3. PHP version:</span>5.5.30<span style="color: #000000">
  4. micro
  5. http:</span><span style="color: #008000">//</span><span style="color: #008000">localhost:8080/test.php?id=3%20or%201=1</span>
  6. <span style="color: #000000">
  7. PHP version:</span>5.5.30<span style="color: #000000">
  8. micro</span>

都不再出现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传参数的过程改了一下：

  1. <?<span style="color: #000000">php
  2. header(</span>‘Content-Type: text/html; charset=utf-8‘<span style="color: #000000">);
  3. echo </span>"PHP version:" . PHP_VERSION . "<br/>"<span style="color: #000000">;
  4. $pdo </span>= <span style="color: #0000ff">new</span> PDO("mysql:host=10.117.146.21:8306;dbname=springdemo", ‘root‘, ‘[password]‘<span style="color: #000000">);
  5. <span style="color: #ff0000">$prepared </span></span><span style="color: #ff0000">= $pdo->prepare(‘select nickname from user where id = :id‘);
  6. $prepared->bindParam(‘:id‘, $_GET[‘id‘]);
  7. $prepared->execute();
  8. </span><span style="color: #0000ff">while</span> ($results = $prepared-><span style="color: #000000">fetch(PDO::FETCH_ASSOC)) {
  9.     print_r($results[</span>‘nickname‘] . ‘<br/>‘<span style="color: #000000">);
  10. }
  11. $pdo</span>=<span style="color: #0000ff">null</span><span style="color: #000000">;
  12. </span>?>

实验之后，结果对于上面哪些url，都能得到正确的结果。

性能方面：

  1. PDO和MySQLi都有非常好的性能。在非prepared statements的基准测试下，MySQLi略快2.5%，而prepared statements下是6.5%，可以说对于性能无关紧要。<br>如果你真的非常介意这一点点性能的话，而自带的MySQL扩展比两者都快，你可以考虑下它。

 

上面部分来自这篇：http://blog.csdn.net/yipiankongbai/article/details/17277477 《PDO vs. MySQLi 选择哪一个？》

PDO（PHP Data Object），Mysqli，以及对sql注入等问题的解决

标签：函数   state   版本   自动   mode   string   color   ble   对象   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！