如何在 php 中创建安全的 mysql 准备语句？

Question

提问by chris

I am new to using prepared statements in mysql with php. I need some help creating a prepared statement to retrieve columns.

我是在 mysql 和 php 中使用准备好的语句的新手。我需要一些帮助来创建一个准备好的语句来检索列。

I need to get information from different columns. Currently for a test file, I use the completely unsecureSQL statement:

我需要从不同的列中获取信息。目前对于测试文件，我使用了完全不安全的SQL 语句：

$qry = "SELECT * FROM mytable where userid='{$_GET['userid']}' AND category='{$_GET['category']}'ORDER BY id DESC"
$result = mysql_query($qry) or die(mysql_error());

Can someone help me create a securemysql statement using input from url parameters (as above) that is prepared?

有人可以帮助我使用来自准备好的 url 参数（如上）的输入创建安全的mysql 语句吗？

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?

奖励：准备好的语句也可以提高速度。如果我在一个页面上只使用准备好的语句三到四次，它会提高整体速度吗？

Answer 1

回答by Amber

Here's an example using mysqli (object-syntax - fairly easy to translate to function syntax if you desire):

这是一个使用 mysqli 的示例（对象语法 - 如果您愿意，可以很容易地转换为函数语法）：

$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();

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:

此外，如果您想要一种简单的方法来获取关联数组（与 SELECT * 一起使用），而不必确切地指定要绑定到的变量，这里有一个方便的函数：

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);
}

To use it, just invoke it instead of calling bind_result:

要使用它，只需调用它而不是调用 bind_result：

$stmt->store_result();

$resultrow = array();
stmt_bind_assoc($stmt, $resultrow);

while($stmt->fetch())
{
    print_r($resultrow);
}

Answer 2

回答by Vinko Vrsalovic

You can write this instead:

你可以这样写：

$qry = "SELECT * FROM mytable where userid='";
$qry.= mysql_real_escape_string($_GET['userid'])."' AND category='";
$qry.= mysql_real_escape_string($_GET['category'])."' ORDER BY id DESC";

But to use prepared statements you better use a generic library, like PDO

但是要使用准备好的语句，您最好使用通用库，例如PDO

