Bad: [php] foreach?

Question

Can't manage to insert data into table

This is an example of the file that has to be inserted into my database. the file get's uploaded to the server with a simple form.

{
    “FileName”: “XXXX",
    “Code”: “11112233",
    “Contacts”: [
        {
        “rowId” => '',
        “TicketId” => "xxxxxxxxxxxx",
        “otherId” => "YYYYYYYYYYYYYYYYYYYYYYY",
        “ClientId” => "wwwwwwwwwwwwwwwwwwwwwwwwwww",
        “Name” => "MARCELLO",
        “LName” => "MARCELLO",
        “Phone” => "4315415151434",
        “ADDRESS” => "hhhhhvofvofvvv",
        “Mail” => "dfwfwf@fwes.fd"
        },
        {
        “rowId” => '',
        “TicketId” => "xxxxxxxxxxxx",
        “otherId” => "YYYYYYYYYYYYYYYYYYYYYYY",
        “ClientId” => "wwwwwwwwwwwwwwwwwwwwwwwwwww",
        “Name” => "MARCELLO",
        “LName” => "MARCELLO",
        “Phone” => "4315415151434",
        “ADDRESS” => "hhhhhvofvofvvv",
        “Mail” => "dfwfwf@fwes.fd"
        }
    ]
}

In the main page i include the script for the connection to the database i'm sure that it works, normally used for all other pages of my web work.

$host = "localhost";
$db_user = "xxxx";
$db_pw = "xxxxx";
$db_name = "xxxxx";
// connessione
try {
  // stringa di connessione al DBMS
  $connessione = new PDO("mysql:host=$host;dbname=$db_name", $db_user, $db_pw);
  // impostazione dell'attributo per il report degli errori
  $connessione->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}

catch(PDOException $e)
{
  // notifica in caso di errore nel tentativo di connessione
  echo "Errore:" .$e->getMessage();
  die();
}

This is tha part of the code of my upload page that is not doing the magic for me:

$file = file_get_contents($filejson);
if(!function_exists('json_decode')) die('Il server non ha tale funzione');
$result = json_decode($file, true);
                foreach ($result as $row){
                        $sql = "INSERT INTO ibl_Anag (TicketId,otherId,ClientId,Name,LName,MobPhone,Phone,address,mail)
                        VALUES ('".$row["TicketId"]."'
                                ,'".$row["otherId"]."'
                                ,'".$row["ClientId"]."'
                                ,'".$row["Name"]."'
                                ,'".$row["LName"]."'
                                ,'".$row["Phone"]."'
                                ,'".$row["MainPhone"]."'
                                ,'".$row["address"]."'
                                ,'".$row["mail"]."' )";
                        $stmt=$connessione->prepare($sql);
                        $stmt->execute();
                        if(!$stmt){Echo "la insert non ha funzionato";}


    }

I don't get errors from the code but the data doesn't insert into the mysql table. Probably i'm doing something wrong in the logic of the script but can't understand where. Can any one please help me. thanks.

your sql misses completly the point of prepared statements and is potentially vulnerable. — Professor Abronsius, Apr 25 '19 at 07:45
Your JSON uses `”` instead of `"` for the quotes. Other than that, you have some homework on making SQL errors visible so you can understand what the server responds. — Markus AO, Oct 04 '21 at 13:07

Zhorov · Answer 1 · 2021-10-04T11:55:21.150

You need to consider the following:

Use parameterized prepared statement. This will help you to prevent possible SQL injection and errors from wrong user input. Assign placeholders (?) in your statement, prepare this statement once, bind values to your parameters and execute the statement multiple times.
Your JSON is not valid (as is posted in the question). Use a valid JSON and read "Contacts" array from this JSON to execute your statement. Also, there is no "MainPhone" in your JSON and "address" is "ADDRESS".

You may try with the following code:

<?php
# Connection
$host    = "localhost";
$db_user = "xxxx";
$db_pw   = "xxxxx";
$db_name = "xxxxx";
try {
    $connessione = new PDO("mysql:host=$host;dbname=$db_name", $db_user, $db_pw);
    $connessione->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
    echo "Errore:" .$e->getMessage();
    die();
}  

# Data
$file = file_get_contents($filejson);
if (!function_exists('json_decode')) {
    die('Il server non ha tale funzione');
}   
$result = json_decode($file, true);

