时间戳

首页 > 解决方案 > 如何从 SQL 查询中去除 XHTML 标记

sql - 如何从 SQL 查询中去除 XHTML 标记

问题描述

我对 Rapid 7 Nexpose SQL 数据库有以下查询，并且一直在尝试删除 xhtl，

SELECT
  dp.policy_id, dp.title as policy_title, dpr.rule_id, dpr.title as policy_rule_title,
  dp.benchmark_name, da.ip_address, da.host_name, dpr.description, dp.category,
  fapr.date_tested, htmlToText(fapr.proof) as proof, fapr.compliance,
  dpr.severity, htmlToText(dpr.rationale) as rationale, htmlToText(dpr.remediation) as remediation
FROM fact_asset_policy_rule fapr
  JOIN dim_policy dp on dp.policy_id = fapr.policy_id
  JOIN dim_policy_rule dpr on dpr.policy_id = fapr.policy_id and fapr.rule_id = dpr.rule_id
  JOIN dim_asset da on da.asset_id = fapr.asset_id
WHERE fapr.compliance = false order by dp.title, dpr.title`

但是，它使用以下 xhtml 标签导出以下内容以进行基本原理和补救，我想摆脱这些标签：

基本原理列示例：

<xhtml:p xmlns="http://checklists.nist.gov/xccdf/1.2" 
xmlns:ae="http://benchmarks.cisecurity.org/ae/0.5" 
xmlns:cc6="http://cisecurity.org/20-cc/v6.1" 
xmlns:cc7="http://cisecurity.org/20-cc/v7.0" 
xmlns:ciscf="https://benchmarks.cisecurity.org/ciscf/1.0" 
xmlns:notes="http://benchmarks.cisecurity.org/notes" 
xmlns:xccdf="http://checklists.nist.gov/xccdf/1.2" 
xmlns:xhtml="http://www.w3.org/1999/xhtml"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">Attack surface 
reduction helps prevent actions and apps that are typically used by 
exploit-seeking malware to infect machines.</xhtml:p>

我只想获得带有文本的输出“攻击面减少有助于防止通常被寻求利用的恶意软件用来感染机器的操作和应用程序”并删除其余数据。

修复示例：

<xhtml:div xmlns="http://checklists.nist.gov/xccdf/1.2"
xmlns:ae="http://benchmarks.cisecurity.org/ae/0.5" 
xmlns:cc6="http://cisecurity.org/20-cc/v6.1" 
xmlns:cc7="http://cisecurity.org/20-cc/v7.0" 
xmlns:ciscf="https://benchmarks.cisecurity.org/ciscf/1.0" 
xmlns:notes="http://benchmarks.cisecurity.org/notes" 
xmlns:xccdf="http://checklists.nist.gov/xccdf/1.2" 
xmlns:xhtml="http://www.w3.org/1999/xhtml" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <xhtml:p> 
<xhtml:p> To establish the recommended configuration via GP, set the 
following UI path to <xhtml:span 
class="inline_block">Enabled</xhtml:span> : </xhtml:p> <xhtml:code 
class="code_block">Computer Configuration\Policies\Administrative 
Templates\Windows Components\Windows Defender Antivirus\Windows Defender 
Exploit Guard\Attack Surface Reduction\Configure Attack Surface 
Reduction rules </xhtml:code> <xhtml:p> <xhtml:strong>Note: 
</xhtml:strong> This Group Policy path may not exist by default. It is 
provided by the Group Policy template <xhtml:span 
class="inline_block">WindowsDefender.admx/adml</xhtml:span> that is 
included with the Microsoft Windows 10 Release 1709 Administrative 
Templates (or newer). </xhtml:p> <xhtml:p class="bold">Impact:</xhtml:p> 
<xhtml:p> <xhtml:p>When a rule is triggered, a notification will be 
displayed from the Action Center.</xhtml:p> </xhtml:p> </xhtml:p> 
</xhtml:div>

再次需要在没有 XHTML 和 xmlns 的情况下保持干净的修复输出，因此干净的文本应如下所示：

To establish the recommended configuration via GP, set the following UI path to Enabled : Computer Configuration\Policies\Administrative Templates\Windows Components\Windows Defender Antivirus\Windows Defender Exploit Guard\Attack Surface Reduction\Configure Attack Surface Reduction rules, This Group Policy path may not exist by default. It is provided by the Group Policy template WindowsDefender.admx/adml that is included with the Microsoft Windows 10 Release 1709 Administrative Templates (or newer).
Impact: When a rule is triggered, a notification will be displayed from the Action Center.

有没有办法可以将干净的 HTML 转换为文本？没有我不需要的内容？

标签： sqlnexpose

解决方案

有没有办法可以将干净的 HTML 转换为文本？

如果它是完全有效的 XHTML，您可以将其转换为 XML，然后使用 XPath 选择您感兴趣的节点的值。

declare @xhtml varchar(max)
set @xhtml = '<xhtml:div xmlns="http://checklists.nist.gov/xccdf/1.2" xmlns:ae="http://benchmarks.cisecurity.org/ae/0.5" xmlns:cc6="http://cisecurity.org/20-cc/v6.1" xmlns:cc7="http://cisecurity.org/20-cc/v7.0" xmlns:ciscf="https://benchmarks.cisecurity.org/ciscf/1.0" xmlns:notes="http://benchmarks.cisecurity.org/notes" xmlns:xccdf="http://checklists.nist.gov/xccdf/1.2" xmlns:xhtml="http://www.w3.org/1999/xhtml" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">     <xhtml:p>         <xhtml:p>To establish the recommended configuration via GP, set the following UI path to <xhtml:span class="inline_block">Enabled</xhtml:span> : </xhtml:p>         <xhtml:code class="code_block">Computer Configuration\Policies\Administrative Templates\Windows Components\Windows Defender Antivirus\Windows Defender Exploit Guard\Attack Surface Reduction\Configure Attack Surface Reduction rules </xhtml:code> <xhtml:p> <xhtml:strong>Note:</xhtml:strong> This Group Policy path may not exist by default. It is provided by the Group Policy template <xhtml:span class="inline_block">WindowsDefender.admx/adml</xhtml:span> that is included with the Microsoft Windows 10 Release 1709 Administrative Templates (or newer). </xhtml:p>         <xhtml:p class="bold">Impact:</xhtml:p> <xhtml:p> <xhtml:p>When a rule is triggered, a notification will be displayed from the Action Center.</xhtml:p>     </xhtml:p>     </xhtml:p>     </xhtml:div>'

;with xmlnamespaces('http://www.w3.org/1999/xhtml' as xhtml) 
select (cast(@xhtml as xml)).value('(//xhtml:p)[1]', 'varchar(max)')

请参阅http://sqlfiddle.com/#!18/9eecb/136834。

如果需要，请使用更具体的 XPath。对于比“单个节点的值”更复杂的需求，您也可以切换到 XQuery。

推荐阅读