使用 Magento 方法编写插入查询并注意 SQL 注入 [英] Using Magento Methods to write Insert Queries with care for SQL Injection

问题描述

我正在使用 Magento 的功能来插入 &更新查询.我的要求是在执行这些类型的查询时我想处理 SQL 注入.但我无法找到 Magento 如何做到这一点.我提供了一个开始样本.请给我一个完整的例子.

I am using the Magento's functionality to insert & update queries. My requirement is that I want to take care of SQL Injection, when doing these types of queries. But I'm unable to find how Magento does this. I'm providing one start sample. Please provide me with one complete example.

<?php
$write = Mage::getSingleton("core/resource")->getConnection("core_write");
$sql = "INSERT INTO Mage_Example (Name, Email, Company, Description, Status, Date)
    VALUES ('$name', '$email', '$company', '$desc', '0', NOW())";
?>

现在我想更改上面的查询以防止可能的 SQL 注入.我不想使用 PHP 的默认mysql_real_escape_string()"内置函数.任何人都可以使用$write"数据库处理程序为我提供一个有用的解决方案.

Now I want to change the above query to prevent the possible SQL Injection. I don't want to use the default "mysql_real_escape_string()" built-in function of PHP. Can anybody please provide me with one useful solution, using the "$write" DB Handler.

非常感谢任何帮助.

推荐答案

好的，研究了一下这个.如果你能得到一个 DB_Adapter 的实例(我相信资源调用会返回)，这应该不会太难.在内部，Magento 是基于 Zend 框架的，而 DB 适配器特别是源自 Zend_Db_Adapter，所以你可以免费使用这些方法.有关更多示例，请参阅之前的链接，但这里是文档中提供的语法，它应该会自动转义您的输入:

Okay, researched this one a little bit. If you can get an instance of a DB_Adapter (which I believe that resource call will return), this shouldn't be too tough. Deep down inside, Magento is based on Zend Framework, and the DB adapter specifically is descended from Zend_Db_Adapter, so you can use those methods for free. See the link before for more examples, but here's the syntax provided in the docs, which should escape your input automagically:

$write = Mage::getSingleton("core/resource")->getConnection("core_write");

// Concatenated with . for readability
$query = "insert into mage_example "
       . "(name, email, company, description, status, date) values "
       . "(:name, :email, :company, :desc, 0, NOW())";

$binds = array(
    'name'    => "name' or 1=1",
    'email'   => "email",
    'company' => "company",
    'desc'    => "desc",
);
$write->query($query, $binds);

同样，请参阅文档以获取更多信息.

Again, see the docs for more information.

更新:

我已经更改了上面的示例.您通过 core_write 请求返回的对象是一个 PDO 对象，它公开了一个 query 方法(见上文)，可以让您使用参数化查询.这是比尝试使用诸如 mysql_real_escape_string 之类的东西进行数据清理更好的方法，而且我已经测试了上述代码的正确性.请注意，与大多数 MySQL 参数化查询相比，绑定是使用 :labels 完成的，而且您的变量不需要引号.

I've changed the example above. The object that you get back with your core_write request is a PDO object that exposes a query method (see above) that will let you used parameterized queries. This is BY FAR a better approach than attempting to use something like mysql_real_escape_string for data sanitization, and I've tested the above code for correctness. Note that, in contrast to most MySQL parameterized queries, the binding is done with :labels, and also that you need no quotes for your vars.

针对您的另一点，如下所述，在 Magento 中执行此操作的正确"方法根本不使用直接查询.Magento 对象模型开发得很好，旨在将这种实现细节从您身上抽象出来，因为您不需要关心它.要正确"做到这一点，请创建一个新的基于数据库的模型并省去麻烦.

In response to your other point, and as noted below, the "right" way to do it in Magento is not to use direct queries at all. The Magento object models are well development and meant to abstract this kind of implementation detail away from you, because you shouldn't need to concern yourself with it. To do it "correctly", create a new database-based model and save the headache.

这篇关于使用 Magento 方法编写插入查询并注意 SQL 注入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！