A good example of binding is here: https://laracasts.com/discuss/channels/laravel/sql-native-to-query-builder
Aug 16, 2022
3
Level 1
DB::select named parameters don't seem to be binding
I'm trying to run some raw SQL safely, but my named parameters don't seem to be binding.
Here's my function
public function order_search(string $search_query): array
{
$sql = "SELECT item_lines.sku,
item_parent.internal_order_id, item_parent.vendor_order_id
FROM item_lines
LEFT JOIN item_parent
ON item_lines.item_parent_id = item_parent.id
WHERE sku LIKE '%:search_query%' OR internal_order_id LIKE '%:search_query2%'
OR vendor_order_id LIKE '%:search_query3%'";
return DB::select( DB::raw($sql), ["search_query" => $search_query, "search_query2" => $search_query, "search_query3" => $search_query]);
}
and I'm making the call in postman with a string that returns results when I try it on the command line.
The postman error is
"message": "SQLSTATE[HY093]: Invalid parameter number (SQL: SELECT item_lines.sku,\n item_parent.internal_order_id, item_parent.vendor_order_id\n FROM item_lines\n LEFT JOIN item_parent\n ON item_lines.item_parent_id = item_parent.id\n WHERE sku LIKE '%:search_query%' OR internal_order_id LIKE '%:search_query2%'\n OR vendor_order_id LIKE '%:search_query3%')",
Level 1
The issue was trying to use % to search for "like" results. I solved this by changing
'%:search_query%'
to
CONCAT('%', :search_query3, '%')
Please or to participate in this conversation.