Having Inconsistent Results When Loading Json File to a Table

Question

I am having a hard time understanding why I am getting the results that I am getting when trying to insert a JSON file data into a new table. The issue is that one JSON file will work fine and populate the table while the other JSON file won't. I am using Xampp phpadmin, and I am clueless as to why my problem persists. The creation of the table works for any JSON file, but the insertion of the data is the main problem.

php File:

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 File: test.json

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

Json File: 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."
        }
]

The var_dump echo for test.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." } }

The test.json file populates the table correctly, but the newmainnews.json doesn't insert anything.

I suspect something is up with the JSON file. Either way, I am, like I previously said, completely clueless, and any clarification or help would be appreciated.

Answer 1

This code is very vulnerable to SQL injection, and I believe that's actually what is causing your problem.

Your sample file contains strings which have single-quotes (') used as apostrophes. Because you're using basic string concatenation for constructing your SQL queries, you're producing invalid SQL as those single-quotes become part of the query.

For example, let's take the first item, but I'm going to shorten it just for ease of reading the example:

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

You then try to create a SQL query using this code:

$sql = "INSERT INTO `".$fname."`(title, imgurl, content) VALUES('".$row["title"]."', '".$row["imgurl"]."', '".$row["content"]."')";

The resulting query will look like this:

INSERT INTO `newmainnews`(title, imgurl, content) VALUES ('NASA's Lucy asteroid mission', 'record.jpg', 'Lucy is scheduled to launch.')

Now, look at the first item in the VALUES section. StackOverflow's syntax highlighting actually helps here. Because of the single quote in NASA's, you're creating invalid SQL because the first value essentially becomes the string "NASA" followed by s Lucy asteroid mission', which MySQL is going to interpret as invalid SQL because the single quote closed the start of the string for the title.

If you aren't familiar with SQL injection, this is one of the most trivial cases, where someone can inject a string containing quotes which closes the input string, and then they can inject arbitrary malicious SQL code. An example of this would be if one of your article titles was changed to this:

{
   "title":"NASA','',''); DROP TABLE `newmainnews`; --",
   "imgurl": "",
   "content": ""
}

I didn't test it, but that should result in your table being deleted.

The solution to this is to familiarize yourself with escaping input and making sure you do not just blindly take input strings and place them directly in SQL. I would highly recommend you try to instead use PDO prepared statements. If you are stuck using mysqli, I think you can also use prepared statements with that, but I know less about that.

Do some additional research yourself, googling topics like "prepared statements" and "how to prevent SQL injection".

Answer 2

For anyone wondering to how you could avoid injections as 404 Not Found mentioned.

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();