Laravel原生SQL语句中替换PHP变量的方法

直接拼接PHP变量到原生SQL里会引发SQL注入风险，Laravel提供了安全的参数绑定方式，推荐用以下两种方法：

方法一：位置占位符绑定 #

在SQL语句中用?代替需要替换的变量，然后将变量作为第二个参数传入DB::select()，参数顺序要和占位符一一对应：

// 定义PHP变量
$shift1Id = '48c73e3d-57de-4fea-aae4-7222d7e6afed';
$shift2Id = '463ec995-cfad-46ff-8e65-3115e6e651c5';
$shift3Id = '0a792c5f-84a6-4534-818c-427cfe8cd3ca';
$productId = '3516c43b-ffe3-4e90-9d93-924a9865c9cd';
$customerId = 'd9916741-ca61-4a57-b281-15c80fad833c';
$startDate = '2023-04-07';
$endDate = '2023-04-09';

// 替换SQL中的硬编码值为?占位符
$query = <<<SQL
SELECT day, date, product1_shift1, product1_shift2, product1_shift3, 
COALESCE(product1_shift1, 0) + COALESCE(product1_shift2, 0) + COALESCE(product1_shift3, 0) AS total_product1_qty
FROM (
    SELECT DAYNAME(date) AS day, DATE_FORMAT(date, "%d-%m-%Y") AS date,
        (SELECT SUM(qty) AS product1_shift1
        FROM bill_details 
        JOIN bills ON bills.bill_id=bill_details.bill_id
        WHERE bills.shift_id=?
        AND bills.date=bills_master.date
        AND bills_master.customer_id=bills.customer_id
        AND bill_details.product_id=?) AS product1_shift1,
        (SELECT SUM(qty) AS product1_shift2
        FROM bill_details 
        JOIN bills ON bills.bill_id=bill_details.bill_id
        WHERE bills.shift_id=?
        AND bills.date=bills_master.date
        AND bills_master.customer_id=bills.customer_id
        AND bill_details.product_id=?) AS product1_shift2,
        (SELECT SUM(qty) AS product1_shift3
        FROM bill_details 
        JOIN bills ON bills.bill_id=bill_details.bill_id
        WHERE bills.shift_id=?
        AND bills.date=bills_master.date
        AND bills_master.customer_id=bills.customer_id
        AND bill_details.product_id=?) AS product1_shift3
    FROM bills bills_master
    WHERE bills_master.date BETWEEN ? AND ?
        AND bills_master.customer_id=?
    GROUP BY bills_master.date
) subquery_alias
SQL;

// 传入参数数组，顺序和占位符一致
dd(DB::select($query, [
    $shift1Id, $productId,
    $shift2Id, $productId,
    $shift3Id, $productId,
    $startDate, $endDate,
    $customerId
]));

方法二：命名占位符绑定 #

用:变量名的形式定义占位符，参数数组用键值对传递，可读性更强，不用担心参数顺序错误：

// 定义PHP变量
$shift1Id = '48c73e3d-57de-4fea-aae4-7222d7e6afed';
$shift2Id = '463ec995-cfad-46ff-8e65-3115e6e651c5';
$shift3Id = '0a792c5f-84a6-4534-818c-427cfe8cd3ca';
$productId = '3516c43b-ffe3-4e90-9d93-924a9865c9cd';
$customerId = 'd9916741-ca61-4a57-b281-15c80fad833c';
$startDate = '2023-04-07';
$endDate = '2023-04-09';

// 替换SQL中的硬编码值为命名占位符
$query = <<<SQL
SELECT day, date, product1_shift1, product1_shift2, product1_shift3, 
COALESCE(product1_shift1, 0) + COALESCE(product1_shift2, 0) + COALESCE(product1_shift3, 0) AS total_product1_qty
FROM (
    SELECT DAYNAME(date) AS day, DATE_FORMAT(date, "%d-%m-%Y") AS date,
        (SELECT SUM(qty) AS product1_shift1
        FROM bill_details 
        JOIN bills ON bills.bill_id=bill_details.bill_id
        WHERE bills.shift_id=:shift1_id
        AND bills.date=bills_master.date
        AND bills_master.customer_id=bills.customer_id
        AND bill_details.product_id=:product_id) AS product1_shift1,
        (SELECT SUM(qty) AS product1_shift2
        FROM bill_details 
        JOIN bills ON bills.bill_id=bill_details.bill_id
        WHERE bills.shift_id=:shift2_id
        AND bills.date=bills_master.date
        AND bills_master.customer_id=bills.customer_id
        AND bill_details.product_id=:product_id) AS product1_shift2,
        (SELECT SUM(qty) AS product1_shift3
        FROM bill_details 
        JOIN bills ON bills.bill_id=bill_details.bill_id
        WHERE bills.shift_id=:shift3_id
        AND bills.date=bills_master.date
        AND bills_master.customer_id=bills.customer_id
        AND bill_details.product_id=:product_id) AS product1_shift3
    FROM bills bills_master
    WHERE bills_master.date BETWEEN :start_date AND :end_date
        AND bills_master.customer_id=:customer_id
    GROUP BY bills_master.date
) subquery_alias
SQL;

// 传入键值对参数
dd(DB::select($query, [
    'shift1_id' => $shift1Id,
    'shift2_id' => $shift2Id,
    'shift3_id' => $shift3Id,
    'product_id' => $productId,
    'start_date' => $startDate,
    'end_date' => $endDate,
    'customer_id' => $customerId
]));

注意事项 #

• 绝对不要用字符串拼接（比如"WHERE shift_id='$shiftId'"）或双引号插值的方式替换变量，这会直接暴露SQL注入漏洞。
• 两种绑定方式都会自动处理变量的转义，确保SQL语句的安全性。

内容的提问来源于stack exchange，提问作者DenjanD