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?

Answer 1

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;'));

Answer 2

More Correct Answer (I Think) For Laravel >= 5.0

DB::raw()

Produces "raw" which means "non-sanitized" string. That's why on Laravel Docs there is a warning:

DB::select() , DB::insert() , DB::update() , DB::delete()

These are the practical methods that Laravel offers which run prepared statements.

DB::statement()

This is again for running prepared statements which are not specific to CRUD.

The methods which use prepared statements provide protection for SQL injection whereas this responsibility belongs to the programmer when using raw expressions.

DB::unprepared()

There is no information about it on Laravel Docs and very little information on internet. Nevertheless I have tested it considering the answer above and this:

Laravel, create MySQL trigger from Migration

e.g. I have tried to create a MySQL trigger with DB::statement() method first but it gave me this error when migrating:

  Exception trace:

  1   PDOException::("SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active.  Consider using PDOStatement::fetchAll().  Alternatively, if
your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.")
      C:\wamp64\www\oscared-laravel\vendor\laravel\framework\src\Illuminate\Database\Connection.php:458

  2   PDOStatement::execute()
      C:\wamp64\www\oscared-laravel\vendor\laravel\framework\src\Illuminate\Database\Connection.php:458

However, when I tested it using DB::unprepared(), it worked smoothly. (Laravel 5.8)