MySQL查询需求：合并同plugin_id漏洞并实现查询内参数传递

希望合并tenable_network表中所有具有相同plugin_id的漏洞记录，展示受影响对象时不重复显示同一漏洞对应的多条受影响记录。

现有查询语句 #

漏洞汇总查询 #

SELECT
 plugin_id as 'Plugin id',
 cve as CVE,
 cvss3_base_score as cvss3_base_score,
 risk as Risk,
 COUNT(DISTINCT(Host)) as 'Affected Unique Hosts',
 Name,
 plugin_family as 'Plugin Family',
 last_seen as 'Last Seen',
 vulnerability_state as 'Vulnerability State'
from tenable_network
GROUP BY plugin_id
ORDER BY plugin_id DESC

指定插件受影响资产查询 #

SELECT
 role_id,
 Host,
 Name,
 Synopsis,
 Port,
 Protocol,
 OS,
 FQDN 
FROM tenable_network
WHERE plugin_id='90317'
GROUP BY Host

当前可针对plugin_id='90317'的记录按Host分组，请问有没有简便方法在查询内传递参数？

1. 使用SQL会话变量（分数据库语法） #

MySQL/MariaDB

先定义会话变量，再在查询中统一引用，避免重复写参数：

-- 定义目标插件ID变量
SET @target_plugin_id = '90317';

-- 漏洞汇总查询（修复原GROUP BY语法问题，加入所有非聚合列）
SELECT
  tn.plugin_id as 'Plugin id',
  tn.cve as CVE,
  tn.cvss3_base_score,
  tn.risk as Risk,
  COUNT(DISTINCT tn.Host) as 'Affected Unique Hosts',
  tn.Name,
  tn.plugin_family as 'Plugin Family',
  tn.last_seen as 'Last Seen',
  tn.vulnerability_state as 'Vulnerability State',
  -- 可选：用GROUP_CONCAT拼接去重的受影响主机列表
  GROUP_CONCAT(DISTINCT tn.Host SEPARATOR ', ') as 'Affected Hosts'
FROM tenable_network tn
WHERE tn.plugin_id = @target_plugin_id
GROUP BY tn.plugin_id, tn.cve, tn.cvss3_base_score, tn.risk, tn.Name, tn.plugin_family, tn.last_seen, tn.vulnerability_state
ORDER BY tn.plugin_id DESC;

-- 受影响资产明细查询
SELECT
  role_id,
  Host,
  Name,
  Synopsis,
  Port,
  Protocol,
  OS,
  FQDN 
FROM tenable_network
WHERE plugin_id = @target_plugin_id
GROUP BY Host;

PostgreSQL

使用\set定义会话变量，或用WITH子句传递参数：

\set target_plugin_id '90317'

-- 漏洞汇总查询
SELECT
  plugin_id as "Plugin id",
  cve as CVE,
  cvss3_base_score,
  risk as Risk,
  COUNT(DISTINCT Host) as "Affected Unique Hosts",
  Name,
  plugin_family as "Plugin Family",
  last_seen as "Last Seen",
  vulnerability_state as "Vulnerability State",
  -- 可选：用STRING_AGG拼接去重主机
  STRING_AGG(DISTINCT Host, ', ') as "Affected Hosts"
FROM tenable_network
WHERE plugin_id = :target_plugin_id
GROUP BY plugin_id, cve, cvss3_base_score, risk, Name, plugin_family, last_seen, vulnerability_state
ORDER BY plugin_id DESC;

-- 资产明细查询
SELECT
  role_id,
  Host,
  Name,
  Synopsis,
  Port,
  Protocol,
  OS,
  FQDN 
FROM tenable_network
WHERE plugin_id = :target_plugin_id
GROUP BY Host;

2. 封装存储过程（高复用场景） #

如果需要频繁查询不同plugin_id，可以把逻辑封装成存储过程，调用时传入参数：

MySQL示例

DELIMITER //
CREATE PROCEDURE GetVulnerabilityInfo(IN p_plugin_id VARCHAR(20))
BEGIN
  -- 执行漏洞汇总查询
  SELECT
    plugin_id as 'Plugin id',
    cve as CVE,
    cvss3_base_score,
    risk as Risk,
    COUNT(DISTINCT Host) as 'Affected Unique Hosts',
    Name,
    plugin_family as 'Plugin Family',
    last_seen as 'Last Seen',
    vulnerability_state as 'Vulnerability State',
    GROUP_CONCAT(DISTINCT Host SEPARATOR ', ') as 'Affected Hosts'
  FROM tenable_network
  WHERE plugin_id = p_plugin_id
  GROUP BY plugin_id, cve, cvss3_base_score, risk, Name, plugin_family, last_seen, vulnerability_state;

  -- 执行受影响资产明细查询
  SELECT
    role_id,
    Host,
    Name,
    Synopsis,
    Port,
    Protocol,
    OS,
    FQDN 
  FROM tenable_network
  WHERE plugin_id = p_plugin_id
  GROUP BY Host;
END //
DELIMITER ;

-- 调用存储过程，传入目标plugin_id
CALL GetVulnerabilityInfo('90317');

3. 应用层参数化查询（程序调用场景） #

如果在Python/Java等应用中执行SQL，直接使用参数化查询（避免SQL注入），在代码层传递参数：

Python（psycopg2示例）

import psycopg2

target_plugin_id = '90317'
conn = psycopg2.connect("dbname=your_db user=your_user password=your_pwd")
cur = conn.cursor()

# 执行漏洞汇总查询
cur.execute("""
  SELECT
    plugin_id as "Plugin id",
    cve as CVE,
    cvss3_base_score,
    risk as Risk,
    COUNT(DISTINCT Host) as "Affected Unique Hosts",
    Name,
    plugin_family as "Plugin Family",
    last_seen as "Last Seen",
    vulnerability_state as "Vulnerability State",
    STRING_AGG(DISTINCT Host, ', ') as "Affected Hosts"
  FROM tenable_network
  WHERE plugin_id = %s
  GROUP BY plugin_id, cve, cvss3_base_score, risk, Name, plugin_family, last_seen, vulnerability_state
  ORDER BY plugin_id DESC;
""", (target_plugin_id,))

# 执行资产明细查询
cur.execute("""
  SELECT
    role_id,
    Host,
    Name,
    Synopsis,
    Port,
    Protocol,
    OS,
    FQDN 
  FROM tenable_network
  WHERE plugin_id = %s
  GROUP BY Host;
""", (target_plugin_id,))

conn.close()

内容的提问来源于stack exchange，提问作者enkiki