CWE view hierarchical relationship analysis: node relationship query

This article is shared from Huawei cloud community Analysis of CWE view hierarchical relationship and query of CWE node relationship , author: Uncle_Tom.

1. Convert CWE to SQLite table

basis Analysis of CWE view hierarchy and storage and definition of CWE nodes Description of the structure and definition of CWE stored in xml file. We convert the information stored in the xml file of CWE into the database. The database adopts lightweight SQLite.

1.1. Database table storing CWE information

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

  • Meaning of CWE database table:

cwe_ Node: main information of CWE node;

cwe_relation: CWE relation;

cwe_ applicable_ Platforms: application platform for CWE

cwe_detection_method: CWE detection method

cwe_ taxonomy_ Mappings: the relationship between CWE and industry specifications;

cwe_demostrative_example: sample code of CWE;

cwe_ observed_ Example: example observed by CWE;

cwe_potential_mitigation: CWE reduction measures;

cwe_ attack_ Patterns: attack mode of CWE;

cwe_ common_ Sequence: hazards caused by CWE.

1.2. Definition of CWE node and node relationship table

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

-- CWE Definition of nodes
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`);

 

1.3. Store xml information in the database

  • Parse the CWE xml file and store the xml information into the Sqlite database. The specific steps are omitted (this code is not difficult to implement);

  • DB Browser for SQLite is used as the query interface;

  • For the convenience of query, we change the type of node relationship as "ChildOf" to "Has_Member".

1.4. Take CWE-1000 researcher view as an example

We still take CWE-1000 researcher view as an example:

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

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

2. Query of CWE relationship

The relationship between CWES is stored in the form of parent and child nodes in the table CWE_ In relation. SQLite provides a recursive query method to traverse the tree structure. We can use this feature of SQLite to complete the query of the relationship between CWES.

Let's first introduce the key technology of this feature, common table expression.

2.1. Common table expression

In 1999, common table expression (CTE) became a part of ANSI SQL 99 standard.

CTE can be regarded as a temporary result set. Using common expression CTE can make the statement more clear and concise.

Benefits of CTE:

  • The readability of query statements is better;

  • In a query, it can be referenced multiple times;

  • Be able to connect multiple CTE s;

  • Be able to create recursive queries;

  • Can improve SQL execution performance;

  • Can effectively replace the view

Comparison of CTE with temporary tables and table variables.

  • Temporary table: the table structure needs to be created through I/O operation in the temporary database TempDB. Once the user pushes out the environment, it will be automatically deleted;

  • Table variable: it exists in the form of table structure in memory. Its definition is consistent with that of variable. Its use is similar to that of table, and there is no need to generate I/O;

  • Common table expression: defines the temporary storage result set object saved in memory. It does not generate I/O and does not need to be defined according to table variables. The use method is similar to that of a table. You can reference it yourself or be referenced multiple times in the 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:

  • Ordinary: ordinary common table expressions help to make the query easier to understand by decomposing the sub queries in the main query;

  • Recursive common table expression: it provides the function of hierarchical or recursive query of trees and graphs.

Due to the many benefits of CTE, especially the enhancement of tree and graph processing ability. Various databases have realized CTE functions:

  • The introduction of CTE into SQL Server 2005 in 2005;

  • CTE was introduced into PostgreSQL version 8.4 in 2009;

  • The introduction of CTE in Oracle version 12.1 in 2013;

  • In 2014, SQLite version 3.8.3 was introduced into CTE;

  • In 2017, MySQL was introduced into CTE in version 8.0.1;

2.2. Recursive query in SQLite

  • SQLite With syntax

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

  • 'SELECT stmt 'must be a composite 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 individual SELECT statements that make up the compound must be recursive. If the FROM clause of a SELECT statement happens to contain a reference to the CTE table (the table named to the left of the AS clause), the SELECT statement is recursive.

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

  • All non recursive SELECT statements must appear before any recursive SELECT statement.

  • Recursive SELECT statements must be separated from non recursive SELECT statements and must be separated from each other by 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 from the last non recursive SELECT statement.

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

Recursive common table expressions must be similar to the following:

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

2.3. CWE position in view

Find the location of a CWE in the attempt. It can be transformed into finding the parent node of a node.

  • Here, the level field is used to reflect the level of the node from the highest level to the current node;

  • The symbol '- >' is used to reflect the link relationship of nodes.

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

CWE-120 actual location

Reference 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 = 120

  • Query results

From the query results, we can see that the parent nodes of CWE-120 are CWE-119, CWE-118 and CWE-664. This result is the same as the actual page display.

  • Note: since CWES are not completely orthogonal when they are defined, there is a scenario where CWES are in different branches. In specific use, the user needs to analyze the context scenario of the actual defect and confirm it. For example:

This scenario exists in CWE-425 direct request (mandatory browsing), as shown in the following query results.

2.4. Child nodes of CWE

Find the CWE contained under a CWE. This scenario can be transformed into finding the child nodes of a node.

For example, find all nodes under CWE-119

  • Reference 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

  • Query results

 

3. Other information obtained from CWE database

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

3.1. Industry specifications associated with CWE

Many of the external views of CWE are the mapping relationship between CWE and industry specifications. We can analyze the focus and overlap of these specifications through the coverage of these specifications to CWE, so as to carry out comprehensive defense according to our own actual situation when formulating security defense measures.

For example: check the reference of the associated industry specifications to CWE in the CWE view.

  • Reference SQL

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

  • Query results

3.2. CWE abatement measures

Check the security mitigation measures identified by CWE for security defense of some security problems.

For example, check the risk reduction measures of CWE associated with CISQ specification.

  • Reference 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:

5. Summary

  • The database table of SQLite corresponding to the main information stored in CWE Xml file is designed;

  • This paper briefly introduces the use of common table expression (CTE) in database for recursive query of tree or graph;

  • Realize the analysis information (parent node) and the information of CWE (child node) in the view with the help of common table expression;

  • According to the CWE database, the analysis of the association relationship of the industry specifications included in CWE is realized;

  • Implement the weakness reduction measures of CWE associated with CISQ according to CWE database;

  • Based on the above analysis, it can better help us complete the comprehensive defense of software security.

 

Click follow to learn about Huawei's new cloud technology for the first time~

Keywords: Huawei Cloud Software development

Added by jdavila28 on Thu, 27 Jan 2022 11:26:23 +0200

Popular Keywords

©2024 Programming VIP