“mysql_fetch_assoc()"更改mysql字段中的数据时出错 [英] "mysql_fetch_assoc()" error when data in mysql field is changed

问题描述

我有一个 mySQL 数据库，我通过 PHP 从中获取一些数据.

这就是我所拥有的:

if ($db_found) {$URL_ID = $_GET["a"];$SQL = "SELECT * FROM tb_employees WHERE URL_ID = $URL_ID";$result = mysql_query($SQL);而 ($db_field = mysql_fetch_assoc($result)) {$firstname = $db_field['firstname'];$surname = $db_field['surname'];$function = $db_field['function'];$email = $db_field['email'];$telnr = $db_field['telnr'];}mysql_close($db_handle);}别的 {print "未找到数据库...请稍后再试.";mysql_close($db_handle);}

我的 mySQL 数据库中的 URL_ID 字段在本例中为 001.当我访问 www.mydomain.com/index.php?a=001 时，它会获取所有数据，并将其放入一个变量中，然后我就可以回显变量没有任何问题.

现在，我想更改 URL_ID，并且在 mySQL 数据库中将其更改为62ac1175".但是，当我继续访问 www.mydomain.com/index.php?a=62ac1175 时，我收到以下错误消息:

<块引用>

警告:mysql_fetch_assoc() 期望参数 1 是资源，给出的布尔值mydomain.com\db_connect.php 第 17 行

mySQL 中的字段的类型为 varchar(8)，排序规则为 utf8_general_ci.

如果我将条目改回 001 并将我的 URL 更改为 ?a=001，它又可以正常工作了.

怎么了?

解决方案

您没有在查询中进行任何错误检查，因此如果查询失败，它就会中断也就不足为奇了.mysql_query() 手册或在这个参考问题.>

示例:

$result = mysql_query($SQL);如果(！$结果){ trigger_error("mySQL 错误:".mysql_error());死();}

您的查询正在中断，因为您没有将输入括在引号中.您可以避免*仅对整数使用引号(62ac1175 不是).试试

$SQL = "SELECT * FROM tb_employees WHERE URL_ID = '$URL_ID'";

此外，您展示的代码容易受到SQL 注入.使用您的库的正确清理方法(例如 mysql_real_escape_string() 用于您正在使用的经典 mysql 库)，或切换到 PDO 和准备好的语句.

在您的代码中，这看起来像这样:而不是

$URL_ID = $_GET["a"];

做

$URL_ID = mysql_real_escape_string($_GET["a"]);

_{* 但是，如果您避免使用引号，mysql_real_escape_string() 将不起作用，您需要手动检查参数是否实际上是整数.}

I have a mySQL database from where I fetch some data via PHP.

This is what I've got:

if ($db_found) {

    $URL_ID = $_GET["a"];

    $SQL = "SELECT * FROM tb_employees WHERE URL_ID = $URL_ID";
    $result = mysql_query($SQL);

    while ($db_field = mysql_fetch_assoc($result)) {
        $firstname = $db_field['firstname'];
        $surname = $db_field['surname'];
        $function = $db_field['function'];
        $email = $db_field['email'];
        $telnr = $db_field['telnr'];
    }

    mysql_close($db_handle);
}
else {
    print "Database not found... please try again later.";
    mysql_close($db_handle);
}

The URL_ID field in my mySQL database is, for this example, 001. When I go to www.mydomain.com/index.php?a=001 it fetches all the data, puts it into a variable, and I can echo the variables without any problem.

Now, I want to change the URL_ID, and I've changed it to "62ac1175" in the mySQL database. However, when I proceed to www.mydomain.com/index.php?a=62ac1175, I get this error message:

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in mydomain.com\db_connect.php on line 17

The field in mySQL has varchar(8) as type and utf8_general_ci as collation.

If I change the entry back to 001 and change my URL to ?a=001, it works fine again.

What's going wrong?

解决方案

You are not doing any error checking in your query, so it's no wonder it breaks if the query fails. How to add proper error checking is outlined in the manual on mysql_query() or in this reference question.

Example:

$result = mysql_query($SQL);

if (!$result)
 { trigger_error("mySQL error: ".mysql_error());
   die(); }

your query is breaking because you aren't wrapping the input in quotes. You can avoid* quotes only for integers (which 62ac1175 is not). Try

$SQL = "SELECT * FROM tb_employees WHERE URL_ID = '$URL_ID'";

Also, the code you show is vulnerable to SQL injection. Use the proper sanitation method of your library (like mysql_real_escape_string() for the classic mysql library that you are using), or switch to PDO and prepared statements.

In your code, this would look like so: Instead of

$URL_ID = $_GET["a"];

do

$URL_ID = mysql_real_escape_string($_GET["a"]);

_{* however, if you avoid quotes, mysql_real_escape_string() won't work and you need to check manually whether the parameter actually is an integer.}

这篇关于“mysql_fetch_assoc()"更改mysql字段中的数据时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！