CWE view hierarchical relationship resolution: Node relationship query

Abstract: This paper focuses on the relationship between CweIs.

This article is shared by Uncle_Tom from “CWE View Hierarchy Analysis: QUERYING CWE Node Relationships” in Huawei cloud community.

1. CWE is converted into SQLite tables

The structure and definition of CWE stored in XML files are described in “Storage and Definition of CWE Nodes for Parsing CWE View Hierarchies.” We converted the information CWE stored in the XML file to the database. The database uses lightweight SQLite.

1.1. Database table for storing CWE information

According to the structure definition of CWE, the main information we need is extracted and the database table is designed. The CWE database storage table is defined as follows:

• CWE database table meaning:

Cwe_node: main information of the CWE node.

Cwe_relation: CWE relation;

Cwe_applicable_platforms: Application platforms suitable for CWE

Cwe_detection_method: INDICATES the DETECTION method of CWE

Cwe_taxonomy_mappings: The relationship between CWE and industry specifications;

Cwe_demostrative_example: CWE sample code;

Cwe_observed_example: an example observed by CWE;

3. cWE_potential_mitigation: CWE mitigation measures

Cwe_attack_patterns: CWE attack patterns;

Cwe_common_consequence: Harm done by CWE.

1.2. Definition of CWE node and node relation table

This paper focuses on the relationship between CWE, so only the definition of CWE nodes and the relationship table between nodes are described here. The two tables are defined as follows:

DROP INDEX IF EXISTS "cWE_node_ind "; DROP TABLE IF EXISTS "cwe_node"; CREATE TABLE "cwe_node" ( "cweId" INTEGER NOT NULL UNIQUE, "nodeType" TEXT NOT NULL, "nameEn" TEXT NOT NULL, "nameCn" TEXT DEFAULT '', "status" TEXT DEFAULT '', "filter" TEXT DEFAULT '', "structure" TEXT DEFAULT '', "description" TEXT DEFAULT '', "extendedDscription" TEXT DEFAULT '', "likelihoodOfExp" TEXT DEFAULT '', "version" TEXT NOT NULL, PRIMARY KEY("cweId") ); CREATE INDEX "cwe_node_ind" ON "cwe_node" ( "cweId", "nodeType" ); -- CWE node relationship DROP INDEX IF EXISTS "cwe_relation_ind"; DROP TABLE IF EXISTS "cwe_relation"; CREATE TABLE "cwe_relation" ( "cwe_relation_id" INTEGER NOT NULL UNIQUE, "cweId" INTEGER NOT NULL, "nodeType" TEXT NOT NULL, "viewId" INTEGER NOT NULL, "relation" TEXT NOT NULL, "targetCweId" INTEGER NOT NULL, "ordinal" TEXT DEFAULT '', "version" TEXT NOT NULL, PRIMARY KEY("cwe_relation_id" AUTOINCREMENT) ); CREATE INDEX `cwe_relation_ind` on `cwe_relation` (`viewid`,`cweId`,`targetCweId`,`relation`);Copy the code

1.3. Store XML information to a database

• Parse CWE XML file to store XML information in Sqlite database, the specific steps are skipped (this code is not difficult to achieve);

• Using DB Browser for SQLite as the query interface;

• For ease of query, we replace all types whose node relationship is “ChildOf” with “Has_Member”.

1.4. Take the CWE-1000 researcher view as an example

Again, let’s take the CWE-1000 researcher view as an example:

select * from cwe_relation where viewId = 1000 and relation = 'Has_Member' order by cwe_relation_id
Copy the code

The query results are as follows: A total of 1077 records are obtained.

2. CWE relationship query

The relation between CWE is stored in table CWE_relation in the form of parent and child nodes. SQLite provides a recursive query to traverse the tree structure. We can use this feature of SQLite to query relationships between CWE.

Let’s first introduce the key technology of this feature, Common Table Expression.

2.1. Common Table Expression

In 1999, Common Table Expression, or CTE, became part of the ANSI SQL 99 standard.

A CTE can be thought of as a temporary result set. Using the common expression CTE makes the statement cleaner and more concise.

Benefits of CTE:

• Query statements are more readable;

• Can be referenced more than once in a query;

• Multiple Ctes can be connected.

• Ability to create recursive queries;

• Can improve SQL execution performance;

• Can effectively replace views

CTE and temporary table, table variable comparison.

• Temporary tables: Table structures need to be created by I/O operations in the temporary database TempDB, which are automatically deleted once the user pushes out of the environment;

• Table variables: exist in memory in the form of table structures, which are defined in the same way as variables, and are used similar to tables without generating I/O.

• Common table expression: Defines a temporary storage result set object that is held in memory, does not generate I/O, does not need to be defined as a table variable, and uses the same method as a table. It can be referenced by itself or referenced multiple times in a query.

Common table expressions act like temporary views that exist only during a single SQL statement. Common table expressions can be divided into recursive common table expressions and non-recursive common table expressions according to whether they are recursive or not:

• Plain: Plain common table expressions help to make the query easier to understand by breaking down the subqueries in the main query;

• Recursive common table expressions: Provides hierarchical or recursive queries for trees and graphs.

Because of the many benefits of CTE, especially the increased processing power for trees and graphs. Various databases implement CTE functionality:

• Introduction of CTE in 2005 VERSION of SQL Server;

• CTE was introduced in PostgreSQL 8.4 in 2009;

• Introduction of CTE in Oracle 12.1 in 2013;

• CTE was introduced in SQLite version 3.8.3 in 2014;

• MySQL introduced CTE in version 8.0.1 in 2017;

2.2. Recursive queries in SQLite

• SQLite With syntax

Recursive common table expressions can be used to write queries that traverse trees or graphs. Recursive public table expressions have the same basic syntax as regular public table expressions, but with the following additional properties:

