Inserting a variable in a raw sql query Laravel

Question

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

So let's start with the simple: php doesn't do variable interpolation unless you use double quotes. Let's start with that. Change your sql statement to use "SELECT ..." and please report whatever behavior is occurring. — gview
– gview, Commented Jun 3, 2016 at 15:59
The 2nd obvious thing I see --- is author a char/varchar value? If so your sql syntax has to be WHERE author = '$x' . The purpose of the RAW method is to inject raw sql, so it's not going to do any magic for you. You have to construct a string that you could copy into a command line tool and have it run. — gview
– gview, Commented Jun 3, 2016 at 16:02
You solved it! Please, put it as an answer to get your credit. You dont have those over 6000 points intellect for nothing. — Arminius
– Arminius, Commented Jun 3, 2016 at 16:06
I am more grateful but if we dont put it as an answer, everybody is going to keep coming to check it as it will remain like unsolved. — Arminius
– Arminius, Commented Jun 3, 2016 at 16:30

David Harkness · Accepted Answer · 2019-07-02 16:22:24Z

21

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.

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.
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)
                   );

edited Jul 2, 2019 at 16:22

David Harkness

36.6k11 gold badges119 silver badges136 bronze badges

answered Jun 3, 2016 at 16:33

gview

15.5k3 gold badges53 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

halfer Over a year ago

Side note: there is no parameter binding going on here, nor escaping, so readers using this code should ensure that $x does not come from user input.

David Harkness Over a year ago

@halfer From my testing, the parameter is made safe as I was able to use where name = :name with ['name' => "o'brien"]. If it didn't escape that apostrophe, the query would have failed.

gview Over a year ago

@halfer: Your comment is pertinent to the non-parameter passing version, which I personally don't advocate nor would I use. Parameterized queries is the way to go if for no other reason than you don't need escaping, which also innoculates you from SQL injection.

Fernando Torres Over a year ago

This method doesn't work if you have more than one value within select method

gview Over a year ago

I don't know what you mean -- "more than one value in select method".

|

halfer · Accepted Answer · 2019-07-02 16:57:46Z

7

Regarding this tutorial

$results = DB::select( DB::raw("SELECT * FROM some_table WHERE some_col = :somevariable"), array(
   'somevariable' => $someVariable,
 ));

edited Jul 2, 2019 at 16:57

halfer

20.2k20 gold badges110 silver badges207 bronze badges

answered Mar 2, 2018 at 11:41

Roland

28.1k9 gold badges108 silver badges103 bronze badges

1 Comment

Fernando Torres Over a year ago

This method doesn't work if you have more than one value within select method

CocDap · Accepted Answer · 2020-01-09 07:48:34Z

-1

This is one example for you to insert variable in a raw sql laravel

        $query_result = Event::select(
            DB::raw('(CASE WHEN status = "draft" THEN "draft" 
            WHEN events.end_time <= \''.$now.'\' THEN "closed"
            ELSE "available"
            END) AS status'))
            ->orderBy('status')
            ->get();

answered Jan 9, 2020 at 7:48

CocDap

2501 silver badge5 bronze badges

1 Comment

D. Lawrence Over a year ago

Please explain your solution instead of showing only code.

Collectives™ on Stack Overflow

Inserting a variable in a raw sql query Laravel

3 Answers 3

8 Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related