Laravel Query Builder, selectRaw or select and raw

Question

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?

Steed

Yess these are the same – Rasmussen

Note that DB::select already accepts a raw SQL query string, so the inner DB::raw in the second example is unnecessary, and will actually screw up things like query logging. – Reinaldoreinaldos

Answer 1

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.

Treasurehouse

I have picked this one as the correct because my question was the difference between them more than the SQL injection part. Thanks. – Steed

Answer 2

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.

Runesmith

just wondering - DB::raw does not accept second parameter - where did you get that information from? I only know of selectRaw - this one really accepts bindings and acts as appender. – Cool

@Cool You are absolutely right. DB::raw does not accept a second parameter, as its main purpose is to return a query Expression instance so the query builder can determine when a parameter should be escaped based on whether or not it's an expression or some other data type. It was my mistake. Thanks for pointing out the error, I've updated the answer. – Runesmith