• “Select-stmt” must be a compound select. That is, the CTE body must be two or more separate SELECT statements separated by compound operators such as UNION, UNION ALL, INTERSECT, or EXCEPT.

• One or more of the single SELECT statements that make up the composite must be “recursive.” A SELECT statement is recursive if the FROM clause of the SELECT statement happens to contain a reference to a CTE table (the table named to the left of the AS clause).

• One or more SELECT statements in a composite must be non-recursive.

• All non-recursive SELECT statements must precede any recursive SELECT statements.

• Recursive SELECT statements must be separated from non-recursive SELECT statements, and must be separated from each other by the UNION or UNION ALL operators. If there are two or more recursive SELECT statements, they must be separated from each other using the same operator that separates the first recursive * SELECT statement from the last non-recursive SELECT statement.

• Recursive SELECT statements may not use aggregate functions or window functions.

The recursive common table expression must look like the following:

• The basic algorithm for calculating the contents of a recursive table is as follows: Run the initial selection and add the results to the queue. When the queue is not empty: Extracts a row from the queue. Insert that row into the recursion table assuming that the single row you just extracted is the only row in the recursion table, and then run the recursive selection to add all the results to the queue.

2.3. CWE position in the view

Find the location of a CWE in an attempt. This translates to finding the parent of a node.

• Here, the level field reflects the level from the highest level to the current node.

• The symbol “->” is used to indicate the link relationship of the node.

For example: need to find the location of CWE-120 in the researcher view CWE-1000.

The actual location of the CWE-120

Refer to the SQL

WITH RECURSIVE tc(level, targetCweId,target) as (select 0 level,targetCweId, targetCweId target from cwe_relation where viewid=1000 and cweid = 1000 and relation='Has_Member' UNION select tc.level+1 level,r.targetCweId, tc.target||"->"||r.targetCweId target from cwe_relation r,tc where r.viewid=1000 and r.relation='Has_Member' and r.cweid  = tc.targetCweId ) SELECT * FROM tc where tc.targetCweId = 120Copy the code

• The query results

The query result shows that the parent nodes of CWE-120 are CWE-119, CWE-118, and CWE-664 in sequence. The result is the same as the actual page display.

• Note: Since CWE are not completely orthogonal to each other when they are defined, there is a scenario where CWE are in different branches. In specific use, the user needs to analyze the context of the actual defect and confirm it. Such as:

Cwe-425 Direct request (mandatory browse) exists in this scenario, as shown in the following query result.

2.4. Child nodes of CWE

Find the CWE contained under a CWE. This scenario can be implemented by looking for the children of a node.

For example, find all nodes under CWE-119

• Refer to the SQL

  WITH RECURSIVE tc(level, targetCweId,target) as (select 0 level,targetCweId, targetCweId target from cwe_relation where viewid=1000 and cweid = 119 and relation=’Has_Member’ UNION select tc.level+1 level,r.targetCweId, tc.target||”->”||r.targetCweId target from cwe_relation r,tc where r.viewid=1000 and r.relation=’Has_Member’ and r.cweid = tc.targetCweId ) SELECT * FROM tc

• The query results

3. Other information obtained from CWE database

After we convert the CWE information to the database, we can also quickly get a lot of useful statistics.

3.1. Industry specifications associated with CWE

Much of the external view of CWE is the mapping of industry specifications to CWE. We can analyze the emphasis and overlap of these specifications through their coverage of CWE, so that when making security defense measures, we can carry out comprehensive defense according to our own actual situation.

For example, view references to CWE by industry specifications associated with the CWE view.

• Refer to the SQL

  select taxonomyName,count(*) from cwe_taxonomy_mappings m group by taxonomyName

• The query results

3.2. CWE reduction measures

View the security mitigation measures identified by CWE for security protection against certain security issues.

For example, look at the risk mitigation measures associated with CWE in the CISQ specification.

• Refer to the SQL

  select cweId,nodetype, sum(case when phase=’Requirements’ then 1 else 0 end) as ‘Requirements’, sum(case when phase=’Architecture and Design’ then 1 else 0 end) as ‘Architecture and Design’, sum(case when phase=’Documentation’ then 1 else 0 end) as ‘Documentation’, sum(case when phase=’Build and Compilation’ then 1 else 0 end) as ‘Build and Compilation’, sum(case when phase=’Implementation’ then 1 else 0 end) as ‘Implementation’, sum(case when phase=’Testing’ then 1 else 0 end) as ‘Testing’, sum(case when phase=’System Configuration’ then 1 else 0 end) as ‘System Configuration’, sum(case when phase=’Operation’ then 1 else 0 end) as ‘Operation’ from ( select distinct a.cweid, a.nodeType,b.phase from ( select c.cweid, c.nodeType from cwe_relation r, cwe_node c where r.viewid=1340 and r.relation = ‘Has_Member’ and c.cweId= r.targetCweId ) a left join (select * from cwe_potential_mitigation m) b on a.cweid=b.cweId )a group by cweId,nodetype

• Query results (part) :

4. Reference:

• CWE: cwe.mitre.org/

• SQLite with clause: www.SQLite.net.cn/lang\_with….

5. Summary

• The main information stored in CWE Xml file corresponds to SQLite database table.

• This paper briefly introduces the recursive query method of common table expression (CTE) used in tree or graph in database.

• The analysis information (parent node) and CWE (child node) that CWE belongs to in the view are realized by means of common table expression.

• According to THE CWE database to achieve the CWE included industry specifications association analysis;

• Implementing CWE vulnerability mitigation measures for CISQ association based on CWE database;

• According to the above analysis, can better help us complete the overall defense of software security.

Click to follow, the first time to learn about Huawei cloud fresh technology ~