<?php
/* Execute a prepared statement by passing an array of values */
$sth = $dbh->prepare('SELECT * FROM mytable where userid=? and category=? 
                      order by id DESC');
$sth->execute(array($_GET['userid'],$_GET['category']));
//Consider a while and $sth->fetch() to fetch rows one by one
$allRows = $sth->fetchAll(); 
?>

Or, using mysqli

或者，使用mysqli

<?php
$link = mysqli_connect("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$category = $_GET['category'];
$userid = $_GET['userid'];

/* create a prepared statement */
if ($stmt = mysqli_prepare($link, 'SELECT col1, col2 FROM mytable where 
                      userid=? and category=? order by id DESC')) {
    /* bind parameters for markers */
    /* Assumes userid is integer and category is string */
    mysqli_stmt_bind_param($stmt, "is", $userid, $category);  
    /* execute query */
    mysqli_stmt_execute($stmt);
    /* bind result variables */
    mysqli_stmt_bind_result($stmt, $col1, $col2);
    /* fetch value */
    mysqli_stmt_fetch($stmt);
    /* Alternative, use a while:
    while (mysqli_stmt_fetch($stmt)) {
        // use $col1 and $col2 
    }
    */
    /* use $col1 and $col2 */
    echo "COL1: $col1 COL2: $col2\n";
    /* close statement */
    mysqli_stmt_close($stmt);
}

/* close connection */
mysqli_close($link);
?>

Answer 3

回答by Bill Karwin

I agree with several other answers:

我同意其他几个答案：

PHP's ext/mysqlhas no support for parameterized SQL statements.
Query parameters are considered more reliable in protecting against SQL injection issues.
mysql_real_escape_string()can also be effective if you use it correctly, but it's more verbose to code.
In some versions, international character sets have cases of characters that are not escaped properly, leaving subtle vulnerabilities. Using query parameters avoids these cases.

PHPext/mysql不支持参数化 SQL 语句。
查询参数被认为在防止 SQL 注入问题方面更可靠。
mysql_real_escape_string()如果您正确使用它，它也可以有效，但它的代码更加冗长。
在某些版本中，国际字符集存在字符未正确转义的情况，从而留下微妙的漏洞。使用查询参数可以避免这些情况。

You should also note that you still have to be cautious about SQL injection even if you use query parameters, because parameters only take the place of literal values in SQL queries. If you build SQL queries dynamically and use PHP variables for the table name, column name, or any other part of SQL syntax, neither query parameters nor mysql_real_escape_string()help in this case. For example:

您还应该注意，即使您使用查询参数，您仍然必须谨慎对待 SQL 注入，因为参数仅代替 SQL 查询中的字面值。如果您动态构建 SQL 查询并使用 PHP 变量作为表名、列名或 SQL 语法的任何其他部分，则mysql_real_escape_string()在这种情况下既没有查询参数也没有帮助。例如：

$query = "SELECT * FROM $the_table ORDER BY $some_column";

Regarding performance:

关于性能：

The performance benefit comes when you execute a prepared query multiple times with different parameter values. You avoid the overhead of parsing and preparing the query. But how often do you need to execute the same SQL query many times in the same PHP request?
Even when you can take advantage of this performance benefit, it is usually only a slight improvement compared to many other things you could do to address performance, like using opcode caching or data caching effectively.
There are even some cases where a prepared query harmsperformance. For example in the following case, the optimizer can't assume it can use an index for the search, because it must assume the parameter value mightbegin with a wildcard:
```
SELECT * FROM mytable WHERE textfield LIKE ?
```

当您使用不同的参数值多次执行准备好的查询时，性能优势就会出现。您可以避免解析和准备查询的开销。但是，您需要在同一个 PHP 请求中多次执行同一个 SQL 查询的频率如何？
即使您可以利用这种性能优势，与您可以为解决性能问题所做的许多其他事情（例如有效地使用操作码缓存或数据缓存）相比，通常也只是略有改进。
甚至在某些情况下，准备好的查询会损害性能。例如，在以下情况下，优化器不能假设它可以使用索引进行搜索，因为它必须假设参数值可能以通配符开头：
```
SELECT * FROM mytable WHERE textfield LIKE ?
```

Answer 4

回答by James Skidmore

Security with MySQL in PHP (or any other language for that matter) is a largely discussed issue. Here are a few places for you to pick up some great tips:

PHP（或任何其他语言）中 MySQL 的安全性是一个主要讨论的问题。这里有几个地方供您获取一些重要提示：

The two most major items in my opinion are:

我认为最重要的两个项目是：

SQL Injection:Be sure to escape all of your query variables with PHP's mysql_real_escape_string()function (or something similar).
Input Validation:Never trust the user's input. See thisfor a tutorial on how to properly sanitize and validation your inputs.

SQL 注入：确保使用 PHP 的mysql_real_escape_string()函数（或类似的东西）转义所有查询变量。
输入验证：永远不要相信用户的输入。有关如何正确清理和验证输入的教程，请参阅此内容。

Answer 5

回答by Cruachan

If you're going to use mysqli - which seems the best solution to me - I highly recommend downloading a copy of the codesense_mysqliclass.

如果您打算使用 mysqli - 这对我来说似乎是最好的解决方案 - 我强烈建议下载codeense_mysqli类的副本。

It's a neat little class that wraps up and hides most of the cruft that accumulates when using raw mysqli such that using prepared statements only takes a line or two extra over the old mysql/php interface

这是一个整洁的小类，它包含并隐藏了使用原始 mysqli 时积累的大部分杂物，这样使用准备好的语句只需要在旧的 mysql/php 接口上多出一两行

Answer 6

回答by Adera

Quite late, but this might help someone:

很晚了，但这可能会帮助某人：

/**
* Execute query method. Executes a user defined query
*
* @param string $query the query statement
* @param array(Indexed) $col_vars the column variables to replace parameters. The count value should equal the number of supplied parameters
*
* Note: Use parameters in the query then supply the respective replacement variables in the second method parameter. e.g. 'SELECT COUNT(*) as count FROM foo WHERE bar = ?'
*
* @return array
*/
function read_sql($query, $col_vars=null) {
    $conn = new mysqli('hostname', 'username', 'user_pass', 'dbname');
    $types = $variables = array();
    if (isset($col_vars)) {
        for ($x=0; $x<count($col_vars); $x++) {
            switch (gettype($col_vars[$x])) {
                case 'integer':
                    array_push($types, 'i');
                        break;
                case 'double':
                    array_push($types, 'd');
                    break;
                default:
                    array_push($types, 's');
            }
            array_push($variables, $col_vars[$x]);
        }
        $types = implode('', $types);
        $sql = $conn->prepare($query);
        $sql -> bind_param($types, ...$variables);
        $sql -> execute();
        $results = $sql -> get_result();
        $sql -> close();
    }else {
        $results = $conn->query($query) or die('Error: '.$conn->error);
    }
    if ($results -> num_rows > 0) {
        while ($row = $results->fetch_assoc()) {
            $result[] = $row;
        }
        return $result;
    }else {
        return null;
    }
}

You can then invoke the function like so:

然后，您可以像这样调用该函数：

read_sql('SELECT * FROM mytable where userid = ? AND category = ? ORDER BY id DESC', array($_GET['userid'], $_GET['category']));

如何在 php 中创建安全的 mysql 准备语句？

提问by chris

回答by Amber

回答by Vinko Vrsalovic

回答by Bill Karwin

回答by James Skidmore

回答by Cruachan

回答by Adera

相关推荐

最近更新

标签

如何在 php 中创建安全的 mysql 准备语句？

提问by chris

回答by Amber

回答by Vinko Vrsalovic

回答by Bill Karwin

回答by James Skidmore

回答by Cruachan

回答by Adera

相关推荐

如何在 PHP 中使用 SSL 证书发送 SOAP 请求？

从 PHP 中具有动态类名的类中获取静态属性

mysqldump 通过 PHP

php PHP中静态属性的魔术__get getter

相关推荐

最近更新

标签