从包含多维数据的json中插入多行到数据库中

Question

这是我正在使用的以下JSON。除了"策略"下的内容外，所有内容都被正确地拉出并添加了。——结果是Array.

我的目标是储存"战术"。值作为逗号分隔的字符串。

'[
{
"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": ""
}  ]'

这是我的代码:

...
$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($k==':tactics')语句，我就得到Array。我不确定如何拉出这些值，因为它们看起来只是一个数组中的字符串。

当前结果如下所示:

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

Answer 1

这是您的脚本的工作重构。

创建白名单列名和一些占位符的数组(我更喜欢模糊的?，但是如果您愿意，也可以使用命名占位符)。

创建单独的值的有效负载，当execute()被调用时提供给准备好的语句。

如果您要自动增加该列，则不需要提及id。

代码:(PHPize。在线演示)

$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>nn", $pdo->lastInsertId());
}
echo json_encode($pdo->query('SELECT * FROM template_rules')->fetchAll(PDO::FETCH_ASSOC), JSON_PRETTY_PRINT);