MySQLi：查询VS准备 [英] MySQLi: query VS prepare
问题描述
在mysqli中有一些我不太明白的是准备和查询。 / p>
这一个使用 mysqli :: query 来处理查询，已知缺乏安全性： （$ query）
{
$ result = parent :: query（$ query）;
// $ result = self :: preparedStatement（$ query）;
if（$ result）
{
return $ result-> fetch_assoc（）;
}
else
{
＃调用get_error函数
return self :: get_error（）;
＃或：
＃return $ this-> get_error（）;
}
}
这是一个具有prepare-bind-execute的有一个更好的安全性，我假设，
public function fetch_assoc_stmt（$ sql，$ types = null，$ params = null）
{
＃创建一个准备的语句
$ stmt = parent :: prepare（$ sql）;
＃绑定参数标记
＃但这不够动态...
// $ stmt-> bind_param（s，$ parameter）;
if（$ types&& $ params）
{
$ bind_names [] = $ types;
（$ 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）;
}
＃执行查询
$ stmt-> execute（）;
＃这些代码行返回一个数组，类似于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（）;
}
但是，这两种方法都返回相同的结果，
$ mysqli =新数据库（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
）
数组
（
[cnt_id] => 2
[cnt_email1] => lau@xx.net
[ cnt_email2] =>
[cnt_fullname] => Lau T
[cnt_firstname] => Thiam
[cnt_lastname] => u
[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 。可能没有机会使用它作为准备使用不同的方式来返回结果。
所以，我的问题因为使用准备比查询更好，为什么要 fetch_assoc 存在吗？我们不应该只是忘记它，或者不应该php.net已经弃用？对于 fetch_all 是一样的 - 为什么我们应该在第一个位置！??
谢谢。 / p>
当使用参数动态生成查询时，准备语句比普通SQL查询更为可取。在您的示例中，您的SQL不包含变量，因此使用纯粹的查询或准备语句在功能上是等价的。
当您必须更改参数的值时，在code> WHERE 子句，例如，那么准备好的语句会给你增加安全性：
...
WHERE col1 =？ AND col2 =？
但是，当您的查询简单而且修复时，可能需要较少的代码才能使用 $ mysqli-> query（$ sql）以及 fetch_assoc（）。使用直接查询而不是准备好的语句不是一个普遍的坏习惯，因为有些人可能相信。当您的查询需要参数化时，或者当必须重复编译和执行相同的查询时，您将从准备好的语句中受益。
There is something I don't quite understand it at all which is prepare and query in mysqli.
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));
they print this:
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
)
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.
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!??
Thanks.
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.
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 = ?
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准备的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
