从mysqli更改为pdo [英] Changing from mysqli to pdo

问题描述

我对这段代码没有任何疑问，它出奇地工作正常，但是我真的不明白它是如何工作的，甚至是正确的，所以:

I do not have a problem with this code it surprisingly works fine but I really don't understand how it works or even is it right, so:

我用mysqli查询ajax发布或获取呼叫的方式是这样的:

My queries with mysqli for ajax post or get calls were like this:

$con = mysqli_connect('localhost','root','','db') or die(header('Location: ./404.php'));
$add = "INSERT INTO table (id, id2, id3) VALUES('','$fid','')";
            if(mysqli_query($con, $add)){
                echo "added";
                }
$remove = "DELETE FROM table WHERE id2='$fid'";
            if(mysqli_query($con, $remove)){
                echo "removed";
            }
$getInfo = "SELECT * FROM table";
            $result = $con->query($getInfo);
            if($result->num_rows > 0) {
                while($row = $result->fetch_assoc()){
                   //do something
                } 
            }

对于$_POST or $_GET values I used mysqli_real_escape_string

此处已转换为PDO:

try{
$con = new PDO('mysql:host=localhost;dbname=db;charset=utf8mb4', 'root', '', array(PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));

$get = $con->query("SELECT * FROM table");
    foreach($get->fetchAll(PDO::FETCH_ASSOC) as $row){
                    $data['0'] = $row['name'];
                    $data['1'] = $row['email'];
                    return $data;
                }

$add = $con->prepare("INSERT INTO table (id, id2, id3) VALUES(:f1,:f2,:f3)");
            $add->execute(array(':f1' => '', ':f2' => $fid, ':f3' => ''));
            echo "added";

$remove = $con->prepare("DELETE FROM table WHERE id2=:f1");
            $remove->bindValue(':f1', $fid, PDO::PARAM_STR);
            $remove->execute();
                echo "removed";

}catch(PDOException $ex){
    echo "error";
}

现在这行得通，但是我真的不知道它是用pdo正确编写的，不需要在mysqli中使用mysqli_real_escape_string之类的东西. 我可以在网上找到的是它现在是怎么写的，代码的哪一部分在做什么，例如当我在mysqli中使用时，我使用

Now this works but I don't really know is it properly written with pdo where I don't need to use something like mysqli_real_escape_string with mysqli or something like that. And all I could find on web is that how it is written now what part of code is doing what for example when I use in mysqli insert, update or delete I use

if(mysqli_query($con, $sql)){echo "success";}else echo 'fail';

如何使用pdo做到这一点? 另外，对于使用try and catch，我不知道我是否需要对每个查询使用它，或者我在上面添加的内容?

How can I do that with pdo? And also for using try and catch I don't know do I need to use it for every query or as I added above?

再说一遍，我对pdo还是陌生的，我不太了解它，上面的代码也可以，但是我不知道它编写的方式正确吗?

Just to say again I am new to pdo, I don't understand it very well and this code from above works but I do not know is it written the right way?

推荐答案

首先，让我祝贺您使用PDO.在我认识的所有经验丰富的PHP开发人员中，几乎所有人都同意，他们更喜欢PDO而不是mysqli.

First off, let me congratulate you for going with PDO. Out of all the experienced PHP developers I know, it's near unanimous that they prefer PDO to mysqli.

我强烈建议您通读本指南以了解如何使用PDO.它应该可以回答您所有的问题，甚至可以回答您将来可能遇到的一些问题.

I highly recommend you read through this guide to using PDO. It should answer all your questions and even answer a few you will likely have in the future.

对于您的具体问题:

不，只要您使用带占位符的预备语句，就不再需要转义任何内容.转义之所以存在，正是因为人们正在将变量插值到SQL语句中，并且这可能会使您需要用引号引起来的字符串混淆.

No you do not need to escape anything anymore, so long as you are using prepared statements with placeholders. Escaping existed exactly because people were interpolating variables into SQL statements and that could confuse the quoting you needed to enclose strings.

使用已准备好的发出该语句的语句不再存在，这也意味着不再存在SQL注入的危险. SQL注入利用字符串连接的优势，再次使用引号将原始SQL语句转换为完全不同的SQL语句，这就是为什么从用户输入接受的非转义字符串是SQL注入的攻击向量的原因.这两个问题都可以通过使用参数和准备好的语句来解决.

With prepared statements that issue no longer exists, which also means that there is no longer the danger of SQL injection. SQL injection takes advantage of string concatenation to transform the original SQL statement into an entirely different one, again using quotes, which is why a non-escaped string accepted from user input was the attack vector for SQL injection. Both problems are solved using parameters and prepared statements.

对于使用PDO进行错误处理，您想利用PDO::ERRMODE_EXCEPTION进行讨论，该PDO::ERRMODE_EXCEPTION

As for error handling with PDO, you want to utilize PDO::ERRMODE_EXCEPTION which is discussed in the manual here.

不幸的是，PDO的默认值为PDO::ERRMODE_SILENT，它实际上会忽略数据库错误，而只是设置您必须检查自己的PDO对象变量.

Unfortunately, the default for PDO is PDO::ERRMODE_SILENT which essentially ignores database errors and just sets PDO object variables you would have to check yourself.

话虽如此，您可以通过在创建PDO连接对象时或之后添加错误模式来解决此问题.示例在我链接的PDO错误模式页面上.

With that said, you can fix this by adding the error mode when you create the PDO connection object or just afterwards. Examples are on the PDO error mode page I linked.

对于Try-Catch块，通常来说，除非您有一些功能代码可以解决该错误，否则您不是要专门捕获异常的对象.从DRY以及反模式的角度来看，包装每个sql调用以使您可以报告错误消息是不好的.使用适当的错误模式，SQL错误将引发您可以在错误处理程序中处理的异常，并且通常情况下您不应该烦恼并从此继续.

As for Try-Catch blocks, in general an exception is not something you want to catch specifically unless you have some functional code to work around the error. Wrapping every sql call just so you can report an error message is bad, both from the point of view of DRY as well as being an anti-pattern. With the proper error mode, SQL errors will throw exceptions that you can handle in your error handler, and in general are things you shouldn't be eating up and continuing on from.

您的错误处理程序应该是(正在生产中)将错误记录到磁盘上/使系统管理员或站点所有者感到不舒服，并显示专业外观的非特定错误消息来通知用户该问题，并且对于所有异常都应该发生这种情况.

Your error handler should be (in production) logging the error to disk/emailing a sysadmin or site owner, and displaying a professional looking non-specific error message informing the user of the problem and that should be happening for all exceptions.

这篇关于从mysqli更改为pdo的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！