Issue with inserting data from multi-dimensional array in mySQL with PHP

Question

The intent of this PHP is to loop through an array returned by an API call and insert the resulting rows into a mySQL table. The array is declared as $data.

The example array provided has two call records [0] and [1] however it can return no call records and many call records depending on caller volume:

Here is my PHP:

<?php 

foreach($data as $i => $item) {

      
   $link = mysqli_connect("127.0.0.1", "db", "password", "table"); 
    

    $sql = "INSERT INTO ns_cdr (session_id,orig_user, dnis, ani, time_begin) 
            VALUES ('".$data[$i]['session_id']."', 
                    '".$data[$i]['orig_match']."', 
                    '".$data[$i]['dnis']."', 
                    '".$data[$i]['ani']."',
                    '".$data[$i]['time_begin']."') 
            ON DUPLICATE KEY UPDATE
                orig_user = '".$data[$i]['orig_match']."',
                dnis = '".$data[$i]['dnis']."',
                ani = '".$data[$i]['ani']."',
                time_begin='".$data[$i]['time_begin']."'
                    ";

        if(mysqli_query($link, $sql)){
        echo "";
        } else{
        echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
        }
    //}

}


?>

This is the result:

Notice: Undefined index: session_id in /var/www/html/tool_getcalls.php on line 50

Notice: Undefined index: orig_match in /var/www/html/tool_getcalls.php on line 51

Notice: Undefined index: dnis in /var/www/html/tool_getcalls.php on line 52

Notice: Undefined index: ani in /var/www/html/tool_getcalls.php on line 53

Notice: Undefined index: time_begin in /var/www/html/tool_getcalls.php on line 54

Notice: Undefined index: orig_match in /var/www/html/tool_getcalls.php on line 56

Notice: Undefined index: dnis in /var/www/html/tool_getcalls.php on line 57

Notice: Undefined index: ani in /var/www/html/tool_getcalls.php on line 58

Notice: Undefined index: time_begin in /var/www/html/tool_getcalls.php on line 59

Just not sure how to handle the loop correctly to parse the array contents. Any help is appreciated!

We get the array upstream and store it in $data. Not really sure what the issue is? — user3498801, Oct 01 '22 at 16:28
The array you're trying to iterate through seem to be inside another array that your code doesn't take into account. Try: `foreach($data['call'] => $item)` and then use `$item['session_id']` etc to get the data in your foreach and see if that works better. — M. Eriksson, Oct 01 '22 at 16:58
**Warning!** You're open to [SQL injection attacks](https://owasp.org/www-community/attacks/SQL_Injection)! Read [how to prevent SQL injection in PHP](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) by using prepared statements with bound parameters instead of injecting variables directly into your queries. It's not just about security. If your data contains, for example, a single quote `'`, your query will break. That's just one example, there are more characters that can cause issues. — M. Eriksson, Oct 01 '22 at 17:03
I'm not super worried about that as this is a backend routine not exposed to world. — user3498801, Oct 01 '22 at 17:09
The suggestion of foreach($data['call'] => $item) resulted in a Parse error: syntax error, unexpected '=>' (T_DOUBLE_ARROW) . — user3498801, Oct 01 '22 at 17:10
@M.Eriksson, any other suggestions? Really appreciate your thoughts. Banging head against wall with this. — user3498801, Oct 01 '22 at 17:51
_"I'm not super worried about that as this is a backend routine not exposed to world"_ - Did you read the whole comment? As mentioned, it's not just about security. Your query will fail if the string contains certain characters. That can happen any time you get data from somewhere and just put it directly into your queries like you're doing. — M. Eriksson, Oct 01 '22 at 18:49
Sorry, my bad... It was a typo. It should be: `foreach($data['call'] as $item)` — M. Eriksson, Oct 01 '22 at 18:53

score 0 · Answer 1 · answered Oct 02 '22 at 09:25

And to process the api response using a prepared statement

/* craft the sql with placeholders */
$sql='insert into `ns_cdr` ( `session_id`, `orig_user`, `dnis`, `ani`, `time_begin` ) 
        values ( ?, ?, ?, ?, ? ) 
    on duplicate key update 
        `orig_user`=?,
        `dnis`=?,
        `ani`=?,
        `time_begin`=?';

/* 
    create the prepared statement and bind the placeholders to variables.
    
    These variables do not need to exist at this stage when using mysqli
    but with PDO this is not the case.
*/
$stmt=$link->prepare( $sql );
$stmt->bind_param( 'sssssssss', $sid, $usr, $dnis, $ani, $begin, $usr, $dnis, $ani, $begin );



/*
    $SRCDATA represents the response from the api. The top-level of this
    array is keyed under `call` so you need to process under that key.
*/
$data=$SRCDATA['call'];


/*
    loop through the data array and, for simplicity, cast the
    child arrays as objects to allow use of arrow notation.
*/
foreach( $data as $index => $item ){
    $obj=(object)$item;
    
    $sid=$obj->session_id;
    $usr=$obj->orig_match;
    $dnis=$obj->dnis;
    $ani=$obj->ani;
    $begin=$obj->time_begin;
    
    # commit the data to the db
    $stmt->execute();
}
$stmt->close();
$link->close();

Issue with inserting data from multi-dimensional array in mySQL with PHP

1 Answers1