Difference between Laravel's raw SQL functions

Question

It seems I'm not the only person struggling with the differences between Laravel's DB::raw(), DB::select(), DB::statement(), and DB::unprepared() methods. It seems as if one almost needs to try a given SQL statement with all 4 to identify which will work. Can anybody clarify how they relate to each other, and which to use for what purposes?

Solution

I will try to clarify:

DB::raw()

It generates a raw and sanitized SQL string, to be passed to other query/statements, preventing SQL injections. Is to be used with all of the and never alone. And you should never send a not sanitized string to your query/statements.

DB::select(DB::raw('select * from whatever'));

DB::select()

Is for simple selects:

DB::select(DB::raw('select * from whatever'));

DB::statement()

I think it work with selects, but should be used for non SQL query commands:

DB::statement(DB::raw('update whatever set valid = true;'));

DB::unprepared()

All SQL commands in Laravel are prepared by default, but sometimes you need to execute a command in an unprepared mode, because some commands in some database cannot be ran in prepared mode. Here's an issue I opened about this: https://github.com/laravel/framework/issues/53

DB::unprepared(DB::raw('update whatever set valid = true;'));

Comments

[Jarek Tkaczyk]

You made a little mistake in DB::raw - it generates non sanitized string and does not prevent injections. Also DB::statement could accept some commands/statements, like set @var = 0. However for most it should be unprepared like you said.

[dspitzle]

Thanks, that is very helpful.

[Howard]

For DB::statement() what do you mean by, "should be used for non SQL query commands"?

[Syed Haziq Hamdani]

What to use when we call a stored procedure, DB::select or DB::statement? For example: DB::select('call storedProcedure(?,?)',array($a,$b)); or DB::statement('call storedProcedure(?,?)',array($a,$b)); ?