PHP PDO MySQL count()准备语句。

I'm on a Web development course. Working with PHP PDO MySQL they teach us on a workshop to do this:

我在上网络开发课程。与PHP PDO MySQL合作，他们在一个研讨会上教我们:

function countUsers($search) {
    $and = '';
    if ($search != '') {
        $and = " AND user_name LIKE '%".$search."%'";    
    }
    $total = $this->db->query("SELECT COUNT(id) as rows FROM users WHERE valid = 1" . $and)->fetch(PDO::FETCH_OBJ);
    return $total->rows;
}

From my point of view this is totally wrong, the statement is not prepared and is passed directly from user input without any validation that can lead to SQL Injection, so I proposed this to the trainer (I know fetchColumn() would be more appropriate here but let's stick with this for the sake of the example):

在我看来,这是完全错误的声明并不准备和通过直接从用户输入没有任何可以导致SQL注入的验证,所以我提出了这个教练(我知道fetchColumn()可能更合适,但我们坚持这个为了例子):

function countUsers($search) {
    $and = '';
    $sqlSearch = "%$search%";

    if ($search != '') {
        $and = " AND user_name LIKE :username";  
    }

    $sql = "SELECT COUNT(id) as rows FROM users WHERE valid = 1" . $and;
    $sth = $this->db->prepare($sql);
    if ($search != '') {
        $sth->bindParam(':username', $sqlSearch, PDO::PARAM_STR);
    }
    $sth->execute();
    $total = $sth->fetch(PDO::FETCH_OBJ);
    return $total->rows;
}

Am I wrong? Are they wrong or we both wrong/right?

我错了吗?是他们错了，还是我们都错了?

1 个解决方案

#1

Yes you are right.

是的,你是对的。

However, your code is not optimal. In fact, prepared statements are intended to make your code cleaner, not more bloated.

但是，您的代码不是最佳的。事实上，准备好的语句是为了使代码更简洁，而不是更臃肿。

function countUsers($search) {
    $sql = "SELECT COUNT(id) FROM users WHERE valid = 1 AND user_name LIKE ?";
    $sth = $this->db->prepare($sql);
    $sth->execute(["%$search%"]);
    return $sth->fetchColumn();
}

Part of the cleanup I did is a mere trick - as you can always search for LIKE '%%' and match all rows (excluding ones where user_name is null though).

我所做的部分清理工作只是一个小技巧——因为您总是可以搜索'% '并匹配所有的行(不包括user_name为空的行)。

But the rest is just a proper use of PDO features:

但剩下的只是对PDO特性的适当使用:

you can always use positional placeholders
您总是可以使用位置占位符
you can always avoid bindParam() call
您总是可以避免bindParam()调用
you should use appropriate fetch mode
您应该使用适当的获取模式。

#1