Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Laravel Query Builder, selectRaw or select and raw

What's the difference between:

DB::table('some_table')
->selectRaw('COUNT(*) AS result')
->get();

and:

DB::select(DB::raw(" 
SELECT COUNT(*) AS result
FROM some_table"));

In the documentation https://laravel.com/docs/5.6/queries they advert about using raw()due SQL Injection, but it's the same with selectRaw?

like image 845
pmiranda Avatar asked May 17 '18 19:05

pmiranda


2 Answers

The end result of both is the same i.e but there are some difference:

The first one:

DB::table('some_table')
    ->selectRaw('COUNT(*) AS result')
    ->get();
  • Returns a collection of PHP objects,
  • You can call collections method fluently on the result
  • It is cleaner.

While the second:

DB::select(DB::raw(" 
    SELECT COUNT(*) AS result
    FROM some_table"
));
  • Returns an array of Php object.

Although they have similarities: the raw query string.

like image 144
Oluwatobi Samuel Omisakin Avatar answered Nov 11 '22 09:11

Oluwatobi Samuel Omisakin


Those two examples yield the same result, although with different result data types.

Using raw queries can indeed be an attack vector if you don't escape values used within the query (especially those coming from user input).

However that can be mitigated very easily by using bindings passed as the second parameter of any raw query method, as showcased in the same documentation (selectRaw accepts a second parameter as an array of bindings, as well as other raw methods from the Query Builder such as whereRaw, etc). Actually at the begining of the docs page you referenced, the second paragraph also states the following:

The Laravel query builder uses PDO parameter binding to protect your application against SQL injection attacks. There is no need to clean strings being passed as bindings.

So as long as you're careful and make sure any parameters are passed as bindings and not concatenated as plain values within the raw query string you should be safe.

like image 4
Bogdan Avatar answered Nov 11 '22 09:11

Bogdan