批量运营CodeQL Cli扫描结果(简易版)

工具

背景&目标

​ CodeQL Cli适合批量做扫描，但是扫描结果并不适合直接做批量的运营，仅适合一些实锤的问题，对于一些还需要人工处理判断的结果就不太适合了(要看源码、调用上下文)；如果使用VSCode插件来做，也只是单条规则扫单个/多个数据库，结果倒是很友好，点点点就能读代码来分析了，所以这种用法不适合批量的query扫描。

​ 如果付费的话自然是可以解决了，可以在CI/CD中集成，就方便多了 -。- 但是对于个人使用者来说不太现实，所以我就想用一个简单的办法来实现这个目的

CodeQL cli批量扫描结束后，导入数据库+历史query结果，直接在vscode里运营结果，流程为：

1
CI/CD 扫描 ---> 结果(数据库+扫描结果) ---> 导入VSCode运营

目标拆解

  1. 批量导入数据库，而不是通过GUI点点点导入
  2. 导入扫描结果，历史query不要清理，为的是把cli的扫描结果导入对应目录之后可以直接在CodeQL的query history中看到

工作流程

批量导入数据库

查看日志，猜测是类似的做法，解析某个配置文件，然后导入，所以要么修改文件，要么直接把数据库目录copy过去就行

1
2
3
4
5
6
Initializing database manager.
Found 1 persisted databases: file:///home/muhe/Work/codeql_multi_work/XNU-revision-2018-October-28--14-31-48
Initializing database panel.
Initializing evaluator log viewer.
Initializing query history manager.
Initializing results panel interface

存储位置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/**
 * databases.ts
 * ------------
 * Managing state of what the current database is, and what other
 * databases have been recently selected.
 *
 * The source of truth of the current state resides inside the
 * `DatabaseManager` class below.
 */

/**
 * The name of the key in the workspaceState dictionary in which we
 * persist the current database across sessions.
 */
const CURRENT_DB = "currentDatabase";

/**
 * The name of the key in the workspaceState dictionary in which we
 * persist the list of databases across sessions.
 */
const DB_LIST = "databaseList";

导出信息就能看到了，所以直接改数据库就能添加多个数据库了

/home/muhe/.config/Code/User/workspaceStorage/693bdf324f8bd69cec87e06d65e8d000/state.vscdb 

1
2
3
4
5
6
7
8
9
10
11
12
13
{
    "databaseList": [
        {
            "uri": "file:///home/muhe/Work/codeql_multi_work/XNU-revision-2018-October-28--14-31-48",
            "options": {
                "ignoreSourceArchive": false,
                "dateAdded": 1684233047172,
                "language": "cpp"
            }
        }
    ],
    "currentDatabase": "file:///home/muhe/Work/codeql_multi_work/XNU-revision-2018-October-28--14-31-48"
}

尝试直接修改这个数据库 就可以批量导入了，不需要挨个点点点了 :)

导入扫描结果

query-history

扫描结果的导入是有个json文件描述的

1
2
3
4
5
6
Reading query history
Reading cached query history from '/home/muhe/.config/Code/User/workspaceStorage/693bdf324f8bd69cec87e06d65e8d000/GitHub.vscode-codeql/workspace-query-history.json'.
Successfully finished extension initialization.
CodeQL extension version: 1.8.4 
CodeQL CLI version: 2.13.1 
Platform: linux x64

