从文本列中提取CVE

Question

我需要从文本列中提取一个CVE。格式是CVE-yyyy-xxxxx。

年份(yyyy)是可变的，每个CVE的x将发生变化--可以是1到6位数字。有时CVE包含在()中，有时紧跟在:后面

该栏如下：

the vulnerability name (CVE-2019-0215) 
the vulnerability name (CVE-2019-0290) extra words 
the vulnerability name CVE-2018-23314: blah blah

新列中的预期产出：

CVE-2019-0215
CVE-2019-0290
CVE 2018-23314

Answer 1

通过示例数据和预期结果(ps:为此学习一些堆栈溢出格式能力)来布局问题是很有用的。

样本数据

CREATE TABLE mytable(
   sourcecol VARCHAR(100) NOT NULL
);
INSERT INTO mytable(sourcecol) VALUES ('the vulnerability name (CVE-2019-0215)');
INSERT INTO mytable(sourcecol) VALUES ('the vulnerability name (CVE-2019-0290) extra words');
INSERT INTO mytable(sourcecol) VALUES ('the vulnerability name CVE-2018-23314: blah blah');

查询:在子字符串函数中使用regex模式

select substring(sourcecol from '(CVE-[0-9]{1,6}-[0-9]{1,6})+')
from mytable

此正则表达式“模式”查找以"CVE-“开头的字符串，后面是1至6位数字，后面是"-”，后面是1到6位数字。

结果

+----------------+
|   substring    |
+----------------+
| CVE-2019-0215  |
| CVE-2019-0290  |
| CVE-2018-23314 |
+----------------+

参见此[医]小提琴

Answer 2

如果这是关于标准CVE格式的，那么可以使用带有正则表达式的子substring()轻松地提取年份：

substring('CVE-2022-1552' from 'CVE-([0-9]{4})-[0-9]+')

返回2022

substring()将返回第一个匹配组，因此regex“描述”整个模式，并通过使用一年的捕获组，只返回该组。

如果需要匹配其他格式，则需要相应地调整正则表达式。

Answer 3

返回字符串中的第一个有效CVE (根据您的定义)：

SELECT substring(col1, 'CVE-[12]\d{3}-\d{1,6}') AS cve
FROM  tbl;

db<>fiddle https://dbfiddle.uk/?rdbms=postgres_14&fiddle=611b139311090c5a83acb856f0b68f3b

第一份简历是1999年发布的，所以一年可以从1或2开始。

CVE的官方定义允许至少4位数字和序列号所需的多少位数，因此：

SELECT substring(col1, 'CVE-[12]\d{3}-\d{4,}') AS cve
FROM  tbl;

手册中的正则表达式的详细信息。