I've got the following raw query:
$results = Db::select( Db::raw("SELECT HOUR(created_at) as hour, COUNT(*) as count
FROM `visited`
WHERE created_at >= DATE_SUB(NOW(),INTERVAL 16 DAY)
GROUP BY HOUR(created_at)") );
I need to parameterize the day interval, so I tried this:
$days = 16;
$results = Db::select( Db::raw("SELECT HOUR(created_at) as hour, COUNT(*) as count
FROM `visited`
WHERE created_at >= DATE_SUB(NOW(),INTERVAL :days DAY)
GROUP BY HOUR(created_at)", ["days" => $days]) );
But I am getting the following error:
"SQLSTATE[HY000]: General error: 2031
Apparently the binding is not working. What am I doing wrong?
DB::raw() is used to make arbitrary SQL commands which aren't parsed any further by the query builder. They therefore can create a vector for attack via SQL injection.
Raw SQL, sometimes also called native SQL, is the most basic, most low-level form of database interaction. You tell the database what to do in the language of the database. Most developers should know basics of SQL. This means how to CREATE tables and views, how to SELECT and JOIN data, how to UPDATE and DELETE data.
For each named parameter, provide an argument to bind() that contains the parameter name and its value. The order in which the parameter value pairs are passed to bind() is of no importance. The example assumes that the test schema has been assigned to the variable db and that the collection my_collection exists.
Try this:
$results = DB::select('SELECT HOUR(created_at) as hour, COUNT(*) as count FROM visited WHERE created_at >= DATE_SUB(NOW(),INTERVAL ? DAY) GROUP BY HOUR(created_at)', [16]);
You can even use named bindings:
$results = DB::select('SELECT HOUR(created_at) as hour, COUNT(*) as count FROM visited WHERE created_at >= DATE_SUB(NOW(),INTERVAL :days DAY) GROUP BY HOUR(created_at)', ['days' => 16]);
Don't need to use DB::raw()
, just use DB::select()
for simple raw select queries: https://laravel.com/docs/master/database#running-queries
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With