我很難理解為什么在嘗試將JSON檔案資料插入新表時會得到我得到的結果。問題是一個JSON檔案可以正常作業并填充表，而另一個JSON檔案則不能。我正在使用Xamppphpadmin，但我不知道為什么我的問題仍然存在。表的創建適用于任何JSON檔案，但資料的插入是主要問題。
php檔案：
include("dbCon.php");
$fname=$_POST['fname'];
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$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);
echo var_dump($fname);
foreach($array as $row) {
$sql = "INSERT INTO `".$fname."`(title, imgurl, content) VALUES('".$row["title"]."', '".$row["imgurl"]."', '".$row["content"]."')";
mysqli_query($conn, $sql);
}
echo var_dump($array);
$conn->close();
JSON 檔案： test.json
[
{
"title":"test1",
"imgurl":"test1",
"content":"test1"
},
{
"title":"test2",
"imgurl":"test2",
"content":"test2"
},
{
"title":"test3",
"imgurl":"test3",
"content":"test3"
}
]
Json 檔案： newmainnews.json
[
{
"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."
},
{
"title":"Mars on the cheap: Scientists working to revolutionize access to the Red Planet",
"imgurl":"mars.jpg",
"content":"Spotting Jupiter is a breeze this week for the naked eye as it reaches its biggest and brightest moment in the night sky. Telescope-hunters will also get a treat looking for moons and atmospheric bands. The gas giant planet will be at opposition today (Aug. 19), meaning it is directly opposite the sun in Earth's sky. Jupiter also makes its closest approach of the year to Earth during opposition. The planet will appear at magnitude -2.9, well within naked-eye range and outshining any star in Earth's sky except, of course, for the sun."
},
{
"title":"Jupiter's winds of change show increased storm speeds in Great Red Spot",
"imgurl":"jupiter.jpg",
"content":"The long-running telescope has been studying the Great Red Spot — a major storm on Jupiter — that is shrinking for mysterious reasons. Alongside that, researchers just uncovered huge changes in wind speeds within the massive storm.Jupiter takes 12 Earth years to orbit the sun. During the Jovian year between 2009 and 2020."
}
]
var_dumptest.json的回聲：
array(3) { [0]=> array(3) { ["title"]=> string(5) "test1" ["imgurl"]=> string(5) "test1" ["content"]=> string(5) "test1" } [1]=> array(3) { ["title"]=> string(5) "test2" ["imgurl"]=> string(5) "test2" ["content"]=> string(5) "test2" } [2]=> array(3) { ["title"]=> string(5) "test3" ["imgurl"]=> string(5) "test3" ["content"]=> string(5) "test3" } }
The var_dump echo for newmainnews.json:
array(3) { [0]=> array(3) { ["title"]=> string(74) "NASA's record-breaking Lucy asteroid mission gearing up for October launch" ["imgurl"]=> string(10) "record.jpg" ["content"]=> string(130) "Lucy is scheduled to launch atop a United Launch Alliance Atlas V rocket from Florida's Cape Canaveral Space Force Station on Oct." } [1]=> array(3) { ["title"]=> string(79) "Mars on the cheap: Scientists working to revolutionize access to the Red Planet" ["imgurl"]=> string(8) "mars.jpg" ["content"]=> string(539) "Spotting Jupiter is a breeze this week for the naked eye as it reaches its biggest and brightest moment in the night sky. Telescope-hunters will also get a treat looking for moons and atmospheric bands. The gas giant planet will be at opposition today (Aug. 19), meaning it is directly opposite the sun in Earth's sky. Jupiter also makes its closest approach of the year to Earth during opposition. The planet will appear at magnitude -2.9, well within naked-eye range and outshining any star in Earth's sky except, of course, for the sun." } [2]=> array(3) { ["title"]=> string(71) "Jupiter's winds of change show increased storm speeds in Great Red Spot" ["imgurl"]=> string(11) "jupiter.jpg" ["content"]=> string(327) "The long-running telescope has been studying the Great Red Spot — a major storm on Jupiter — that is shrinking for mysterious reasons. Alongside that, researchers just uncovered huge changes in wind speeds within the massive storm.Jupiter takes 12 Earth years to orbit the sun. During the Jovian year between 2009 and 2020." } }
該test.json檔案正確填充了表格，但newmainnews.json沒有插入任何內容。
我懷疑JSON檔案有問題。無論哪種方式，就像我之前所說的那樣，我完全一無所知，任何澄清或幫助將不勝感激。
uj5u.com熱心網友回復：
這段代碼很容易受到SQL 注入的影響，我相信這實際上是導致您出現問題的原因。
您的示例檔案包含將單引號 ( ') 用作撇號的字串。因為您使用基本的字串連接來構建 SQL 查詢，所以當這些單引號成為查詢的一部分時，您將生成無效的 SQL。
例如，讓我們采用第一項，但為了便于閱讀示例，我將縮短它：
{
"title":"NASA's Lucy asteroid mission",
"imgurl":"record.jpg",
"content":"Lucy is scheduled to launch."
}
然后嘗試使用以下代碼創建 SQL 查詢：
$sql = "INSERT INTO `".$fname."`(title, imgurl, content) VALUES('".$row["title"]."', '".$row["imgurl"]."', '".$row["content"]."')";
結果查詢將如下所示：
INSERT INTO `newmainnews`(title, imgurl, content) VALUES ('NASA's Lucy asteroid mission', 'record.jpg', 'Lucy is scheduled to launch.')
現在，查看該VALUES部分中的第一項。StackOverflow 的語法高亮實際上在這里有所幫助。由于 中的單引號NASA's，您正在創建無效的 SQL，因為第一個值本質上變成了后面的字串 ，MySQL 會將其解釋為無效的 SQL，因為單引號關閉了 title 字串的開頭。"NASA"s Lucy asteroid mission',
如果您不熟悉 SQL 注入，這是最簡單的情況之一，有人可以注入包含引號的字串來關閉輸入字串，然后他們可以注入任意惡意 SQL 代碼。例如，如果您的一篇文章標題更改為：
{
"title":"NASA','',''); DROP TABLE `newmainnews`; --",
"imgurl": "",
"content": ""
}
我沒有測驗它，但這應該會導致您的表被洗掉。
對此的解決方案是熟悉轉義輸入并確保您不會盲目地獲取輸入字串并將它們直接放入 SQL 中。我強烈建議您嘗試改用PDO 準備好的陳述句。如果您堅持使用mysqli，我認為您也可以使用準備好的陳述句，但我對此知之甚少。
自己做一些額外的研究，在谷歌上搜索“準備好的陳述句”和“如何防止 SQL 注入”等主題。
uj5u.com熱心網友回復：
對于任何想知道如何避免注入404 Not Found 的人。
foreach($array as $row) {
$stmt = $conn->prepare("INSERT INTO `".$fname."`(title, imgurl, content) VALUES (?,?,?)");
$stmt->bind_param("sss", $title, $imgurl, $content);
$title = $row["title"];
$imgurl = $row["imgurl"];
$content = $row["content"];
$stmt->execute();
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/346081.html
下一篇：從查詢中獲取價值