在原始SQL查询Laravel中插入变量 [英] 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";

然后，我需要在WHERE语句中嵌入该变量(即它的值).如果我对值进行硬编码，它将带来正确的结果，但是我已经尝试了各种方法来插入该变量而没有成功.好吧，假设我设法使用了该变量，那么我将不得不研究绑定以避免SQL注入，但是到目前为止，我要说的是，看看该变量是否可以在查询中使用.

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.

我已经尝试过，双引号，串联. $ vx. ，花括号{$ x}，像$ variable这样的普通变量，但是在某些情况下(并置)会产生语法错误，或者如果我只是在author = $ x处嵌入这样的变量，它告诉我可以找不到名为$ x

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

推荐答案

这似乎是一个简单的PHP变量插值问题.

This appears to be a simple PHP variable interpolation issue.

DB::raw()确实需要 raw SQL.因此，您需要在传递的SQL字符串中解决几个问题.

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变量插值(将变量注入字符串)仅在字符串两端使用双引号的情况下发生.用单引号将其变为字符串常量.
2. 如果Author是char/varchar，则SQL语法要求在原始SQL语句中的字符串前后加上引号.查询生成器通常会为您解决这些问题，但是您可以解决它们.

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

与所有插值一样，如果要插值的变量来自用户输入，则可以打开SQL注入的可能性.从最初的问题来看，尚不清楚这是否是一个问题.

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()具有一个选项，该选项使您可以传递从SQL注入本质上安全的参数数组.在这种情况下，解决方案将是:

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

这篇关于在原始SQL查询Laravel中插入变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！