<?php
$json = '[
{
"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,
"kind": "Scheduled",
"createIncident": true,
"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,
"kind": "Scheduled",
"createIncident": true,
"playbookName": ""
}
]';
$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;
}
}
/*
printf(
"INSERT INTO `template_rules` (%s) VALUES (%s)\n",
implode(',', $columns),
implode(',', $placeholders)
);
*/
$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);