PHP处理JSON数据插入MySQL的最佳实践：避免SQL注入导致的错误

include("dbCon.php"); // 假设dbCon.php建立了$conn连接

$fname=$_POST['fname']; // 表名由POST请求传入，存在风险

if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

// 创建表的SQL语句，表名直接拼接，存在SQL注入风险
$sql = "CREATE TABLE `".$fname."`(
  id bigint(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  imgurl VARCHAR(255) NOT NULL,
  content VARCHAR(20000) NOT NULL
)";

if ($conn->query($sql) === TRUE) {
   echo "Table ".$fname." created successfully";
} else {
   echo "Error creating table: " . $conn->error;
}

$json = file_get_contents('../jsonFIle/'.$fname.'.json');
$array = json_decode($json, true);

// 核心问题所在：插入数据的SQL语句，值直接拼接，存在严重SQL注入风险
foreach($array as $row) {
 $sql = "INSERT INTO `".$fname."`(title, imgurl, content) VALUES('".$row["title"]."', '".$row["imgurl"]."', '".$row["content"]."')";
 mysqli_query($conn, $sql); // 使用mysqli_query执行未转义的SQL
}

$conn->close();

{
    "title":"NASA's record-breaking Lucy asteroid mission gearing up for October launch", 
    "imgurl":"record.jpg",
    "content":"Lucy is scheduled to launch atop a United Launch Alliance Atlas V rocket from Florida's Cape Canaveral Space Force Station on Oct."
}

VALUES ('NASA's record-breaking Lucy asteroid mission...', ...)

INSERT INTO `newmainnews`(title, imgurl, content) VALUES ('NASA','',''); DROP TABLE `newmainnews`; --', '', '')

<?php
include("dbCon.php"); // 假设dbCon.php建立了$conn连接

$fname = $_POST['fname']; // 仍然建议对$fname进行严格的白名单验证，防止表名注入

if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

// 1. 创建表的SQL语句，对表名进行严格验证是关键
// 建议：在实际应用中，表名不应直接由用户输入决定，而应通过白名单机制进行验证。
// 或者，如果表名必须动态生成，确保$fname只包含字母数字和下划线，并进行过滤。
if (!preg_match('/^[a-zA-Z0-9_]+$/', $fname)) {
    die("Invalid table name provided.");
}

$createTableSql = "CREATE TABLE `".$fname."`(
  id bigint(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  imgurl VARCHAR(255) NOT NULL,
  content VARCHAR(20000) NOT NULL
)";

if ($conn->query($createTableSql) === TRUE) {
   echo "Table ".$fname." created successfully<br>";
} else {
   echo "Error creating table: " . $conn->error . "<br>";
}

$jsonFilePath = '../jsonFIle/'.$fname.'.json';
if (!file_exists($jsonFilePath)) {
    die("JSON file not found: " . $jsonFilePath);
}
$json = file_get_contents($jsonFilePath);
$array = json_decode($json, true);

if (json_last_error() !== JSON_ERROR_NONE) {
    die("Error decoding JSON: " . json_last_error_msg());
}

if (!is_array($array)) {
    die("JSON content is not a valid array.");
}

// 2. 使用预处理语句插入数据
// 准备SQL语句，使用问号`?`作为参数占位符
$insertSql = "INSERT INTO `".$fname."`(title, imgurl, content) VALUES (?, ?, ?)";
$stmt = $conn->prepare($insertSql);

if ($stmt === false) {
    die("Error preparing statement: " . $conn->error);
}

// 绑定参数：'sss' 表示三个参数都是字符串类型
// $title, $imgurl, $content 是在循环内部赋值的变量
$stmt->bind_param("sss", $title, $imgurl, $content);

foreach($array as $row) {
    // 确保JSON数据结构与预期一致
    if (!isset($row["title"]) || !isset($row["imgurl"]) || !isset($row["content"])) {
        echo "Skipping malformed row: " . json_encode($row) . "<br>";
        continue;
    }

    // 将数据赋值给绑定变量
    $title = $row["title"];
    $imgurl = $row["imgurl"];
    $content = $row["content"];

    // 执行预处理语句
    if (!$stmt->execute()) {
        echo "Error inserting data for row " . json_encode($row) . ": " . $stmt->error . "<br>";
    } else {
        echo "Row inserted successfully: " . $title . "<br>";
    }
}

// 关闭预处理语句
$stmt->close();
$conn->close();
?>

// ... (代码上半部分与之前相同)

foreach($array as $row) {
    $title = mysqli_real_escape_string($conn, $row["title"]);
    $imgurl = mysqli_real_escape_string($conn, $row["imgurl"]);
    $content = mysqli_real_escape_string($conn, $row["content"]);

    $sql = "INSERT INTO `".$fname."`(title, imgurl, content) VALUES('".$title."', '".$imgurl."', '".$content."')";
    mysqli_query($conn, $sql);
}
// ...