Cant insert specific row into the database PHP MySQL JSON

Question

I pulled the json into the php object and started putting data from that object into the database. I have 20 users in json and everyone succeded to go into the database except one which surname is O'Carolan. I think that error is in that single quote, smth about that. I read everything about sql injection and tried everything i found here on stackoverflow with the similar errors and still doesnt work. I tried with the PDO also and prepared statements and still doesnt work. Here I always get an error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 'male')' at line 2. Also, when printing out the users from json it prints them properly and everything is ok there, just that 3rd user O'carolan wont go into to database. My json is at http://dev.30hills.com/data.json and my code is:

<?php

$servername = "localhost";
$username = "root";
$password = "";
$db = "30hills";

// Create connection
$conn = new mysqli($servername, $username, $password, $db);

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

$json_string = 'http://dev.30hills.com/data.json';
$jsondata = file_get_contents($json_string);
$obj = json_decode($jsondata, false);

$elementCount  = count($obj);

for ($x = 0; $x < $elementCount; $x++) {

    $id = $obj[$x]->id;
    $firstname = $obj[$x]->firstName;       
    $surname = $obj[$x]->surname;

    //if (preg_match('/'.$special_chars.'/', $surname)){
    //  $surname = str_replace("'","",$surname);
    //}

    $age = $obj[$x]->age;
    $gender = $obj[$x]->gender;

    echo $id;
    echo " ";
    echo $firstname;
    echo " ";
    echo $surname;
    echo "<br>";


    mysqli_query($conn, "INSERT INTO user (`id`, `firstName`, `surname`, `age`, `gender`) 
                VALUES($id, '$firstname', '" . $surname . "', $age, '$gender')") 
                or die(mysqli_error($conn));

?>

yeah but how? i tried everything, i also make "O'Carolan" string to be "OCarolan" and it still wont insert him into the db @SougataBose — Veljko Markovic, May 10 '17 at 10:52
Double quote it "O''Carolan", you can check following answer also http://stackoverflow.com/questions/1912095/how-to-insert-a-value-that-contains-an-apostrophe-single-quote — MNJ, May 10 '17 at 11:05
I tried that also and it wont work. When it prints out, it prints it good, but wont insert that row @MilosNikolik — Veljko Markovic, May 10 '17 at 11:08
You could use PDO prepared statements, that way you can insert data containing the quotes and they won't "break" the SQL statement — Florian Humblot, May 10 '17 at 11:22
bro, i've done that also, read the question, i wrote that i tried PDO and mysqli_real_escape_string, nothing works @FMashiro — Veljko Markovic, May 10 '17 at 11:27
@VeljkoMarkovic I have read the question, and I am willing to bet that you could get it working with PDO as this error just isn't possible if you do it correctly. — Florian Humblot, May 10 '17 at 11:31
i found the error now. It is because of age = null in json for 3rd user. it wont insert that row into the database because of null value of age @FMashiro — Veljko Markovic, May 10 '17 at 11:42
Glad you found the error, you may want to post your fix as an answer so that others can use your solution in the future :) — Florian Humblot, May 10 '17 at 11:43

score 0 · Answer 1 · answered May 10 '17 at 11:48

0

The answer is that age = null in json wont insert into database, must make that nullable into the table

answered May 10 '17 at 11:48

Veljko Markovic

3
4

Cant insert specific row into the database PHP MySQL JSON

1 Answers1