什么是函数mysql_real_escape_string的PDO等价物？

I am modifying my code from using mysql_* to PDO. In my code I had mysql_real_escape_string(). What is the equivalent of this in PDO?

我正在修改我的代码使用mysql_ *到PDO。在我的代码中，我有mysql_real_escape_string（）。 PDO中的等价物是什么？

4 个解决方案

#1

Well No, there is none!

嗯不，没有！

Technically there is PDO::quote() but it is rarely ever used and is not the equivalent of mysql_real_escape_string()

从技术上讲，有PDO :: quote（）但它很少被使用，并不等同于mysql_real_escape_string（）

That's right! If you are already using PDO the proper way as documented using prepared statements, then it will protect you from MySQL injection.

那就对了！如果您已经使用预处理语句记录的正确方式使用PDO，那么它将保护您免受MySQL注入。

---

# Example:

Below is an example of a safe database query using prepared statements (pdo)

下面是使用预准备语句（pdo）的安全数据库查询的示例

  try {
     // first connect to database with the PDO object. 
     $db = new \PDO("mysql:host=localhost;dbname=xx;charset=utf8", "xx", "xx", [
       PDO::ATTR_EMULATE_PREPARES => false, 
       PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
     ]); 
 } catch(\PDOException $e){
     // if connection fails, show PDO error. 
   echo "Error connecting to mysql: " . $e->getMessage();
 }

And, now assuming the connection is established, you can execute your query like this.

而且，现在假设已建立连接，您可以像这样执行查询。

if($_POST && isset($_POST['color'])){ 

    // preparing a statement
    $stmt = $db->prepare("SELECT id, name, color FROM Cars WHERE color = ?");

    // execute/run the statement. 
    $stmt->execute(array($_POST['color']));

    // fetch the result. 
    $cars = $stmt->fetchAll(\PDO::FETCH_ASSOC); 
    var_dump($cars); 
 }

Now, as you can probably tell, I haven't used anything to escape/sanitize the value of $_POST["color"]. And this code is secure from myql-injection thanks to PDO and the power of prepared statements.

现在，正如你可能知道的那样，我没有使用任何东西来逃避/消毒$ _POST [“color”]的值。由于PDO和预处理语句的强大功能，此代码对于myql-injection是安全的。

---

It is worth noting that you should pass a charset=utf8 as attribute, in your DSN as seen above, for security reasons, and always enable PDO to show errors in the form of exceptions.

值得注意的是，出于安全原因，您应该在DSN中传递charset = utf8作为属性，并始终使PDO能够以异常的形式显示错误。

PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION

so errors from you database queries won't reveal sensitive data like your directory structure, database username etc.

因此，数据库查询中的错误不会泄露敏感数据，如目录结构，数据库用户名等。

Last but not least, there are moments when you should not trust PDO 100%, and will be bound to take some extra measures to prevent sql injection, one of those cases is, if you are using an outdated versions of mysql [ mysql =< 5.3.6 ] as described in this answer

最后但并非最不重要的是，有些时候你不应该信任PDO 100％，并且必然要采取一些额外措施来防止sql注入，其中一种情况是，如果你使用的是过时版本的mysql [mysql = < 5.3.6]如本答案所述

But, using prepared statements as shown above will always be safer, than using any of the functions that start with mysql_

但是，使用如上所示的预处理语句总是比使用以mysql_开头的任何函数更安全

Good reads

好读

PDO Tutorial for MySQL Developers

#2

There is none*! The object of PDO is that you don’t have to escape anything; you just send it as data. For example:

空无一人*！ PDO的目标是你不必逃避任何事情;你只需将其作为数据发送。例如：

$query = $link->prepare('SELECT * FROM users WHERE username = :name LIMIT 1;');
$query->execute([':name' => $username]); # No need to escape it!

As opposed to:

相反：

$safe_username = mysql_real_escape_string($username);
mysql_query("SELECT * FROM users WHERE username = '$safe_username' LIMIT 1;");

_{* Well, there is one, as Michael Berkowski said! But there are better ways.}

*好吧，就像Michael Berkowski所说的那样！但有更好的方法。

#3

$v = '"'.mysql_real_escape_string($v).'"'; 

is the equivalent of $v = $this->db->quote($v); be sure you have a PDO instance in $this->db so you can call the pdo method quote()

相当于$ v = $ this-> db-> quote（$ v）;确保在$ this-> db中有一个PDO实例，这样你就可以调用pdo方法quote（）

#4

If to answer the original question, then this is the PDO equivalent for mysql_real_escape_string:

如果要回答原始问题，那么这是mysql_real_escape_string的PDO等价物：

function my_real_escape_string($value, $connection) {
    /* 
    // this fails on: value="hello'";
    return trim ($connection->quote($value), "'");
    */
    return substr($connection->quote($value), 1, -1);       
}

btw, the mysqli equivalent is:

顺便说一下，mysqli的等价物是：

function my_real_escape_string($value, $connection) {
    return mysqli_real_escape_string($connection, $value);
}