Menu
I would simply like to run such query:
select * from `users` where SUBSTRING_INDEX(`email`, '@' ,-1) not in ('gmail.com, outlook.com');
Two ways crossed my mind which non of them work:
$providers = array('gmail.com', 'outlook.com');
$providers = "'" . implode("', '", $providers) . "'";
User::whereRaw("SUBSTRING_INDEX(`email`, '@' ,-1) not in (?)", $providers);
the above would not work because PDO will escape the "'" characters.
User::whereIn(DB::raw("SUBSTRING_INDEX(`email`, '@' ,-1)", $providers);
this one simply does not work. any idea?
322
DB::raw() is used to make arbitrary SQL commands which aren't parsed any further by the query builder. They therefore can create a vector for attack via SQL injection.
$someVariable = Input::get("some_variable"); $results = DB::select( DB::raw("SELECT * FROM some_table WHERE some_col = :somevariable"), array( 'somevariable' => $someVariable, )); Voìla! Safe queries! Lastly, if you are performing queries which don't return data, then using a SELECT query will result in errors.
Laravel provide wherein() to use sql wherein query. in wherein() we just need to pass two argument one is column name and another if array of ids or anything that you want. You can see bellow syntax on wherein query in laravel: whereIn(Coulumn_name, Array);
Raw SQL, sometimes also called native SQL, is the most basic, most low-level form of database interaction. You tell the database what to do in the language of the database. Most developers should know basics of SQL. This means how to CREATE tables and views, how to SELECT and JOIN data, how to UPDATE and DELETE data.
Here's a safer way to do it:
$providers = ['gmail.com', 'outlook.com'];
$placeholder = implode(', ', array_fill(0, count($providers), '?'));
User::whereRaw("SUBSTRING_INDEX(`email`, '@' ,-1) not in ($placeholder)", $providers);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
