我正在从事一个需要将数据数组（安全）插入MySQL数据库的项目。我的代码当前使用 foreach 构造循环访问 XML 文档，解析我选择从中提取的所有数据。
这是代码...
foreach ($xml->town as $towns) {
# -> player information
$player_id = mysqli_real_escape_string($dbconnect,$towns->player->playername['id']);
$player_name = mysqli_real_escape_string($dbconnect,$towns->player->playername);
# -> town data information
$town_name = mysqli_real_escape_string($dbconnect,$towns->towndata->townname);
$town_id = mysqli_real_escape_string($dbconnect,$towns->towndata->townname['id']);
$founded_on = mysqli_real_escape_string($dbconnect,$towns->towndata->foundeddatetime);
$population = mysqli_real_escape_string($dbconnect,$towns->towndata->population);
$capital = mysqli_real_escape_string($dbconnect,$towns->towndata->iscapitalcity);
# -> alliance info
$alliance_cap = mysqli_real_escape_string($dbconnect,$towns->towndata->isalliancecapitalcity);
# -> location information
$map_x = mysqli_real_escape_string($dbconnect,$towns->location->mapx);
$map_y = mysqli_real_escape_string($dbconnect,$towns->location->mapy);
# -> terrain information
$terrain_type_id = mysqli_real_escape_string($dbconnect,$towns->location->terraintype['id']);
$terrain_type = mysqli_real_escape_string($dbconnect,$towns->location->terraintype);
$terrain_overall_id = mysqli_real_escape_string($dbconnect,$towns->location->terrainoveralltype['id']);
$terrain_overall_type = mysqli_real_escape_string($dbconnect,$towns->location->terrainoveralltype);
在我最近问的一个关于XML解析的问题中，有人评论说我没有正确使用mysqli_real_escape_string。他提到我应该首先提取数据，然后再调用mysqli_real_escape_string来准备数据库插入操作。
MySQL 查询
我写了一个简单的MySQL查询，它有效;尽管由于某种原因，它只插入数组的第一行，而不是整个数据数组。
mysqli_query($dbconnect,
"INSERT INTO towndata (player_id, playername, town_name, town_id, founded_on, population, capital, alliance_cap, map_x, map_y, terrain_type_id, terrain_type, terrain_overall_id, terrain_overall_type)
VALUES ('$player_id', '$player_name', '$town_name', '$town_id', '$founded_on', '$population', '$capital', '$alliance_cap', '$map_x', '$map_y', '$terrain_type_id', '$terrain_type', '$terrain_overall_id', '$terrain_overall_type')"
); # end mysql statement
} # end of _foreach_ loop
现在对于问题/问题部分...
为什么只插入第一行而不是整个数据数组？
如上所述，
INSERT INTO语句有效。但是，我似乎无法弄清楚为什么我的代码只插入第一行。应用程序的其他部分使用相同的代码，但没有表现出这种行为，这使我对原因完全一无所知。使用
mysqli_real_escape_string准备数据数组（和字符串）以进行安全数据库插入的正确/有效方法是什么？我意识到这可能是一个偏好问题;但我不禁想知道是否有我不熟悉的模式或范式。
提前感谢您的时间、帮助和建议。
$mysqli = new mysqli("localhost", "my_user", "my_password", "xxxx");
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s'n", mysqli_connect_error());
exit();
}
foreach ($xml->town as $towns) {
# -> player information
$player_id = $towns->player->playername['id'];
$player_name = $towns->player->playername;
# -> town data information
$town_name = $towns->towndata->townname;
$town_id = $towns->towndata->townname['id'];
$founded_on = $towns->towndata->foundeddatetime;
$population = $towns->towndata->population;
$capital = $towns->towndata->iscapitalcity;
# -> alliance info
$alliance_cap = $towns->towndata->isalliancecapitalcity;
# -> location information
$map_x = $towns->location->mapx;
$map_y = $towns->location->mapy;
# -> terrain information
$terrain_type_id = $towns->location->terraintype['id'];
$terrain_type = $towns->location->terraintype;
$terrain_overall_id = $towns->location->terrainoveralltype['id'];
$terrain_overall_type = $towns->location->terrainoveralltype;
/* create a prepared statement */
if ($stmt = $mysqli->prepare("INSERT INTO `towndata` (`player_id`, `playername`, `town_name`, `town_id`, `founded_on`, `population`, `capital`, `alliance_cap`, `map_x`, `map_y`, `terrain_type_id`, `terrain_type`, `terrain_overall_id`, `terrain_overall_type`) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)")) {
/* bind parameters for markers */
$stmt->bind_param('ssssssssssssss', $player_id, $playername, $town_name, $town_id,$founded_on, $population, $capital, $alliance_cap,$map_x, $map_y, $terrain_type_id, $terrain_type,$terrain_overall_id, $terrain_overall_type);
/* execute query */
$stmt->execute();
}
}
哪里
- i 对应的变量类型为整数
- d 对应变量的类型为双精度
- s 对应的变量具有类型字符串
- b 对应的变量是一个 blob，将以数据包的形式发送
因此，在上面的代码中，如果并非所有变量都是字符串，则可以更改参数绑定中的"SSSSSSSSSSSSSSSS"部分。
您的插入似乎仅适用于循环的第一个循环。我建议您打印出每个周期的插入字符串，而不执行它，然后从MySQL客户端或phpMyAdmin测试插入语句。您可能会遇到主键冲突（您可以通过这种方式检查）或其他错误，具体取决于第一行之后的数据。
与其使用mysqli_real_escape_string，我建议您查看MySQLi准备的语句，因为它们将为您提供编写INSERT语句的正确方法，避免SQL注入问题。
准备好的发言稿