Inserting a Variable in a Raw SQL Query Laravel

Inserting a variable in a raw sql query Laravel

This appears to be a simple PHP variable interpolation issue.

DB::raw() wants literally raw SQL. So there are a couple of issues that need to be fixed in the SQL string you are passing.

1. PHP Variable interpolation (injecting variables into a string) only happens if you use double quotes around the string. With single quotes it becomes a string constant.
2. If Author is a char/varchar, then SQL syntax requires quotes around the string in your raw SQL statement. Query builders typically take care of these issues for you, but you are going around them.

So the "fixed" version of this would be:

$x = "whatever";
$results = DB::select(DB::raw("SELECT 
                       t.id, t.AvgStyle, r.RateDesc
                   FROM (
                       SELECT
                           p.id, ROUND(AVG(s.Value)) AS AvgStyle
                       FROM posts p

                       INNER JOIN styles s
                           ON s.post_id = p.id
                       WHERE author = '$x'    
                       GROUP BY p.id
                   ) t
                   INNER JOIN rates r
                       ON r.digit = t.AvgStyle"
                   ));

Like all interpolation, this opens you up to the possibility of SQL injection if the variable being interpolated comes from user input. From the original question it is unclear whether this is a problem.

DB::select() has an option that allows you to pass an array of parameters that is inherently safe from SQL injection. In that case the solution would be:

$x = "whatever";
$results = DB::select(DB::raw("SELECT 
                       t.id, t.AvgStyle, r.RateDesc
                   FROM (
                       SELECT
                           p.id, ROUND(AVG(s.Value)) AS AvgStyle
                       FROM posts p

                       INNER JOIN styles s
                           ON s.post_id = p.id
                       WHERE author = :author
                       GROUP BY p.id
                   ) t
                   INNER JOIN rates r
                       ON r.digit = t.AvgStyle"
                   ),
                       array('author' => $x)
                   );

Trying to include Variables inside a Laravel Raw Query

Reference: https://laravel.com/docs/5.2/upgrade#upgrade-5.2.0

The lists method on the Collection, query builder and Eloquent query builder objects has been renamed to pluck. The method signature remains the same.

Now that being said you would replace lists to pluck function(search for Retrieving A List Of Column Values in this page)

How to pass multiple variables in closure function: You can use use keyword and a list of options like below.

->select(DB::raw(function($query) use ($a, $b, $c) {
    // use $a, $b, $c
 })

But in this case you can omit this closure by just directly putting raw text inside DB::raw(). So your query can be like this using $stateRegistered and $state variables.

$products = DB::table('Chemical')
          ->join('ChemicalTarget', 'Chemical.ChemicalID', '=', 'ChemicalTarget.ChemicalID')
          ->select(DB::raw("CONCAT(Product, ' ', CASE WHEN '$stateRegistered' != 'Y' THEN '(not $state)' ELSE '' END) as label"))
          ->orderBy('label', 'asc')
          ->groupBy('label')
          ->pluck('label'); 

Array variable inside DB::raw query laravel

Concatenate the string with the php variable

DB::raw("COUNT(CASE WHEN (status_change_log.status in ".$processingStatus.") THEN 1 END) AS processing")

How to insert the value into query?

Try This

$orders =  Order::with('customer','product')->select('orders.id', 'orders.customer_id', 'orderdets.product_id', DB::raw('SUM(orderdets.quantity) as sum'))->get();
dd($orders);

store result of raw DB in a variable in laravel

removing the DB:: table line and modifying the rest as follows solved the issue

DB::select(DB::raw("SELECT MIN(t1.ID + 1) AS nextID
                        FROM DB.tablename t1
                        LEFT JOIN DB.tablename t2
                         ON t1.ID + 1 = t2.ID
                         WHERE t2.ID IS NULL
                    ")
                )['0']->nextID;

How to bind parameters to a raw DB query in Laravel that's used on a model?

OK, after some experimenting, here's the solution that I came up with:

$property = 
    Property::select(
        DB::raw("title, lat, lng, ( 
            3959 * acos( 
                cos( radians(  ?  ) ) *
                cos( radians( lat ) ) * 
                cos( radians( lng ) - radians(?) ) + 
                sin( radians(  ?  ) ) *
                sin( radians( lat ) ) 
            )
       ) AS distance")
    )
    ->having("distance", "<", "?")
    ->orderBy("distance")
    ->take(20)
    ->setBindings([$lat, $lng, $lat,  $radius])
    ->get();

Basically, setBindings has to be called on the query. Wish this was documented!

---

Related Topics