Arminius Arminius - 6 months ago 36
SQL Question

Inserting a variable in a raw sql query Laravel

I am inside a function in a controller.

So from the Form, I get a value for a variable, say:

$x = "whatever";


Then I need to embed that variable (so, its value), in the WHERE statement. If I hardcode the value, it brings a correct result, but I have tried in all ways to insert that variable without success. Well, supposing that I manage to use that variable, then I will have to look into binding to avoid sql injection, but so far, I would say, see if that variable can get used in the query.

I have tried, double quotes, concatenation . $vx . , curly braces {$x}, the variable plain like this $variable, but either gives syntax errors in some cases, (concatenation), or if I just embed the variable like this where author = $x, it tells me that it can't find the column named $x

$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'
));

Answer

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. Querybuilders 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"
                       ));
Comments