mysql - 使用原始 sql 时的 Laravel SQL 参数绑定(bind)

Question

我有以下查询:

$venues = Venue::select(['id', 'name'])
            ->where('name', 'LIKE', "%{$query}%")
            ->orderByRaw("CASE " .
                         "WHEN name like '{$query}%' THEN 0 " . // start with
                         "WHEN name like '% {$query}%' THEN 1 " . // start of a later word
                         "ELSE 3 " .
                         "END"
            )
            ->limit(5)
            ->get();

问题是上述查询容易受到 SQL 注入(inject)攻击。我该如何解决这个问题？

参数绑定(bind)解释如下:

https://laravel.com/docs/5.6/queries#raw-expressions

但如果我这样做:

$venues = Venue::select(['id', 'name'])
            ->where('name', 'LIKE', "%{$query}%")
            ->orderByRaw("CASE " .
                         "WHEN name like '?%' THEN 0 " . // start with
                         "WHEN name like '% ?%' THEN 1 " . // start of a later word
                         "ELSE 3 " .
                         "END",
                         [
                             $query,
                             $query,
                         ]
            )
            ->limit(5)
            ->get();

我得到了不同的结果。

Answer 1

尝试将百分比添加到查询参数中，如下所示:

...
->orderByRaw("CASE " .
    "WHEN name like ? THEN 0 " . // start with
    "WHEN name like ? THEN 1 " . // start of a later word
    "ELSE 3 " .
    "END",
    [
        "{$query}%",
        "% {$query}%",
    ]
)
...