# Prepare and execute statement
try {
    $sql = 
        "INSERT INTO ibl_Anag (TicketId,otherId,ClientId,Name,LName,MobPhone,Phone,address,mail)
        VALUES (?,?,?,?,?,?,?,?,?)";
    $stmt = $connessione->prepare($sql);
    foreach ($result["Contacts"] as $row) {
        $ticketId = $row["TicketId"];
        $otherId  = $row["otherId"];
        $clientId = $row["ClientId"];
        $name     = $row["Name"];
        $lname    = $row["LName"];
        $mobPhone = $row["Phone"];
        $phone    = $row["Phone"];
        $address  = $row["ADDRESS"];
        $mail     = $row["mail"];
        $stmt->BindParam(1, $ticketId);
        $stmt->BindParam(2, $otherId); 
        $stmt->BindParam(3, $clientId);
        $stmt->BindParam(4, $name);    
        $stmt->BindParam(5, $lname);   
        $stmt->BindParam(6, $mobPhone);
        $stmt->BindParam(7, $phone);   
        $stmt->BindParam(8, $address);
        $stmt->BindParam(9, $mail);    
        if (!$stmt->execute()) {
            echo "la insert non ha funzionato";
        }
    }                       
} catch(PDOException $e) {
    echo "Errore:" .$e->getMessage();
    die();
}  
?>

Valid JSON example (based on the sample data from the question):

{
    "FileName": "XXXX",
    "Code": "11112233",
    "Contacts": [
        {
        "rowId": "",
        "TicketId": "xxxxxxxxxxxx",
        "otherId": "YYYYYYYYYYYYYYYYYYYYYYY",
        "ClientId": "wwwwwwwwwwwwwwwwwwwwwwwwwww",
        "Name": "MARCELLO",
        "LName": "MARCELLO",
        "Phone": "4315415151434",
        "ADDRESS": "hhhhhvofvofvvv",
        "Mail": "dfwfwf@fwes.fd"
        },
        {
        "rowId": "",
        "TicketId": "xxxxxxxxxxxx",
        "otherId": "YYYYYYYYYYYYYYYYYYYYYYY",
        "ClientId": "wwwwwwwwwwwwwwwwwwwwwwwwwww",
        "Name": "MARCELLO",
        "LName": "MARCELLO",
        "Phone": "4315415151434",
        "ADDRESS": "hhhhhvofvofvvv",
        "Mail": "dfwfwf@fwes.fd"
        }
    ]
}

Thanks, your example made me understand my mistakes. – Marcello Di Cesare Apr 26 '19 at 07:44 — Marcello Di Cesare, Apr 26 '19 at 07:44

Professor Abronsius · Answer 2 · 2019-04-25T08:05:58.943

To use a prepared statement correctly you need to assign placeholders in the SQL statement to which you bind variables before executing the statement. The beauty is that you create the prepared statement once outside the loop and then execute many times in the loop simply by changing the assigned values of the named variables. The below code has not been tested but should direct you towards the end goal.

The code below is using mySQLi - a similar approach can be used if using PDO except that the variables should exist, even as empty variables, before assigning to bindParam etc

$sql='insert into ibl_anag
    ( `ticketid`,`otherid`,`clientid`,`name`,`lname`,`mobphone`,`phone`,`address`,`mail` )
        values
    (?,?,?,?,?,?,?,?,?)';

$stmt=$connessione->prepare( $sql );


if( $stmt ){
    /*
        assumed that fields named as ID are integers, 
        other firlds are strings
    */
    $stmt->bind_params( 'iiissssss', $tid,$oid,$cid,$name,$lname,$mob,$phone,$addr,$email );


    $json = json_decode( file_get_contents( $filejson ) );
    foreach( $json as $obj ){
        $tid=$obj->TicketId;
        $oid=$obj->otherId;
        $cid=$obj->ClientId;
        $name=$obj->Name;
        $lname=$obj->LName;
        $mob=$obj->Phone;
        $phone=$obj->MainPhone;
        $addr=$obj->address;
        $email=$obj->mail;

        $stmt->execute();
    }
    $stmt->close();
}

For PDO you could try as follows:

$sql='insert into ibl_anag
    ( `ticketid`,`otherid`,`clientid`,`name`,`lname`,`mobphone`,`phone`,`address`,`mail` )
        values
    (:tid,:oid,:cid,:name,:lname,:mob,:phone,:addr,:email)';

$stmt=$connessione->prepare( $sql );

if( $stmt ){

    $json = json_decode( file_get_contents( $filejson ) );
    foreach( $json as $obj ){
        $stmt->execute(array(
            ':tid'  =>  $obj->TicketId,
            ':oid'  =>  $obj->otherId,
            ':cid'  =>  $obj->ClientId,
            ':name' =>  $obj->Name,
            ':lname'=>  $obj->LName,
            ':mob'  =>  $obj->Phone,
            ':phone'=>  $obj->MainPhone,
            ':addr' =>  $obj->address,
            ':email'=>  $obj->mail
        ));
    }
}

Bad: [php] foreach?

2 Answers2