如何使用标准方式在 Magento 2 中编写选择查询

Posted by

您可以在 Magento 2 中编写一个选择查询来从表中检索记录。可以使用最佳实践编写选择查询以避免安全漏洞。

例如,您正在从core_config_data表中搜索记录,
行查询为:

SELECT * FROM `core_config_data` WHERE `scope` = 'default' AND `path` = 'general/locale/code'

您可以直接使用上面的字符串作为查询并将其传递给query()方法,但这不是编写选择查询的安全方法。我将向您展示使用最佳安全标准实践编写数据库查询的标准 Magento 方法。

<?php
namespace Jesadiya\SelectQuery\Model\ResourceModel;

use Magento\Framework\App\ResourceConnection;

class Data
{
    /**
     * @var ResourceConnection
     */
    private $resourceConnection;

    public function __construct(
        ResourceConnection $resourceConnection
    ) {
        $this->resourceConnection = $resourceConnection;
    }

    /**
     * Select query to fetch records
     *
     * @return array
     */
    public function selectQuery()
    {
        $tableName = $this->resourceConnection->getTableName('core_config_data');

        //Initiate Connection
        $connection = $this->resourceConnection->getConnection();
        $path = 'general/locale/code';
        $scope = 'default';

        $select = $connection->select()
            ->from(
                ['c' => $tableName],
                ['*']
            )
            ->where(
                "c.path = :path"
            )->where(
                "c.scope = :scope"
            );
        $bind = ['path'=>$path, 'scope'=>$scope];
        $records = $connection->fetchAll($select, $bind);

        return $records;
    }
}

上面的 Query [‘*’]表示获取表的所有字段。如果需要,您也可以通过逗号分隔传递特定字段。

您需要使用绑定参数在查询中分别传递每个 where 条件。

->where(
    "c.path = :path"
)->where(
    "c.scope = :scope"
)
$bind = ['path'=>$path, 'scope'=>$scope];

将$path和$scope值传递给绑定数组,而不是将值直接传递给给定条件。

绑定的替代方案(第二种方式)

$select = $connection->select()
            ->from(
                ['c' => $tableName],
                ['*']
            )
            ->where(
                "c.path = ?", $path
            )->where(
                "c.scope = ?", $scope
            );
$records = $connection->fetchAll($select);

输出:

Array
(
    [0] => Array
        (
            [config_id] => 6
            [scope] => default
            [scope_id] => 0
            [path] => general/locale/code
            [value] => en_US
            [updated_at] => 2020-02-10 06:27:49
        )

)