How to use raw sql query string that is created dynamically?-CodePudding

I've seen similar posts here, however they're for older Laravel versions, and also my query string is created dynamically.

I get an array of arrays that represent the values I need to use when I construct the sql query string. For example:

[
    ["a" => "a1", "b" => "b1", "c" => "c1"],
    ["a" => "a2", "b" => "b2", "c" => "c2", "d" => "d2"]
]

Then I need to create some complex query that's impossible to write with Laravel's query builder and uses the dynamic data from above:

SELECT
...
WHERE (a="a1", b="b1", c="c1")
OR WHERE (a="a2", b="b2", c="c2", d="d2")
...

From older posts I've seen here, it was mentioned I can use

$result = DB::select($query_string):

Or even with DB::statement.

But, I'm not sure it's still a good way in Laravel 8 and above because it's not in the docs.

But even if yes, it means I'll put the string as is without taking care of binding the values to prevent sql injection.

So how can I do it in the case?

CodePudding user response：

$sql = "SELECT * FROM "TABLE_NAME"; $results = DB::select(DB::raw($sql)); you need to try this way

CodePudding user response：

I would go with Closure and foreach, because you can use array with string keys to define where/orWhere conditions.

$conditions = [
   ["a" => "a1", "b" => "b1", "c" => "c1"],
   ["a" => "a2", "b" => "b2", "c" => "c2", "d" => "d2"]
];

DB::table('your_table')->where(function($query) use ($conditions) {
       foreach($conditions as $condition) {
           $query->orWhere($condition);
       }
   })->selectRaw($query_string)->get();

If you really need to go with raw SQL, just add the raw functions like orderByRaw(), groupByRaw, etc... https://laravel.com/api/9.x/Illuminate/Database/Query/Builder.html

And about sql injection, you need to validate your data. https://laravel.com/docs/9.x/validation