当名称包含撇号时,用于将通过表单提交的名称与 MySQL 数据库相匹配的脚本会中断

发布于 12-13 06:54 字数 1624 浏览 2 评论 0原文

我有一个脚本,使用 mysql_real_escape_string 将名称输入 MySQL 数据库,以便正确处理撇号。我遇到的问题是下面的脚本检查使用另一种形式输入的名称是否与我的数据库中已有的名称相对应,如果找到名称则更新该行。

当我尝试在该脚本处理的表单中输入带有撇号的名称时,收到一条错误消息,指出未找到该名称,并且错误消息中的名称在撇号之前包含一个反斜杠,这显然是问题。

所以问题是,如何修改下面的脚本,以便它可以处理带撇号的名称?

谢谢,

尼克

$row_count = count($_POST['name']);
if ($row_count > 0) {

    mysql_select_db($database, $connection);
    $name = array();
    $workshop = array(); 
    $not_found = array();

    for($i = 0; $i < $row_count; $i++) {
        // variable sanitation...
        $name[$i] = mysql_real_escape_string(ucwords($_POST['name'][$i]));
        $workshop[$i] = mysql_real_escape_string($_POST['workshop'][$i]);
    }
    $names = "('".implode("','",$name)."')";

    $not_in = Array();

    // lets say all names doesn't exist in `conference`
    foreach($name as $value) {
        // names in array are keys, not values
        $not_in[$value] = true;
    }


    $query = mysql_query("SELECT Name FROM conference WHERE Name IN $names"); 
    while(list($dbname) = @mysql_fetch_row($query)) {
        // delete those name from $not_in who exists
        unset($not_in[$dbname]);
    }

    // names in $not_in array are keys, not values
    $not_in = array_keys($not_in);

    if(empty($not_in)) {
        // its ok, all names have been found. do the magic.
        for($i = 0; $i < $row_count; $i++) {
            $sql = "UPDATE conference SET Workshop = '$workshop[$i]' WHERE Name LIKE '$name[$i]'";
            mysql_query($sql);
            $body .= "Name: " . $name[$i] . "    Workshop: " . $workshop[$i] . "\n\n";
        }

I have script that enters names into a MySQL database, using mysql_real_escape_string so that apostrophes are handled correctly. The trouble I am experiencing is with the script below that checks to see if names entered using another form correspond to names already in my database and if names are found updates the row.

When I try to enter a name with an apostrophe into the form processed by this script, I get an error message stating that the name wasn't found, and the name in the error message contains a backslash before the apostrophe, which is obviously the issue.

So the question is, how can I amend the script below so that it will work with names with apostrophes?

Thanks,

Nick

$row_count = count($_POST['name']);
if ($row_count > 0) {

    mysql_select_db($database, $connection);
    $name = array();
    $workshop = array(); 
    $not_found = array();

    for($i = 0; $i < $row_count; $i++) {
        // variable sanitation...
        $name[$i] = mysql_real_escape_string(ucwords($_POST['name'][$i]));
        $workshop[$i] = mysql_real_escape_string($_POST['workshop'][$i]);
    }
    $names = "('".implode("','",$name)."')";

    $not_in = Array();

    // lets say all names doesn't exist in `conference`
    foreach($name as $value) {
        // names in array are keys, not values
        $not_in[$value] = true;
    }


    $query = mysql_query("SELECT Name FROM conference WHERE Name IN $names"); 
    while(list($dbname) = @mysql_fetch_row($query)) {
        // delete those name from $not_in who exists
        unset($not_in[$dbname]);
    }

    // names in $not_in array are keys, not values
    $not_in = array_keys($not_in);

    if(empty($not_in)) {
        // its ok, all names have been found. do the magic.
        for($i = 0; $i < $row_count; $i++) {
            $sql = "UPDATE conference SET Workshop = '$workshop[$i]' WHERE Name LIKE '$name[$i]'";
            mysql_query($sql);
            $body .= "Name: " . $name[$i] . "    Workshop: " . $workshop[$i] . "\n\n";
        }

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

分开我的手2024-12-20 06:54:50

嗯!我想我可能已经发现了这个问题。问题可能不在于查询,而在于 PHP 代码。下面我将尝试使用您的示例 John O'Shea 进行解释。

for($i = 0; $i < $row_count; $i++) {
    // variable sanitation...
    $name[$i] = mysql_real_escape_string(ucwords($_POST['name'][$i]));
    $workshop[$i] = mysql_real_escape_string($_POST['workshop'][$i]);
}
$names = "('".implode("','",$name)."')";

$not_in = Array();

// lets say all names doesn't exist in `conference`
foreach($name as $value) {
    // names in array are keys, not values
    $not_in[$value] = true;
}

在上面的代码之后,数组 $not_in 将包含转义键,因为 $name 已经包含使用 mysql_real_escape_string() 转义的值。因此,例如:

$not_in[John] = true;
$not_in[约翰·奥谢] = true;

$query = mysql_query("SELECT Name FROM conference WHERE Name IN $names"); 
while(list($dbname) = @mysql_fetch_row($query)) {
    // delete those name from $not_in who exists
    unset($not_in[$dbname]);
}

现在,上面代码中的 $dbname 包含从数据库检索的未转义值,例如不带反斜杠的 John O'Shea。由于这不是 $not_in 包含的内容,因此 unset() 将不起作用。这意味着所有撇号值都保留在 $not_in 数组中。

因此解决方法是将未转义的值保留在 $not_in 中。

希望这是有道理的!

========== 编辑:响应如何在 $not_in 中保留未转义的值:

这个想法是在需要的地方进行转义。以下是您可以对代码进行的更改:

重写第一个 for() 如下:

for($i = 0; $i < $row_count; $i++) {
    // variable sanitation...
    //$name[$i] = mysql_real_escape_string(ucwords($_POST['name'][$i]));
    $name[$i] = ucwords($_POST['name'][$i]);
    $workshop[$i] = mysql_real_escape_string($_POST['workshop'][$i]);
}
$names = "('" . mysql_real_escape_string(implode("','",$name)) . "')";

并将 UPDATE 语句重写为:

$sql = "UPDATE conference SET Workshop = '$workshop[$i]' WHERE Name LIKE '" . mysql_real_escape_string($name[$i]) . "'";

顺便说一句,根据您的代码,如果有一个名称不存在,则 UPDATE 将不会运行存在于数据库中。仅当在数据库中找到所有 $_POST['name'] 时才绝对有必要运行 UPDATE 吗?如果没有,您可以显着减少代码量。

我尚未测试上述更改,但我认为它们应该有效。如果您遇到任何问题,请告诉我。

========== 编辑 2:用于更新现有记录并为不存在的记录生成错误的代码片段

嘿尼克,我认为仅编写以下代码就可以解决问题:

$row_count = count($_POST['name']);
if ($row_count > 0) {
    mysql_select_db($database, $connection);
    for ($i = 0; $i < $row_count; $i++) {
        mysql_query("UPDATE conference SET Workshop = '" . mysql_real_escape_string($_POST['workshop'][$i]) . "' WHERE Name LIKE '" . mysql_real_escape_string($_POST['name'][$i]) . "'");
        $affectedRows = mysql_affected_rows();
        if ($affectedRows == 0) {
            echo '<br>Name did not exist - ' . $_POST['name'][$i];
        }
    }
}

希望这会有所帮助!

Hmmm! I think I might have found the issue. The problem might not be with the query but with the PHP code. I'll try to explain below using your example John O'Shea.

for($i = 0; $i < $row_count; $i++) {
    // variable sanitation...
    $name[$i] = mysql_real_escape_string(ucwords($_POST['name'][$i]));
    $workshop[$i] = mysql_real_escape_string($_POST['workshop'][$i]);
}
$names = "('".implode("','",$name)."')";

$not_in = Array();

// lets say all names doesn't exist in `conference`
foreach($name as $value) {
    // names in array are keys, not values
    $not_in[$value] = true;
}

After the above code, Array $not_in will contain escaped keys because $name already contains values escaped using mysql_real_escape_string(). Hence, for example:

$not_in[John] = true;
$not_in[John O\'Shea] = true;

$query = mysql_query("SELECT Name FROM conference WHERE Name IN $names"); 
while(list($dbname) = @mysql_fetch_row($query)) {
    // delete those name from $not_in who exists
    unset($not_in[$dbname]);
}

Now $dbname in the above code contains unescaped values retrieved from the DB, for example John O'Shea without the backslashes. Since this is not what $not_in contains, the unset() will not work. This means that all apostrophe values remain in the $not_in array.

So the fix is to keep unescaped values in $not_in.

Hope this makes sense!

========== EDIT: In response to how to keep unescaped values in $not_in:

The idea is to do escaping just where it is needed. Here are the changes that you may do to your code:

Rewrite the first for() as below:

for($i = 0; $i < $row_count; $i++) {
    // variable sanitation...
    //$name[$i] = mysql_real_escape_string(ucwords($_POST['name'][$i]));
    $name[$i] = ucwords($_POST['name'][$i]);
    $workshop[$i] = mysql_real_escape_string($_POST['workshop'][$i]);
}
$names = "('" . mysql_real_escape_string(implode("','",$name)) . "')";

And rewrite the UPDATE statement as:

$sql = "UPDATE conference SET Workshop = '$workshop[$i]' WHERE Name LIKE '" . mysql_real_escape_string($name[$i]) . "'";

By the way, According to your code, the UPDATE will not run if there is one name that does not exist in the database. Is it absolutely necessary to run the UPDATE only if all the $_POST['name'] are found in the database? If not, you can significantly reduce the amount of code.

I haven't tested the above changes but I think they should work. Let me know if you get any issues.

========== EDIT 2: Code snippet for updating records that exist and generating errors for records that did not

Hey Nick, I think only writing the below code should do the trick:

$row_count = count($_POST['name']);
if ($row_count > 0) {
    mysql_select_db($database, $connection);
    for ($i = 0; $i < $row_count; $i++) {
        mysql_query("UPDATE conference SET Workshop = '" . mysql_real_escape_string($_POST['workshop'][$i]) . "' WHERE Name LIKE '" . mysql_real_escape_string($_POST['name'][$i]) . "'");
        $affectedRows = mysql_affected_rows();
        if ($affectedRows == 0) {
            echo '<br>Name did not exist - ' . $_POST['name'][$i];
        }
    }
}

Hope this helps!

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文