当名称包含撇号时,用于将通过表单提交的名称与 MySQL 数据库相匹配的脚本会中断
我有一个脚本,使用 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";
}
嗯!我想我可能已经发现了这个问题。问题可能不在于查询,而在于 PHP 代码。下面我将尝试使用您的示例 John O'Shea 进行解释。
在上面的代码之后,数组 $not_in 将包含转义键,因为 $name 已经包含使用 mysql_real_escape_string() 转义的值。因此,例如:
$not_in[John] = true;
$not_in[约翰·奥谢] = true;
现在,上面代码中的 $dbname 包含从数据库检索的未转义值,例如不带反斜杠的 John O'Shea。由于这不是 $not_in 包含的内容,因此 unset() 将不起作用。这意味着所有撇号值都保留在 $not_in 数组中。
因此解决方法是将未转义的值保留在 $not_in 中。
希望这是有道理的!
========== 编辑:响应如何在 $not_in 中保留未转义的值:
这个想法是在需要的地方进行转义。以下是您可以对代码进行的更改:
重写第一个 for() 如下:
并将 UPDATE 语句重写为:
顺便说一句,根据您的代码,如果有一个名称不存在,则 UPDATE 将不会运行存在于数据库中。仅当在数据库中找到所有 $_POST['name'] 时才绝对有必要运行 UPDATE 吗?如果没有,您可以显着减少代码量。
我尚未测试上述更改,但我认为它们应该有效。如果您遇到任何问题,请告诉我。
========== 编辑 2:用于更新现有记录并为不存在的记录生成错误的代码片段
嘿尼克,我认为仅编写以下代码就可以解决问题:
希望这会有所帮助!
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.
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;
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:
And rewrite the UPDATE statement as:
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:
Hope this helps!