在原始 sql 查询 Laravel 中插入变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37618764/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Inserting a variable in a raw sql query Laravel
提问by Arminius
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.
然后我需要在 WHERE 语句中嵌入该变量（因此，它的值）。如果我对值进行硬编码，它会带来正确的结果，但我已经尝试了各种方法来插入该变量，但没有成功。好吧，假设我设法使用该变量，那么我将不得不研究绑定以避免 sql 注入，但到目前为止，我想说的是，看看该变量是否可以在查询中使用。
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
我试过，双引号，串联。$vx 。, 花括号 {$x}，像这个 $variable 这样的普通变量，但是在某些情况下会出现语法错误（连接），或者如果我只是像这样在 author = $x 的地方嵌入变量，它告诉我它可以找不到名为 $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'
));
回答by gview
This appears to be a simple PHP variable interpolation issue.
这似乎是一个简单的 PHP 变量插值问题。
DB::raw()wants literally rawSQL. So there are a couple of issues that need to be fixed in the SQL string you are passing.
DB::raw()想要真正的原始SQL。因此，您传递的 SQL 字符串中有几个问题需要解决。
- 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.
- PHP 变量插值（将变量注入字符串）只有在字符串周围使用双引号时才会发生。用单引号它变成一个字符串常量。
- 如果 Author 是
char/varchar，则 SQL 语法需要在原始 SQL 语句中的字符串周围加上引号。查询构建器通常会为您处理这些问题，但您正在解决这些问题。
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.
与所有插值一样，如果被插值的变量来自用户输入，则这会让您面临 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:
DB::select()有一个选项，允许您传递参数数组，这些参数本质上是安全的，不会受到 SQL 注入的影响。在这种情况下，解决方案是：
$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)
);
回答by roli roli
Regarding this tutorial
关于本教程
$results = DB::select( DB::raw("SELECT * FROM some_table WHERE some_col = :somevariable"), array(
'somevariable' => $someVariable,
));
回答by Dung HDinh
This is one example for you to insert variable in a raw sql laravel
这是您在原始 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();
