php - 当我单击按钮时，我试图根据电子邮件过滤器运行两个语句

Question

这是我得到的错误

SQLSTATE[42000]：语法错误或访问冲突：1064 您的 SQL 语法有错误；检查与您的 MariaDB 服务器版本相对应的手册，以了解在“[e-mail]”附近使用的正确语法。GROUP BY email, date' at line 2 (SQL: SELECT email, date, min(time) AS checkin, max(time) AS checkout,( (TIME_TO_SEC(TIMEDIFF(max(time), min(time))) / 60 ) / 60) 差异↵ FROM profile WHERE '. 1 = 1 和电子邮件，如'[e-mail]'。' GROUP BY email, date)"

我正在尝试根据提供的电子邮件和单击按钮来过滤数据。第一个查询运行良好，但是当我尝试在第二个查询中使用相同的 where 条件时出现错误。

$post = $request->all();
$email = $request->input('email');
$cond = ' 1=1 ';
if(!empty($post['email'])){
    $cond .= " and email like '".$post['email']."'";
}
$qry = 'SELECT User_id, email, status, date, time FROM profile WHERE '.$cond.' ';
$data = DB::select($qry);

$sql=" SELECT email, date, min(time) AS checkedin, max(time) AS checkedout,( (TIME_TO_SEC(TIMEDIFF(max(time), min(time))) / 60) / 60) difference
    FROM profile WHERE '.$cond.' GROUP BY email, date";
    $previousdata = DB::select($sql);

标签： phpsqllaravel

Answer 1

您使用了错误的字符串连接$sql：

$sql=" SELECT email, date, min(time) AS checkedin, max(time) AS checkedout,( (TIME_TO_SEC(TIMEDIFF(max(time), min(time))) / 60) / 60) difference
    FROM profile WHERE " . $cond . " GROUP BY email, date";

使用原始 SQL，您的查询将容易受到 SQL 注入的攻击。阅读有关此问题的更多信息。

---

从技术上讲，您可以对这两个语句使用 Laravel查询构建器。

$conditions = [];

if ($email) {
    $conditions[] = ['email', 'like', $email];
}

$profile = DB::table('profile')
    ->select('user_id', 'email', 'status', 'date', 'time')
    ->where($conditions)
    ->get();

$previousData = DB::table('profile')
    ->select('email', 'date', DB:raw('min(time) checkedin'), DB:raw('max(time) checkedout'), DB::raw('((TIME_TO_SEC(TIMEDIFF(max(time), min(time))) / 60) / 60) difference'))
    ->where($conditions)
    ->groupBy('email', 'date')
    ->get();