MySQLi:查询 VS 准备

2021-12-25 00:00:00 fetch php mysqli data-binding

mysqli 中的prepare 和query 有一点我不太明白.

There is something I don't quite understand it at all which is prepare and query in mysqli.

这个是使用mysqli::query来处理查询的，众所周知它缺乏安全性:

This one is using mysqli::query to process the query and it has been known of lacking security:

public function fetch_assoc($query)
    {
        $result = parent::query($query);
        //$result = self::preparedStatement($query);
        if($result) 
        {
            return $result->fetch_assoc();
        } 
        else
        {
            # call the get_error function
            return self::get_error();
            # or:
            # return $this->get_error();
        }
    }

这是我认为具有更好安全性的准备绑定执行的那个，

this is the one with prepare-bind-execute which has a better security I assume,

public function fetch_assoc_stmt($sql,$types = null,$params = null)
    {
        # create a prepared statement
        $stmt = parent::prepare($sql);

        # bind parameters for markers
        # but this is not dynamic enough...
        //$stmt->bind_param("s", $parameter);

        if($types&&$params)
        {
            $bind_names[] = $types;
            for ($i=0; $i<count($params);$i++) 
            {
                $bind_name = 'bind' . $i;
                $$bind_name = $params[$i];
                $bind_names[] = &$$bind_name;
            }
            $return = call_user_func_array(array($stmt,'bind_param'),$bind_names);
        }

        # execute query 
        $stmt->execute();

        # these lines of code below return one dimentional array, similar to mysqli::fetch_assoc()
        $meta = $stmt->result_metadata(); 

        while ($field = $meta->fetch_field()) { 
            $var = $field->name; 
            $$var = null; 
            $parameters[$field->name] = &$$var; 
        }

        call_user_func_array(array($stmt, 'bind_result'), $parameters); 

        while($stmt->fetch()) 
        { 
            return $parameters;  
        }

        # close statement
        $stmt->close();
    }

然而，这两种方法都返回相同的结果，

However, both of these two methods return the same result, 

$mysqli = new database(DB_HOST,DB_USER,DB_PASS,DB_NAME);

$sql = "
SELECT *
FROM root_contacts_cfm
ORDER BY cnt_id DESC
";
print_r($mysqli->fetch_assoc_stmt($sql));

print_r($mysqli->fetch_assoc($sql));

他们打印:

Array
(
    [cnt_id] => 2
    [cnt_email1] => lau@xx.net
    [cnt_email2] => 
    [cnt_fullname] => Lau T
    [cnt_firstname] => Thiam
    [cnt_lastname] => Lau
    [cnt_organisation] => 
    [cnt_website] => 
    [cnt_biography] => 
    [cnt_gender] => 
    [cnt_birthday] => 
    [cnt_address] => 
    [cnt_postcode] => 
    [cnt_telephone] => 
    [cnt_note] => 
    [cnt_key] => 
    [cat_id] => 
    [tcc_id] => 
    [cnt_suspended] => 0
    [cnt_created] => 2011-02-04 00:00:00
    [cnt_updated] => 2011-02-04 13:54:36
)
Array
(
    [cnt_id] => 2
    [cnt_email1] => lau@xx.net
    [cnt_email2] => 
    [cnt_fullname] => Lau T
    [cnt_firstname] => Thiam
    [cnt_lastname] => Lau
    [cnt_organisation] => 
    [cnt_website] => 
    [cnt_biography] => 
    [cnt_gender] => 
    [cnt_birthday] => 
    [cnt_address] => 
    [cnt_postcode] => 
    [cnt_telephone] => 
    [cnt_note] => 
    [cnt_key] => 
    [cat_id] => 
    [tcc_id] => 
    [cnt_suspended] => 0
    [cnt_created] => 2011-02-04 00:00:00
    [cnt_updated] => 2011-02-04 13:54:36
)

您应该已经注意到，在fetch_assoc_stmt 的方法中，我根本没有使用fetch_assoc.可能根本没有机会使用它，因为 prepare 使用不同的方式返回结果.

You should have noticed that inside the method of fetch_assoc_stmt I don't use fetch_assoc at all. Probably no chance at all in using it as prepare uses a different way to return result.

那么，我的问题是，既然使用prepare 比query 好，为什么要存在fetch_assoc?我们不应该忘记它还是不应该 php.net 弃用它?fetch_all 也是一样的 - 为什么我们首先要拥有它！??

So, my question is since using prepare is better than query, why should fetch_assoc exist at all? Shouldn't we just forget about it or shouldn't php.net have it deprecated?? It is the same for fetch_all - why should we have it in the first place!??

谢谢.

推荐答案

当您使用参数动态生成查询时，准备好的语句比普通 SQL 查询更可取.在您的示例中，您的 SQL 不包含任何变量，因此使用普通查询或准备好的语句在功能上是等效的.

Prepared statements are preferable to plain SQL queries when you are using parameters to dynamically generate the query. In you example, your SQL contains no variables, so using a plain query or prepared statement are functionally equivalent.

当你必须改变参数的值时，例如在 WHERE 子句中，准备好的语句会给你增加安全性:

When you must change the values of parameters, in the WHERE clause, for example, then prepared statements will give you added security:

...
WHERE col1 = ? AND col2 = ?

但是当您的查询简单且固定时，使用 $mysqli->query($sql) 和 fetch_assoc() 可能需要较少的代码.使用直接查询而不是准备好的语句并不是普遍的坏习惯，正如您可能相信的那样.当您的查询需要参数化时，或者当同一个查询必须重复编译和执行时，您将从准备好的语句中受益.

But when your query is simple and fixed, it may require less code to use $mysqli->query($sql) along with fetch_assoc(). Using direct queries rather than prepared statements is not a universally bad practice, as some might have you believe. When your query requires parameterization, or when the same query must be compiled and executed repeatedly, then you'll benefit from the prepared statement.

相关文章