MySQL 准备好的语句 [英] MySQL Prepared Statements

问题描述

我只是想知道是否有一种方法可以在 MySQL 中使用某种形式的准备好的语句，这样我就不必转义所有输入，也不必将所有文件从 MySQL 切换到 MySQLi.我真的不相信转义函数，所以如果有任何可以在常规 MySQL 中工作的替代方法，那就太好了.

I was just wondering if there was a way I could use some form of prepared statements in MySQL so I wouldn't have to escape all my inputs and I wouldn't have to switch all of my files from MySQL to MySQLi. I really don't trust the escaping functions, so if there is any alternatives that work in regular MySQL, it would be great.

推荐答案

使用 PDO (PHP 数据对象) 连接到您的 MySQL 数据库.此方法将确保所有数据库输入将始终被视为文本字符串，您将永远不必进行任何手动转义.

Use PDO (PHP Data Objects) to connect to your MySQL database. This method will make sure that all database input will always be treated as text strings and you will never have to do any manual escaping.

这与正确使用 html_entities() 显示来自数据库的数据相结合，是保护您的页面免遭注入的可靠且好方法.我总是使用 PDO 来处理我项目中的所有数据库连接.

This combined with proper use of html_entities() to display data from your database is a solid and good way to protect your page from injection. I always use PDO to handle all my database connections in my projects.

创建数据库对象(在这种情况下强制执行某种字符编码):

Create database object (and in this case enforce a certain character encoding):

try {
    $db = new PDO("mysql:host=[hostname];dbname=[database]",'[username]','[password]');
    $db->setAttribute(PDO::MYSQL_ATTR_INIT_COMMAND, "SET NAMES utf8");
    $db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
    $db->exec('SET NAMES utf8');
} catch (PDOException $e) {
    echo $e->getMessage();
}

然后像这样使用它:

$id = 1;
$q = $db->prepare('SELECT * FROM Table WHERE id = ?');
$q->execute(array($id));
$row = $q->fetch();
echo $row['Column_1'];

或

$q = $db->prepare('UPDATE Table SET Column_1 = ?, Column_2 = ? WHERE id = ?');
$q->execute(array('Value for Column_1','Value for Column_2',$id));

并使用通配符:

$search = 'John';
$q = $db->prepare('SELECT * FROM Table WHERE Column_1 LIKE ?');
$q->execute(array('%'.$search.'%'));
$num = $q->rowCount();
if ($num > 0) {
  while ($row = $q->fetch()) {
    echo $row['Column_1'];
  }
} else {
  echo "No hits!";
}

阅读更多:

如何防止 PHP 中的 SQL 注入?

何时*不*使用准备好的语句?

PDO 准备语句的安全性

http://php.net/manual/en/book.pdo.php

这篇关于MySQL 准备好的语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！