使用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 Framework，并且数据库适配器专门源自 Zend_Db_Adapter ，因此您可以免费使用这些方法.有关更多示例，请参见前面的链接，但这是docs中提供的语法，该语法应自动转义您的输入:

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屋！