数据库处理操作！！

/*1 删除cncve编号为空的条目*/

/*

delete from vul_info where cncve is NULL

delete from vul_ref where cncve is NULL

delete from vul_cvss where cncve is NULL

delete from vul_cve where cncve is NULL

*/

 

/*2 将CAN改为CVE*/

 

/*update vul_cve set cve=replace(cve,'CAN','CVE') where charindex('CAN',cve)>0*/

 

/*3 将nipc改为NIPC*/

/*

update vul_cve set cncve=replace(cncve,'nipc','NIPC') where charindex('nipc',cncve)>0

update vul_info set cncve=replace(cncve,'nipc','NIPC') where charindex('nipc',cncve)>0

update vul_ref set cncve=replace(cncve,'nipc','NIPC') where charindex('nipc',cncve)>0

update vul_cvss set cncve=replace(cncve,'nipc','NIPC') where charindex('nipc',cncve)>0

*/

/*4 纠正序号的顺序颠倒*/

/*

update vul_cve set cncve=replace(cncve,'2004-NIPC','NIPC-2004') where charindex('nipc',cncve)>4

update vul_info set cncve=replace(cncve,'2004-NIPC','NIPC-2004') where charindex('nipc',cncve)>4

update vul_cvss set cncve=replace(cncve,'2004-NIPC','NIPC-2004') where charindex('nipc',cncve)>4

update vul_ref set cncve=replace(cncve,'2004-NIPC','NIPC-2004') where charindex('nipc',cncve)>4

*/

 

/*5 删除cncve编号为空的条目*/

/*

select * from vul_info where cncve=''

delete from vul_info where cncve=''

delete from vul_cve where cncve=''

delete from vul_cvss where cncve=''

delete from vul_ref where cncve=''

*/

 

/*6 补全cncve编号1*/

/*

select * from vul_info where cncve='NIPC-2003-0094'

select * from vul_info where charindex('漏洞',name) =0

 

 

select * from vul_info where charindex('NIPC-2002',cncve)>0 order by cncve asc

select * from vul_info where name like 'Microsoft TSAC ActiveX远程缓冲区溢出漏洞'

select * from vul_info where charindex('NIPC-2002',cncve)>0  order by cncve asc

 

update vul_ref set cncve='NIPC-2002-0715' where cncve='NIPC-2002-'

update vul_cvss set cncve='NIPC-2002-0715' where cncve='NIPC-2002-'

update vul_info set cncve='NIPC-2002-0715' where cncve='NIPC-2002-'

update vul_cve set cncve='NIPC-2002-0715' where cncve='NIPC-2002-'

*/

 

/*7 补全cncve编号2*/

/*

select * from vul_info where len(cncve)>4 and len(cncve)<14 order by cncve asc

update vul_ref set cncve='NIPC-2002-0117' where cncve='NIPC-2002-117'

update vul_cvss set cncve='NIPC-2002-0117' where cncve='NIPC-2002-117'

update vul_info set cncve='NIPC-2002-0117' where cncve='NIPC-2002-117'

update vul_cve set cncve='NIPC-2002-0117' where cncve='NIPC-2002-117'

 

update vul_ref set cncve='NIPC-2002-0126' where cncve='NIPC-2002-126'

update vul_cvss set cncve='NIPC-2002-0126' where cncve='NIPC-2002-126'

update vul_info set cncve='NIPC-2002-0126' where cncve='NIPC-2002-126'

update vul_cve set cncve='NIPC-2002-0126' where cncve='NIPC-2002-126'

 

update vul_ref set cncve='NIPC-2007-0223' where cncve='NIPC-2007-223'

update vul_cvss set cncve='NIPC-2007-0223' where cncve='NIPC-2007-223'

update vul_info set cncve='NIPC-2007-0223' where cncve='NIPC-2007-223'

update vul_cve set cncve='NIPC-2007-0223' where cncve='NIPC-2007-223'

*/

 

/*8 补全cncve编号3*/

/*

select * from vul_info where publishtime='2004-11-03' order by publishtime asc

select * from vul_info where cncve='NIPC-0680'

 

update vul_ref set cncve='NIPC-2004-0680' where cncve='NIPC-0680'

update vul_cvss set cncve='NIPC-2004-0680' where cncve='NIPC-0680'

update vul_info set cncve='NIPC-2004-0680' where cncve='NIPC-0680'

update vul_cve set cncve='NIPC-2004-0680' where cncve='NIPC-0680'

*/

 

/*9 没有cve编号的前期数据, 补全cncve编号至1998年*/

