如何在php中创建安全的mysql准备语句? [英] How to create a secure mysql prepared statement in php?
问题描述
我是第一次使用php在mysql中使用预备语句.我需要一些帮助来创建准备好的语句来检索列.
I am new to using prepared statements in mysql with php. I need some help creating a prepared statement to retrieve columns.
我需要从不同的列中获取信息.当前,对于测试文件,我使用完全不安全 SQL语句:
I need to get information from different columns. Currently for a test file, I use the completely unsecure SQL statement:
$qry = "SELECT * FROM mytable where userid='{$_GET['userid']}' AND category='{$_GET['category']}'ORDER BY id DESC"
$result = mysql_query($qry) or die(mysql_error());
有人可以帮助我使用已准备好的url参数(如上)的输入来创建安全 mysql语句吗?
Can someone help me create a secure mysql statement using input from url parameters (as above) that is prepared?
奖金:预先准备好的语句也可以提高速度.如果我仅在页面上使用三到四次准备好的语句,会提高整体速度吗?
BONUS: Prepared statements are suppose to increase speed as well. Will it increase overall speed if I only use a prepared statement three or four times on a page?
推荐答案
下面是使用mysqli的示例(对象语法-如果需要,可以很容易地转换为函数语法):
Here's an example using mysqli (object-syntax - fairly easy to translate to function syntax if you desire):
$db = new mysqli("host","user","pw","database");
$stmt = $db->prepare("SELECT * FROM mytable where userid=? AND category=? ORDER BY id DESC");
$stmt->bind_param('ii', intval($_GET['userid']), intval($_GET['category']));
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($column1, $column2, $column3);
while($stmt->fetch())
{
echo "col1=$column1, col2=$column2, col3=$column3 \n";
}
$stmt->close();
此外,如果您想要一种简单的方法来获取关联数组(供SELECT *使用),而不必确切指定要绑定的变量,这是一个方便的功能:
Also, if you want an easy way to grab associative arrays (for use with SELECT *) instead of having to specify exactly what variables to bind to, here's a handy function:
function stmt_bind_assoc (&$stmt, &$out) {
$data = mysqli_stmt_result_metadata($stmt);
$fields = array();
$out = array();
$fields[0] = $stmt;
$count = 1;
while($field = mysqli_fetch_field($data)) {
$fields[$count] = &$out[$field->name];
$count++;
}
call_user_func_array(mysqli_stmt_bind_result, $fields);
}
要使用它,只需调用它而不是调用bind_result:
To use it, just invoke it instead of calling bind_result:
$stmt->store_result();
$resultrow = array();
stmt_bind_assoc($stmt, $resultrow);
while($stmt->fetch())
{
print_r($resultrow);
}
这篇关于如何在php中创建安全的mysql准备语句?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!