我这里随意跑了两个Query，查看这个文件可以看到这两次记录:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
{
  "version": 2,
  "queries": [
    {
      "initialInfo": {
        "queryText": "/**\n * @name Empty block\n * @kind problem\n * @problem.severity warning\n * @id cpp/example/empty-block\n */\n\nimport cpp\n \nfrom BlockStmt b\nwhere b.getNumStmt() = 0\nselect b, \"This is an empty block.\"\n",
        "isQuickQuery": false,
        "isQuickEval": false,
        "queryPath": "/home/muhe/Tools/vscode-codeql-starter/codeql-custom-queries-cpp/example.ql",
        "databaseInfo": {
          "databaseUri": "file:///home/muhe/Work/codeql_multi_work/XNU-revision-2018-October-28--14-31-48",
          "name": "XNU-revision-2018-October-28--14-31-48"
        },
        "start": "2023-05-16T10:44:25.321Z",
        "id": "example.ql-g8Dji9oz8xqxh-XoF96jF"
      },
      "t": "local",
      "evalLogLocation": "/home/muhe/.config/Code/User/globalStorage/github.vscode-codeql/queries/example.ql-DQ3x1MPMGzVEtt7SAYjJ9/evaluator-log.jsonl",
      "evalLogSummaryLocation": "/home/muhe/.config/Code/User/globalStorage/github.vscode-codeql/queries/example.ql-DQ3x1MPMGzVEtt7SAYjJ9/evaluator-log.summary",
      "completedQuery": {
        "query": {
          "querySaveDir": "/home/muhe/.config/Code/User/globalStorage/github.vscode-codeql/queries/example.ql-DQ3x1MPMGzVEtt7SAYjJ9",
          "dbItemPath": "/home/muhe/Work/codeql_multi_work/XNU-revision-2018-October-28--14-31-48",
          "databaseHasMetadataFile": true,
          "metadata": {
            "name": "Empty block",
            "kind": "problem",
            "problem.severity": "warning",
            "id": "cpp/example/empty-block"
          },
          "resultsPaths": {
            "resultsPath": "/home/muhe/.config/Code/User/globalStorage/github.vscode-codeql/queries/example.ql-DQ3x1MPMGzVEtt7SAYjJ9/results.bqrs",
            "interpretedResultsPath": "/home/muhe/.config/Code/User/globalStorage/github.vscode-codeql/queries/example.ql-DQ3x1MPMGzVEtt7SAYjJ9/interpretedResults.sarif"
          }
        },
        "result": {
          "runId": 0,
          "queryId": 0,
          "resultType": 0,
          "evaluationTime": 11424,
          "message": "finished in 11 seconds"
        },
        "successful": true,
        "message": "finished in 11 seconds",
        "resultCount": 1461,
        "sortedResultsInfo": {}
      }
    },
    {
      "initialInfo": {
        "queryText": "/**\n * @name Empty block\n * @kind problem\n * @problem.severity warning\n * @id cpp/example/empty-block\n */\n\nimport cpp\n \nfrom BlockStmt b\nwhere b.getNumStmt() = 0\nselect b, \"This is an empty block.\"\n",
        "isQuickQuery": false,
        "isQuickEval": false,
        "queryPath": "/home/muhe/Tools/vscode-codeql-starter/codeql-custom-queries-cpp/example.ql",
        "databaseInfo": {
          "databaseUri": "file:///home/muhe/Work/codeql_multi_work/XNU-revision-2018-October-28--14-31-48",
          "name": "XNU-revision-2018-October-28--14-31-48"
        },
        "start": "2023-05-16T10:45:23.082Z",
        "id": "example.ql-2I3wLwL9OlUi_h2VbLFuj"
      },
      "t": "local",
      "evalLogLocation": "/home/muhe/.config/Code/User/globalStorage/github.vscode-codeql/queries/example.ql-tPeM-xPnZG3MkZC0duLSE/evaluator-log.jsonl",
      "evalLogSummaryLocation": "/home/muhe/.config/Code/User/globalStorage/github.vscode-codeql/queries/example.ql-tPeM-xPnZG3MkZC0duLSE/evaluator-log.summary",
      "completedQuery": {
        "query": {
          "querySaveDir": "/home/muhe/.config/Code/User/globalStorage/github.vscode-codeql/queries/example.ql-tPeM-xPnZG3MkZC0duLSE",
          "dbItemPath": "/home/muhe/Work/codeql_multi_work/XNU-revision-2018-October-28--14-31-48",
          "databaseHasMetadataFile": true,
          "metadata": {
            "name": "Empty block",
            "kind": "problem",
            "problem.severity": "warning",
            "id": "cpp/example/empty-block"
          },
          "resultsPaths": {
            "resultsPath": "/home/muhe/.config/Code/User/globalStorage/github.vscode-codeql/queries/example.ql-tPeM-xPnZG3MkZC0duLSE/results.bqrs",
            "interpretedResultsPath": "/home/muhe/.config/Code/User/globalStorage/github.vscode-codeql/queries/example.ql-tPeM-xPnZG3MkZC0duLSE/interpretedResults.sarif"
          }
        },
        "result": {
          "runId": 0,
          "queryId": 0,
          "resultType": 0,
          "evaluationTime": 31,
          "message": "finished in 0 seconds"
        },
        "successful": true,
        "message": "finished in 0 seconds",
        "resultCount": 1461,
        "sortedResultsInfo": {}
      }
    }
  ]
}