/*

drop table #temp

drop table #temp2

 

select * into #temp from vul_cve where cncve<>'' and Not (cncve is NULL) and len(cncve)<=4 and cve='' order by cncve asc

 

declare @i int 

declare @count int

declare @str varchar(500)

declare @tempcncve varchar(50)

set @i=1

set @str=''

select @count=count(*) from vul_cve where cncve<>'' and Not (cncve is NULL) and len(cncve)<=4 and cve=''

 

 

select identity(int,1,1) as autoID, * into #temp2 from #temp

 

while @i<=@count

begin

 

select @tempcncve=cncve from #temp2 where autoID=@i

 

set @str=

(

case 

when @i>999 then 'NIPC-1998-'+str(@i,4,0)

when @i>99 and @i<1000  then 'NIPC-1998-0'+str(@i,3,0)

when @i>9 and @i<100  then 'NIPC-1998-00'+str(@i,2,0)

when @i>0 and @i<10  then 'NIPC-1998-000'+str(@i,1,0)

end

)

 

update vul_info set cncve=@str where cncve=@tempcncve

update vul_cve set cncve=@str where cncve=@tempcncve

update vul_ref set cncve=@str where cncve=@tempcncve

update vul_cvss set cncve=@str where cncve=@tempcncve

 

set @i=@i+1

end

 

*/

 

/*10 有cve编号的前期数据, 暂编到1997，为了检索和翻译*/

/*

drop table #temp

drop table #temp2

drop table #temp3

 

declare @tempcncve varchar(50)

declare @str varchar(50)

declare @i int

declare @count int

 

set @i=1

 

select * into #temp from vul_cve where cncve<>'' and Not (cncve is NULL) and len(cncve)<=4 and cve<>'' order by cncve asc

 

select @count=count(*) from #temp

 

select identity(int,1,1) as autoID, * into #temp2 from #temp

 

 

while @i<=@count

begin

 

select @tempcncve=cncve from #temp2 where autoID=@i

 

set @str=

(

case 

when @i>999 then 'NIPC-1997-'+str(@i,4,0)

when @i>99 and @i<1000  then 'NIPC-1997-0'+str(@i,3,0)

when @i>9 and @i<100  then 'NIPC-1997-00'+str(@i,2,0)

when @i>0 and @i<10  then 'NIPC-1997-000'+str(@i,1,0)

end

)

 

update vul_info set cncve=@str where cncve=@tempcncve

update vul_cve set cncve=@str where cncve=@tempcncve

update vul_ref set cncve=@str where cncve=@tempcncve

update vul_cvss set cncve=@str where cncve=@tempcncve

 

set @i=@i+1

end

 

*/

 

/* 11 规范publishtime*/

/*

update vul_info set publishtime=NULL where publishtime='' 

drop table #temp

select * into #temp  from vul_info where publishtime<>'' and NOT( publishtime IS NULL) and charindex(' 0:00:00',publishtime)<=0

update vul_info set publishtime=publishtime+' 0:00:00' where cncve in (select cncve from #temp)

update vul_info set publishtime=replace(publishtime,'-0','-') where NOT (publishtime is NULL)

update vul_info set publishtime=replace(publishtime,'- ','-') where NOT (publishtime is NULL)

update vul_info set publishtime=replace(publishtime,'－','-') where NOT (publishtime is NULL)

update vul_info set publishtime=replace(publishtime,'''','')

update vul_info set publishtime=replace(publishtime,'20003-6-17','2003-6-17')

update vul_info set publishtime=replace(publishtime,'--','-') where NOT (publishtime is NULL)

update vul_info set publishtime=replace(publishtime,'-0 0:00:00','-1 0:00:00') where NOT (publishtime is NULL)

update vul_info set publishtime=replace(publishtime,'2004-0:00:00','2004-11-3 0:00:00') where NOT (publishtime is NULL)

*/

/*辅助代码

select publishtime2=Convert(datetime,publishtime) from vul_info where NOT (publishtime is NULL)

*/

/* 12 规范updatetime*/

/*

update vul_info set updatetime=substring(updatetime,7,4)+'-'+substring(updatetime,1,2)+'-'+substring(updatetime,4,2)+' 0:00:00' where NOT (updatetime is NULL) and charindex('12:00AM',updatetime)>0

*/

/*辅助代码

select * from vul_info where NOT (updatetime is NULL) and charindex('12:00AM',updatetime)>0

 

select updatetime2=Convert(datetime,updatetime) from vul_info where NOT (updatetime is NULL)

*/

/*13 改publishtime updatetime的类型为datetime型*/

 

 

/*14 查漏补缺*/

