我有点困惑如何在这使用准备语句。我尝试了,但是失败了,因为我的查询是分开的。
$q = "SELECT * FROM report WHERE 1";
if(isset($_GET['name']) && !empty($_GET['name'])){
$name = $_GET['name'];
$q .= " AND user ='$name'";
}
if(isset($_GET['category']) && !empty($_GET['category'])){
$category = $_GET['category'];
$q .= " AND category ='$category'";
}
$q .= " ORDER BY report.id desc LIMIT $offset, $no_of_records_per_page";
$all = $conn->query($q);
我试过这个,但是它搞砸了,不工作。
$q = $conn->prepare("SELECT * FROM report WHERE 1");
if(isset($_GET['name']) && !empty($_GET['name'])){
$name = $_GET['name'];
$q .= " AND user = ?";
}
if(isset($_GET['category']) && !empty($_GET['category'])){
$category = $_GET['category'];
$q .= " AND category = ?";
}
$q .= " ORDER BY report.id desc LIMIT $offset, $no_of_records_per_page";
$q->bind_param('ss', $name, $category);
$q->execute();
$all = $q->get_result();
下面是我如何使用预处理语句(使用PDO)编写它的代码:
$terms = [];
$params = [];
if($user = $_GET['name'] ?? null) {
$terms[] = "user = ?";
$params[] = $user;
}
if($category = $_GET['category'] ?? null) {
$terms[] = "category = ?";
$params[] = $category;
}
$where = '';
if ($terms) {
$where = "WHERE " . implode(" AND ", $terms);
}
$q = "SELECT * FROM report $where
ORDER BY report.id desc
LIMIT $no_of_records_per_page OFFSET $offset";
$stmt = $conn->prepare($q);
$stmt->execute($params);
我假设$offset
和$no_of_records_per_page
不是来自不安全的来源。如果在代码中使用字面量设置它们,则直接在查询中使用它们是安全的。
你的评论:
PHP的[]
操作符只能向数组中添加一个元素。如果你添加的是一个数组,它将成为一个嵌套数组。
可以用两种方法将多个元素添加到数组中。一次一个:
$params[] = $from_date;
$params[] = $to_date;
或者使用内置数组函数,就像下面两种解决方案:
$params = array_push($params, $from_date, $to_date);
$params = array_merge($params, [$from_date, $to_date]);
或者在PHP 7.4及更高版本中,可以这样组合数组:
$params = [...$params, $from_date, $to_date];