所以把query结果按照这个格式填进去就行了。

配置文件中需要的CodeQL cli信息获取

1
2
3
4
5
6
7
8
9
10
11
❯ tree -L 1 .
.
├── evaluator-log-end.summary
├── evaluator-log.jsonl
├── evaluator-log.summary
├── evaluator-log.summary.map
├── interpretedResults.sarif
├── query.log
├── results.bqrs
├── results.dil
└── timestamp

经过测试，运营只需要扫描结果就行，其他的可以忽略

  • Evaluator Log 相关可以不要
  • DIL 也可以不要，可以用于query调优啥的，我们只运营结果就不考虑了

FYI: 其他的文件(log、dil等)是为了下面菜单中展示的功能做的：

image-20230516184801557

批量query & 导入结果分析

一般来说，我们会使用到开源的规则以及自己写的规则，如果有一定的积累的话，自己的规则可以搞成一个qlpack，方便后面对新目标的快速分析或者批量查找问题。

通用规则/开源规则

第一种情况，可以利用下面的命令，批量跑特定的规则集

1
2
3
4
5
6
7
8
9
10
11
12
# muhe @ muhe-NUC11PAHi5 in ~/Tools/vscode-codeql-starter/ql/cpp/ql/src/codeql-suites on git:codeql-cli/latest o [18:53:36]
$ tree -L 1 .
.
|-- cpp-code-scanning.qls
|-- cpp-lgtm-full.qls
|-- cpp-lgtm.qls
|-- cpp-security-and-quality.qls
|-- cpp-security-experimental.qls
|-- cpp-security-extended.qls
`-- exclude-slow-queries.yml

0 directories, 7 files

比如我们尝试使用cpp-security-and-quality.qls这个规则集跑老版本的XNU作为演示

1
codeql database run-queries --ram=16384 --threads=12 XNU-revision-2018-October-28--14-31-48  --min-disk-free=1024 -v ~/Tools/vscode-codeql-starter/ql/cpp/ql/src/codeql-suites/cpp-security-and-quality.qls

image-20230516191008453

FYI: 可以使用 codeql resolve queries ~/Tools/vscode-codeql-starter/ql/cpp/ql/src/codeql-suites/cpp-security-and-quality.qls --format=text 获取这个规则集包含了哪些query

image-20230516185953628

特有规则

第二种就使用规则仓库中PICO的pack就行，或者直接指定一个qls扫，就是类似的做法了，比如可以自己搞一个qlpack：

1
codeql database run-queries --ram=16384 --threads=8  --min-disk-free=1024  -- [database] [qlpack]

结果处理

对于这种跑query的方式，如果不指定输出，默认结果会放在数据库的 results目录下，比如:

image-20230516191056186

所以可以写个脚本

  • 修改state.vscdb，批量把codeql db导入
  • 修改query-history文件，把扫描结果导入

最终效果

最终实现的效果如下 :)

image-20230516195840932

FYI: 两个关键文件的路径不同平台下大同小异:

1
2
3
4
5
6
7
8
9
10
11
if 'macOS' in current_platform:
    globalStorage = f'{os.getenv("HOME")}/Library/Application Support/Code/User/globalStorage'
    workspaceStorage = f'{os.getenv("HOME")}/Library/Application Support/Code/User/workspaceStorage'
elif 'Linux' in current_platform:
    globalStorage = f'{os.getenv("HOME")}/.config/Code/User/globalStorage'
    workspaceStorage = f'{os.getenv("HOME")}/.config/Code/User/workspaceStorage'
elif 'Windows' in current_platform:
    globalStorage = f'{os.getenv("APPDATA")}\\Code\\User\\globalStorage'
    workspaceStorage = f'{os.getenv("APPDATA")}\\Code\\User\\workspaceStorage'
else:
    # error

参考