使用空参数运行 PHP 搜索脚本会返回整个 MySQL 表

发布于 2024-12-10 10:42:38 字数 529 浏览 0 评论 0原文

当我通过 PHP 运行以下 MySQL 查询并且 $_GET() 的所有元素都是空字符串时，返回 volunteers 表中的所有记录（出于明显的原因）。

$first = $_GET['FirstName'];
$last = $_GET['LastName'];
$middle = $_GET['MI'];

$query = "SELECT * FROM volunteers WHERE 0=0";

if ($first){
    $query .= " AND first like '$first%'";
}

if ($middle){
    $query .= " AND mi like '$middle%'";
}

if ($last){
    $query .= " AND last like '$last%'";
}

$result = mysql_query($query);

允许将空参数发送到此脚本并导致返回空的 $result 的最优雅的方法是什么？

原文

When I run the following MySQL query via PHP and all of the elements of $_GET() are empty strings, all the records in the volunteers table are returned (for obvious reasons).

$first = $_GET['FirstName'];
$last = $_GET['LastName'];
$middle = $_GET['MI'];

$query = "SELECT * FROM volunteers WHERE 0=0";

if ($first){
    $query .= " AND first like '$first%'";
}

if ($middle){
    $query .= " AND mi like '$middle%'";
}

if ($last){
    $query .= " AND last like '$last%'";
}

$result = mysql_query($query);

What is the most elegant way of allowing empty parameters to be sent to this script with the result being that an empty $result is returned?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

︶￣淡然 2024-12-17 10:42:38

我的解决方案：

$input = Array(
    'FirstName' => 'first',
    'LastName'  => 'last',
    'MI'        => 'mi'
);

$where = Array();
foreach($input as $key => $column) {
    $value = trim(mysql_escape_string($_GET[$key]));
    if($value) $where[] = "`$column` like '$value%'";
}
if(count($where)) {
    $query = "SELECT * FROM volunteers WHERE ".join(" AND ", $where);
    $result = mysql_query($query);
}

my solution:

$input = Array(
    'FirstName' => 'first',
    'LastName'  => 'last',
    'MI'        => 'mi'
);

$where = Array();
foreach($input as $key => $column) {
    $value = trim(mysql_escape_string($_GET[$key]));
    if($value) $where[] = "`$column` like '$value%'";
}
if(count($where)) {
    $query = "SELECT * FROM volunteers WHERE ".join(" AND ", $where);
    $result = mysql_query($query);
}

回复收藏 0 原文

弱骨蛰伏 2024-12-17 10:42:38

如果查询没有什么可做的，那么运行（可能）昂贵的查询是没有意义的。因此，与其尝试提出替代查询来防止搜索无术语，不如在没有术语的情况下根本不运行搜索：

$where = '';
... add clauses ...
if ($where !== '') {
   $sql = "SELECT ... WHERE $where";
   ... do query ...
} else {
   die("You didn't enter any search terms");
}

There's no point in running a (potentially) expensive query if there's nothing for that query to do. So instead of trying to come up with an alternate query to prevent no-terms being searched, just don't run the search at all if there's no terms:

$where = '';
... add clauses ...
if ($where !== '') {
   $sql = "SELECT ... WHERE $where";
   ... do query ...
} else {
   die("You didn't enter any search terms");
}

回复收藏 0 原文