PDO准备了用于存储html内容的语句 [英] PDO prepared statements to store html content

问题描述

我正在寻找一种在准备好的语句中处理HTML内容的方法.

I'm looking for a way to handle HTML content within prepared statements.

我的应用程序提供了一个基本的所见即所得编辑器，并且在用户保存内容之后，我的脚本将HTML数据存储在sqlite数据库中.

My application provides a basic WYSIWYG Editor and after the user is saving the content my script stores HTML-Data in an sqlite database.

但是，如果我使用准备好的语句，我的HTML就会自然地转义.

But if i'am using a prepared statement my HTML gets -naturally- escaped.

这就是我到目前为止:

try {

    /* Create databases and open connections */
    $dbh = new PDO( 'sqlite:db/coaching.sqlite' );

    /* Set Error Mode for Exception Handling */
    $dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

    /* Prepare SQL Statement */
    $query = $dbh->prepare( "UPDATE Content SET Value=:value WHERE Token=:token" );

    /* Bind Param to Statement */
    $query->bindParam( ':token', $_POST['id'], PDO::PARAM_STR);
    $query->bindParam( ':value', $_POST['value'], PDO::PARAM_STR);

    /* Execute Query */
    $query->execute();

    /* Echo Data */
    echo $_POST['value'];

    /* Close connections to Database */
    $dbh = NULL;

}
catch( PDOException $e ) {

    /* Print Error-Messages */
    echo $e->getMessage();
}

推荐答案

准备好的语句不会转义变量.命令和变量同时但独立地传输到数据库.如果您看到数据在数据库中转义，则还有另一个原因.例如. magic_quotes已打开.您可以在脚本中回显get_magic_quotes_gpc来查看它们是打开还是关闭?如果启用，则可以使用不同的技术将其设置为关闭" .这样可以解决问题.

Prepared statements do not escape variables. The command and the variables are transferred to database simultaneously but independently. If you see your data escaped in database, there's another reason. E.g. magic_quotes are turned on. Can you echo get_magic_quotes_gpc in your script to see if they're On or Off? If they're On, you can set them Off using different techniques. This will solve the problem.

此外，在您发表评论后，准备好的声明会 防止 SQL注入攻击，因此您不必担心转义变量.可能很难理解的是准备好的语句的工作方式.假设您有一个查询:

Additionaly, following your comment, prepared statements do prevent SQL injection attacks so you don't have to worry about escaping your variables. What may be difficult to understand is the way the prepared statements work. Say you have a query:

$query = "SELECT `id` FROM `users` WHERE `login` = '" . $login . "' AND `password` = '" . $password ."'";

$login和$password照原样直接传递给查询.如果有人尝试将mylogin' --传递给$login，则查询变为:

$login and $password are passed to the query directly, as they are. If someone attempts to pass mylogin' -- to $login, the query becomes:

$query = "SELECT `id` FROM `users` WHERE `login` = 'mylogin' -- ' AND `password` = 'anypassword'";

并发送到数据库.这样，攻击者就可以访问任何帐户.

and is send to the database. This way an attacker can gain access to any account.

准备好的语句做什么，它们独立于查询传递查询参数.在将查询传输到数据库之前，该查询不是基于变量的构建.而是将变量以某种方式转移到查询旁边.在查询中引用它们.这样一来，就不会无意或无意地欺骗查询.

What prepared statements do, they transfer the query arguments independly of the query. The query is NOT build of the variables before it is transferred to database. Instead, the variables are transferred somehow next to the query. They are referenced in the query. This way the query can't be spoofed neither intentionally nor unintentionally.

使用准备好的语句，示例的$login将按原样传输，并且不会影响查询结构.

With prepared statement the exampled $login will be transferred as is, and will not affect the query structure.

如果可以乘飞机运载乘客，而实际上乘客实际上并不登机，那将被称为准备好的航班" :)乘客将无法影响航线并劫持飞机.他们会在飞机降落时出现在目标机场.

If it would be possible to carry passengers by plane, with passengers actually NOT boarding the plane, that would be called a "prepared flight" :) Passengers would not be able to influence the route and hijack the plane. They would have appeared at the target airport with the plane landing.

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