我正在使用 Case 进行 SQL 选择查询，并希望动态计算价格

Question

我的控制器代码

$nicepay_commission = Configure::read('nicepay_commission');

$paypal_commission = Configure::read('paypal_commission');

 

$getQuery = $this->OrderProduct

    ->find('all', [

        'contain' => [

            'Orders' => ['PaymentMethods'],

            'Products' => ['ProductType']

        ]

    ])

   ->distinct('Products.id')

   ->select([

        'product_name' => 'MAX(Products.product_name)',

        'count' => 'SUM(OrderProduct.qty)', 

        'actual_rate' => 'SUM(OrderProduct.actual_rate)',

        'revenue_based_actual_rate' => '(

            SUM(

                CASE

                WHEN PaymentMethods.payment_gateway = \'nicepay\'

                THEN (OrderProduct.actual_rate-((OrderProduct.actual_rate*"'.$nicepay_commission.'")/100))

                WHEN PaymentMethods.payment_gateway = \'paypal\'

                THEN (OrderProduct.actual_rate-((OrderProduct.actual_rate*"'.$paypal_commission.'")/100))

                ELSE (OrderProduct.actual_rate)

                END

            )

        )'

   ])

   ->where($conditions);

但发生了一些错误，我找不到如何管理它。

我的错误日志看起来像

2020-08-20 07:56:56 错误：[PDOException] SQLSTATE[42S22]：[Microsoft][SQL Server 的 ODBC 驱动程序 17][SQL Server]无效的列名“2”。

如果我静态使用这些值，那么就没有错误

$getQuery = $this->OrderProduct

    ->find('all', [

        'contain' => [

            'Orders' => ['PaymentMethods'],

            'Products' => ['ProductType']

        ]

    ])

Answer 1

首先，如果可以避免，切勿将日期直接插入 SQL 片段，即使您认为它们可能来自安全来源！也就是说，查看生成的 SQL 查询（如果您还没有使用Debug Kit，则应该安装它），您将值括在双引号中，即生成的 SQL 将如下所示：OrderProduct.actual_rate * "2"在 ISO SQL 中，这意味着2将用作标识符。删除引号可以解决问题，但是您仍然将动态数据注入到 SQL 字符串中，如果可能的话应该避免这种情况，因此您应该更进一步并绑定值，以减少创建 SQL 的机会注入漏洞：// ...->select([    'product_name' => 'MAX(Products.product_name)',    'count' => 'SUM(OrderProduct.qty)',     'actual_rate' => 'SUM(OrderProduct.actual_rate)',    'revenue_based_actual_rate' => '(        SUM(            CASE            WHEN PaymentMethods.payment_gateway = \'nicepay\'            THEN (OrderProduct.actual_rate-((OrderProduct.actual_rate * :nicepayCommission)/100))            WHEN PaymentMethods.payment_gateway = \'paypal\'            THEN (OrderProduct.actual_rate-((OrderProduct.actual_rate * :paypalCommission)/100))            ELSE (OrderProduct.actual_rate)            END        )    )'])->bind(':nicepayCommission', $nicepay_commission, 'integer')->bind(':paypalCommission', $paypal_commission, 'integer')// ...