Insert multiple rows into database from json containing multidimensional data

Question

This is the following JSON I am working with. I have everything being pulled and added correctly except the content under "tactics" -- which comes out as Array.

My goal is to store the "tactics" values as a comma-delimited string.

'[
{
  "queryFrequency": "P1D",
  "queryPeriod": "P1D",
  "triggerOperator": "GreaterThan",
  "triggerThreshold": 0,
  "eventGroupingSettings": {
    "aggregationKind": "SingleAlert"
  },
  "severity": "Medium",
  "query": "let extess",
  "suppressionDuration": "PT1H",
  "suppressionEnabled": false,
  "tactics": [
    "Execution",
    "Persistence"
  ],
  "displayName": "MFA disabled for a user",
  "enabled": true,
  "description": "Multi-Factor Authentication (MFA) helps prevent credential compromise. This alert identifies when an attempt has been made to diable MFA for a user ",
  "alertRuleTemplateName": "65c78944-930b-4cae-bd79-c3664ae30ba7",
  "lastModifiedUtc": "2021-06-16T16:29:52.6974983Z",
  "name": "1ada95bc-b4d5-4776-bc3e-2dbb3684c0b1",
  "id": "/sc0b1",
  "kind": "Scheduled",
  "createIncident": true,
  "groupingConfiguration": {
    "enabled": false,
    "reopenClosedIncident": false,
    "lookbackDuration": "PT5H",
    "entitiesMatchingMethod": "All",
    "groupByEntities": [
      "Account",
      "Ip",
      "Host",
      "Url",
      "FileHash"
    ]
  },
  "playbookName": ""
},
{
  "queryFrequency": "P1D",
  "queryPeriod": "P1D",
  "triggerOperator": "GreaterThan",
  "triggerThreshold": 0,
  "eventGroupingSettings": {
    "aggregationKind": "SingleAlert"
  },
  "severity": "Medium",
  "query": "StppUsed",
  "suppressionDuration": "PT1H",
  "suppressionEnabled": false,
  "tactics": [
    "Execution",
    "Persistence"
  ],
  "displayName": "Explicit MFA Deny",
  "enabled": true,
  "description": "User explicitly denies MFA push, indicating that login was not expected and the account\'s password may be compromised.",
  "alertRuleTemplateName": "a22740ec-fc1e-4c91-8de6-c29c6450ad00",
  "lastModifiedUtc": "2021-06-16T16:29:54.0826821Z",
  "name": "bba57ceb-dd33-4297-8080-b19b1bd07a21",
  "id": "/suobba5d07a21",
  "kind": "Scheduled",
  "createIncident": true,
  "groupingConfiguration": {
    "enabled": false,
    "reopenClosedIncident": false,
    "lookbackDuration": "PT5H",
    "entitiesMatchingMethod": "All",
    "groupByEntities": [
      "Account",
      "Ip",
      "Host",
      "Url",
      "FileHash"
    ]
  },
  "playbookName": ""
    }  ]'

This is my code:

...
$dep_cols=array("queryFrequency","queryPeriod","triggerOperator","triggerThreshold","aggregationKind","severity","query","suppressionDuration","suppressionEnabled","tactics","displayName","enabled","description","kind","createIncident","playbookName");  // declare columns
    $dep_keys=array_map(function($v){return ":$v";},$dep_cols);  // build :keys    
    $dep_cols=array_combine($dep_keys,$dep_cols);   // assign :keys
    var_export($dep_cols);
    $dep_query="INSERT INTO `template_rules` (`id`,`".implode('`,`',$dep_cols)."`)"; // list columns as csv
    $dep_query.=" VALUES ('',".implode(',',array_keys($dep_cols)).");";
    echo "<div>$dep_query</div>";
    $stmt_add_dep=$db->prepare($dep_query);
        
    foreach(json_decode($json) as $d){
        foreach($dep_cols as $k=>$v){
            if($k==':tactics'){$v=json_decode($v);}
            $stmt_add_dep->bindValue($k,(property_exists($d,$v)?$d->$v:""));
            echo "<div>$k => {$d->$v}</div>";
        }
        $stmt_add_dep->execute();
        echo "<div>Dep Affected Rows: ",$stmt_add_dep->rowCount(),"</div><br>";
    }
...

If I remove the if($k==':tactics') statement, I just get Array. I'm not sure how to pull those values out as they look to just be a string in an array.

Current results look like this:

...
:suppressionDuration => PT1H
:suppressionEnabled =>
:tactics =>
:displayName => MFA disabled for a user
:enabled => 1
...

I would like to see var_export(json_decode($json)) as part of the minimal reproducible example. — mickmackusa
– mickmackusa ♦, Commented Nov 13, 2022 at 22:53
How exactly do you wish for "tactics": ["Execution", "Persistence"] to be stored in your :tactics column? Are you storing the two values as a json string, as a comma-delimited string, something else? What is your goal? — mickmackusa
– mickmackusa ♦, Commented Nov 13, 2022 at 23:28
@mickmackusa My goal is to do it as a comma-delimited string. :) — Jason
– Jason, Commented Nov 14, 2022 at 0:20

mickmackusa · Accepted Answer · 2022-11-14 01:32:20Z

Here's a working refactor of your script.

Create arrays of the whitelisted column names and a number of placeholders (I prefer the vague ?, but you can use named placeholders if you like).

Create separate payloads of values to be fed to the prepared statement when execute() is called.

You don't need to mention id if you are autoincrementing that column.

Code: (PHPize.online Demo)

$whitelist = [
    "queryFrequency", "queryPeriod", "triggerOperator", "triggerThreshold",
    "aggregationKind", "severity", "query", "suppressionDuration",
    "suppressionEnabled", "tactics", "displayName", "enabled",
    "description", "kind", "createIncident", "playbookName"
];

$columns = [];
$placeholders = [];
$valueSets = [];
foreach ($whitelist as $column) {
    $columns[] = "`$column`";
    $placeholders[] = "?";
}
foreach (json_decode($json) as $i => $obj) {
    $obj->aggregationKind = $obj->eventGroupingSettings->aggregationKind ?? null;
    $obj->tactics = property_exists($obj, 'tactics') ? implode(',', $obj->tactics) : null;
    foreach ($whitelist as $column) {
        $valueSets[$i][] = $obj->$column ?? null;
    }
}
    
$stmt = $pdo->prepare(
    sprintf(
        'INSERT INTO `template_rules` (%s) VALUES (%s)',
        implode(',', $columns),
        implode(',', $placeholders)
    )
);
foreach ($valueSets as $values) {
    $stmt->execute($values);
    printf("<div>New autoincremented Id: %d</div><br>\n\n", $pdo->lastInsertId());
}

echo json_encode($pdo->query('SELECT * FROM template_rules')->fetchAll(PDO::FETCH_ASSOC), JSON_PRETTY_PRINT);

Amazing. Thanks for the help. I learned a few things tonight :)

Collectives™ on Stack Overflow

Insert multiple rows into database from json containing multidimensional data

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related