JSON数据导入数据库：解决PHP中JSON文件加载与SQL注入的隐患

[
        {
            "title":"test1", 
            "imgurl":"test1",
            "content":"test1"
        }
]

[
        {
            "title":"NASA's record-breaking Lucy asteroid mission...", 
            "imgurl":"record.jpg",
            "content":"Lucy is scheduled to launch..."
        }
]

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

foreach($array as $row) {
   $sql = "INSERT INTO `".$fname."`(title, imgurl, content) VALUES('".$row["title"]."', '".$row["imgurl"]."', '".$row["content"]."')";
   mysqli_query($conn, $sql);
}
// ...

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

$fname = $_POST['fname'];

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

// 创建表的SQL，这里同样建议使用预处理语句来处理表名，以防万一
// 但通常表名不会是用户输入，此处暂不修改，保持原样
$sql_create_table = "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_create_table) === TRUE) {
   echo "Table ".$fname." created successfully<br>";
} else {
   echo "Error creating table: " . $conn->error . "<br>";
}

$json_file_path = '../jsonFIle/'.$fname.'.json';
if (!file_exists($json_file_path)) {
    die("Error: JSON file not found at " . $json_file_path);
}

$json = file_get_contents($json_file_path);
$array = json_decode($json, true);

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

if (!is_array($array) || empty($array)) {
    echo "No data to insert or JSON is empty.<br>";
    $conn->close();
    exit();
}

// 准备插入语句
$stmt = $conn->prepare("INSERT INTO `".$fname."`(title, imgurl, content) VALUES(?, ?, ?)");

if ($stmt === false) {
    die("Prepare failed: " . $conn->error);
}

// 绑定参数：'sss' 表示三个参数都是字符串类型
$stmt->bind_param("sss", $title, $imgurl, $content);

$inserted_count = 0;
foreach($array as $row) {
    // 为每个循环迭代设置变量值
    $title = $row["title"];
    $imgurl = $row["imgurl"];
    $content = $row["content"];

    if ($stmt->execute()) {
        $inserted_count++;
    } else {
        echo "Error inserting row: " . $stmt->error . " for title: " . htmlspecialchars($title) . "<br>";
    }
}

$stmt->close(); // 关闭预处理语句
echo "Successfully inserted " . $inserted_count . " rows into table " . $fname . ".<br>";

$conn->close(); // 关闭数据库连接
?>

<?php
// dbCon.php 可能包含类似以下代码来建立PDO连接
/*
$host = 'localhost';
$db    = 'your_database_name';
$user = 'your_username';
$pass = 'your_password';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION, // 错误模式为抛出异常
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,     // 默认获取关联数组
    PDO::ATTR_EMULATE_PREPARES   => false,                // 禁用模拟预处理，使用原生预处理
];
try {
     $pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
     die("Connection failed: " . $e->getMessage());
}
*/
// 假设 $pdo 变量已在 dbCon.php 或其他地方建立

$fname = $_POST['fname'];

// 创建表的SQL (PDO方式)
// 注意：表名本身不应作为绑定参数，因为PDO只绑定值，不绑定标识符。
// 因此，表名仍需拼接，但应确保$fname的来源是安全的，或进行严格的白名单验证。
$sql_create_table = "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
)";

try {
    $pdo->exec($sql_create_table);
    echo "Table ".$fname." created successfully<br>";
} catch (\PDOException $e) {
    echo "Error creating table: " . $e->getMessage() . "<br>";
    // 根据错误类型决定是否终止，例如表已存在可以忽略
}

$json_file_path = '../jsonFIle/'.$fname.'.json';
if (!file_exists($json_file_path)) {
    die("Error: JSON file not found at " . $json_file_path);
}

$json = file_get_contents($json_file_path);
$array = json_decode($json, true);

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

if (!is_array($array) || empty($array)) {
    echo "No data to insert or JSON is empty.<br>";
    exit();
}

// 准备插入语句 (PDO方式)
$stmt = $pdo->prepare("INSERT INTO `".$fname."`(title, imgurl, content) VALUES(:title, :imgurl, :content)");

$inserted_count = 0;
foreach($array as $row) {
    try {
        $stmt->execute([
            ':title' => $row["title"],
            ':imgurl' => $row["imgurl"],
            ':content' => $row["content"]
        ]);
        $inserted_count++;
    } catch (\PDOException $e) {
        echo "Error inserting row: " . $e->getMessage() . " for title: " . htmlspecialchars($row["title"]) . "<br>";
    }
}

echo "Successfully inserted " . $inserted_count . " rows into table " . $fname . ".<br>";
// PDO连接在脚本结束时会自动关闭，或手动设置为null
$pdo = null; 
?>