预备语句，转义变量 [英] Prepared Statements, escape variables

问题描述

我是否需要做任何事情来保护这三个变量，例如使用转义字符串或绑定它们?我不确定我是否正确地做到了这一点，人们只是建议使用准备好的语句，所以我试图找出它们.

Do I need to do anything to protect the three variables, like using the escape string or binding them? I'm not sure if I did this correctly, people just recommended using prepared statements and so I am trying to figure them out.

$order = $_POST['order'];
$heading = $_POST['heading'];
$content = $_POST['content'];    
try {
$dbh = new PDO("mysql:host=$hostname;dbname=saintfiv_faq", $username, $password);
/*** echo a message saying we have connected ***/
echo 'Connected to database<br />';

/*** INSERT data ***/
$stmt = $dbh->prepare("INSERT INTO faq(`order`, `heading`, `content`) VALUES (:order, :heading, :content)");
$stmt->bindParam(':order', $order, PDO::PARAM_INT);
$stmt->bindParam(':heading', $heading, PDO::PARAM_STR, strlen($heading));
$stmt->bindParam(':content', $content, PDO::PARAM_STR, strlen($content));
/*** close the database connection ***/
$stmt->execute();
}
catch(PDOException $e)
{
echo $e->getMessage();
}

推荐答案

您没有在代码中使用准备好的语句.准备好的语句看起来像这样:

You are not using prepared statements in your code. Prepared statements would look more like this:

$stmt = $db->prepare("INSERT INTO foo (bar, baz) VALUES (?, ?);");

$stmt->bindValue(1, "Fez");
$stmt->bindValue(2, "Hat");
$stmt->execute();

您的示例代码可能容易受到SQL注入的攻击，因为您只是将变量直接插入SQL字符串中.您将要使用准备好的语句并绑定值(这是首选的解决方案)，或者只是确保将所有输入正确地转义到exec().

Your example code is potentially vulnerable to SQL injection because you are simply inserting the variables directly into the SQL string. You will want to either use prepared statements and bind the values (this is the preferred solution), or alternatively just make sure you escape all input to exec() correctly.

也许值得一提的是，exec()对于完全硬编码的语句(例如$db->exec("SELECT foo FROM bar;");)很好，因为SQL是硬编码的，所以没有注入SQL的潜力.但是，出于风格考虑，我想始终改用prepare.

It might also be worth mentioning that exec() is fine for totally hardcoded statements - e.g., $db->exec("SELECT foo FROM bar;"); - since the SQL is hardcoded, there is no potential for SQL injection. I, however, like to always use prepare instead, as a matter of style.

要在代码中专门执行查询，您可以执行以下操作:

To specifically execute the query in your code, you would do something like this:

$stmt = $db->prepare("INSERT INTO faq (`order`, `heading`, `content`) " .
    "VALUES (?, ?, ?);");

$stmt->bindValue(1, $order);
$stmt->bindValue(2, $heading);
$stmt->bindValue(3, $content);
$stmt->execute();

我还建议使用官方的PHP文档，因为它显示了执行相同操作的其他方法(即，您可以将参数作为数组传递给execute()，而不是单独绑定它们):

I would also recommend the official PHP documentation, as it shows some other ways of doing the same thing (namely, you can pass your parameters as an array to execute() instead of binding them individually): http://php.net/manual/en/pdo.prepare.php.

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