Binding on `statement` not supported in Eloquent 5.1?

Question

I'm new to Laravel and trying to do a string query in Eloquent. I was trying to use DB::statement, but I kept getting errors about placeholders in the query string. It seems I either don't have the syntax right, or bindings are unimplemented or unsupported?

The reason I want to use statement is because I'm doing an INSERT... SELECT, which I haven't been able to find any documentation about in Eloquent.

Here's my code:

$ php artisan tinker
Psy Shell v0.5.2 (PHP 5.6.13-0+deb8u1 — cli) by Justin Hileman
>>> echo \DB::statement('CREATE DATABASE :db', [':db'=>'test']);
Illuminate\Database\QueryException with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1 (SQL: CREATE DATABASE :db)'
>>> \DB::statement('CREATE DATABASE ?', ['test']);
Illuminate\Database\QueryException with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1 (SQL: CREATE DATABASE test)'

These are the two syntax forms (? and :string) from PDO. Other methods in DB such as select and insert support this, according to the documentation.

The relevant parts of these errors are near '?' at line 1 (SQL: CREATE DATABASE :db) and near '?' at line 1 (SQL: CREATE DATABASE test). MySQL thinks there is an unbound ? in the query string. I didn't even use that syntax in the first query. I'm concluding from that that the bind() method did not correctly bind my placeholders.

This question on Laracasts is asking about the syntax, but there is no accepted answer.

Edit One answer says that statement() doesn't support CREATE. I tried some queries out with SELECT, and got the same results, with both placeholders:

>>> \DB::statement('SELECT 1 WHERE \'a\' = ?', array('a'));
Illuminate\Database\QueryException with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE 'a' = ?' at line 1 (SQL: SELECT 1 WHERE 'a' = a)'
>>> \DB::statement('SELECT 1 WHERE \'a\' = :letter', array(':letter'=>'a'));
Illuminate\Database\QueryException with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE 'a' = ?' at line 1 (SQL: SELECT 1 WHERE 'a' = :letter)'

Answer 1

Actually, you can use create and drop query in DB::statement(), but named bindings is not used in that way.

Here are some queries that will success.

drop and create do not accept bindings.

>>> \Db::statement('create database test')
=> true
>>> \Db::statement('drop database test')                                                                                                                                              
=> true

Do not use backslash and single quotes in the statement

>>> \Db::statement('insert into users (id, name) values (?, ?)', ['1', 'John'])
=> true 

DB::statement() only return ture when success, so if you want to see select results, you should use DB::select()

>>> \Db::statement('select * from users')
=> true
>>> \Db::select('select * from users')                                                                                                                                                
=> [
     {#770
       +"id": 1,
       +"name": "John",
     },
   ]

Remove leading : in the second argument.

>>> \Db::statement('update users set name = :name where id = :id', ['id' => 1, 'name' => 'John'])
=> true

You will get affect rows if you use DB::update and DB::delete

>>> \Db::delete('delete from users where id = :id', ['id' => 1])
=> 1

Answer 2

The errors you receive are only indirectly related with Laravels DB::statement() function. They all fail within that method at the line

return $me->getPdo()->prepare($query)->execute($bindings);

within the file vendor/laravel/framework/src/Illuminate/Database/Connection.php

Responsible for that failure is the resulting call to PDO::prepare()

The Docuemenation says:

Parameter markers can represent a complete data literal only. Neither part of literal, nor keyword, nor identifier, nor whatever arbitrary query part can be bound using parameters. For example, you cannot bind multiple values to a single parameter in the IN() clause of an SQL statement.

Also have a look at the user contributed notes at the above php.net documentation. Additionally have a look at Can PHP PDO Statements accept the table or column name as parameter?

Your create examples are not supported by PDO.

The reason your SELECT examples fail is simply due to an invalid syntax.

\DB::statement('SELECT 1 WHERE \'a\' = ?', array('a'))

You are simply missing the FROM clause. This example works perfeclty well at my test computer:

$ret = \DB::statement('SELECT 1 FROM `users` WHERE `username` = ?', ["gregor"]);

But

$ret = \DB::statement('SELECT 1 WHERE `username` = ?', ["testname"]);

Generates the exact error, you receive.

Also note, that \DB::statement does not return any ressources. It just indicates by returning true or false, whether the query suceeded.

Your option is to use DB::raw() within your insert() statement, if you want to use INSERT...SELECT. Some googling will help you, to find the proper solution. Maybe as Starting Points: Raw Queries in Laravel, or How to use raw queries in Laravel

Answer 3

What you're trying to do is passing the table name through binding.

DB::statement('select * from ?',['users'])

which according to this post, it's not possible.

of course if you want to sanitize the data you can use an array of short codes like so:

$tables = ['users','test','another'];

and the query would look something like:

$code = 0;
DB::statement("select * from $tables[$code] where a=?",[2]);
DB::statement("create table $tables[$code]");