/*

select * from vul_info where cncve<>'' and Not (cncve is NULL) and len(cncve)<14 order by cncve asc

 

update vul_info set cncve=replace(cncve,'NIPC-20040-','NIPC-2004-') 

update vul_cve set cncve=replace(cncve,'NIPC-20040-','NIPC-2004-') 

update vul_cvss set cncve=replace(cncve,'NIPC-20040-','NIPC-2004-') 

update vul_ref set cncve=replace(cncve,'NIPC-20040-','NIPC-2004-') 

 

update vul_info set cncve=replace(cncve,'BNIPC-','NIPC-') 

update vul_cve set cncve=replace(cncve,'BNIPC-','NIPC-') 

update vul_cvss set cncve=replace(cncve,'BNIPC-','NIPC-')  

update vul_ref set cncve=replace(cncve,'BNIPC-','NIPC-') 

*/

 

/*15 翻译后处理1 有cve编号的前期数据, 补全cncve编号至当年*/

/*

drop table #temp

drop table #temp2

drop table #temp3

 

declare @tempcncve varchar(50)

declare @tempcve varchar(50)

declare @tempstr varchar(50)

declare @tempint int

declare @i int

declare @count int

 

set @i=1

 

select * into #temp from vul_cve where cncve<>'' and Not (cncve is NULL) and charindex('NIPC-1997-')>0 and cve<>'' order by cncve asc

 

select @count=count(*) from #temp

 

select identity(int,1,1) as autoID, * into #temp2 from #temp

 

while @i<=@count

 

begin

 

select @tempcncve= cncve from #temp2 where autoID=@i

 

select @tempcve= cve from #temp2 where autoID=@i

 

select @tempstr=substring(@tempcve,5,4)

 

drop table #temp3

 

select top 1 * into #temp3 from vul_info where charindex('NIPC-'+@tempstr+'-',cncve)>0 order by cncve desc

 

select @tempint=CAST(substring(cncve,11,4) AS Integer) from #temp3

 

set @tempint=@tempint +1

 

update vul_info set cncve='NIPC-'+@tempstr+'-'+str(@tempint,4,0) where cncve=@tempcncve

update vul_cve set cncve='NIPC-'+@tempstr+'-'+str(@tempint,4,0) where cncve=@tempcncve

update vul_ref set cncve='NIPC-'+@tempstr+'-'+str(@tempint,4,0) where cncve=@tempcncve

update vul_cvss set cncve='NIPC-'+@tempstr+'-'+str(@tempint,4,0) where cncve=@tempcncve

 

set @i=@i+1

 

end

*/

 

 

 

 

/*16 翻译后处理2 修正有publishtime或者updatetime的前期数据的cncve编号为当年*/

/*

 

drop table #temp

drop table #temp2

drop table #temp3

 

declare @i int

declare @count int

declare @tempcncve varchar(50)

declare @temppt datetime

declare @temput datetime

declare @tempint int

 

 

select * into #temp from vul_info where (NOT (updatetime is NULL) or NOT(publishtime is NULL)) and charindex('NIPC-1998-',cncve)>0

 

select @count=count(*) from #temp

 

select identity(int,1,1) as autoID, * into #temp2 from #temp

 

set @i=1

 

while @i<=@count

 

begin

 

select @tempcncve=cncve from #temp2 where autoID=@i

select @temppt=publishtime from #temp2 where autoID=@i

select @temput=updatetime from #temp2 where autoID=@i

 

if(NOT(@temppt is NULL) and str(year(@temppt),4,0)>'1998')

 

begin

 

drop table #temp3

 

select top 1 * into #temp3 from vul_info where charindex('NIPC-'+str(year(@temppt),4,0)+'-',cncve)>0 order by cncve desc

 

select @tempint=CAST(substring(cncve,11,4) AS Integer) from #temp3

 

set @tempint=@tempint +1

 

update vul_info set  cncve='NIPC-'+str(year(@temppt),4,0)+'-'+str(@tempint,4,0) where cncve=@tempcncve

 

end

 

else if(NOT(@temput is NULL) and str(year(@temput),4,0)>'1998')

 

begin

 

drop table #temp4

 

select top 1 * into #temp4 from vul_info where charindex('NIPC-'+str(year(@temput),4,0)+'-',cncve)>0 order by cncve desc

 

select @tempint=CAST(substring(cncve,11,4) AS Integer) from #temp4

 

set @tempint=@tempint +1

 

update vul_info set  cncve='NIPC-'+str(year(@temput),4,0)+'-'+str(@tempint,4,0) where cncve=@tempcncve

 

end

 

set @i=@i+1

end

*/

/*辅助结果查看

select * from vul_info where charindex('NIPC-1998-',cncve)>0 and str(year(publishtime),4,0)>'1998'

*/