Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Laravel whereRaw with parameters doesn't work?

Tags:

laravel-4

According to the documentation, this should work:

return $query->whereRaw("lang =  '?'",array(App::getLocale()));

But it doesn't while this works:

return $query->whereRaw("lang =  '".App::getLocale()."'");

What am I doing wrong? Here is the documentation

$users = User::whereRaw('age > ? and votes = 100', array(25))->get();
like image 818
Elia Weiss Avatar asked Dec 30 '13 10:12

Elia Weiss


1 Answers

There is no need to put quotes around the "?" placeholders. Just try:

return $query->whereRaw("lang = ?",array(App::getLocale()));

From PHP documentation about PDO (which should be valid also here):

For those wondering why adding quotes to around a placeholder is wrong, and why you can't use placeholders for table or column names: There is a common misconception about how the placeholders in prepared statements work: they are not simply substituted in as (escaped) strings, and the resulting SQL executed. Instead, a DBMS asked to "prepare" a statement comes up with a complete query plan for how it would execute that query, including which tables and indexes it would use, which will be the same regardless of how you fill in the placeholders.

The plan for "SELECT name FROM my_table WHERE id = :value" will be the same whatever you substitute for ":value", but the seemingly similar "SELECT name FROM :table WHERE id = :value" cannot be planned, because the DBMS has no idea what table you're actually going to select from.

Even when using "emulated prepares", PDO cannot let you use placeholders anywhere, because it would have to work out what you meant: does "Select :foo From some_table" mean ":foo" is going to be a column reference, or a literal string?

When your query is using a dynamic column reference, you should be explicitly white-listing the columns you know to exist on the table, e.g. using a switch statement with an exception thrown in the default: clause.


Edit: Please be careful as this answer is very old and can be outdated. Please confirm with Laravel's documentation.

like image 115
Cranio Avatar answered Oct 09 '22 08:10

Cranio