問題描述

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.

這篇關于MySQLi:查詢 VS 準備的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！