如何正确传递数据数组至服务器与下载链接？XLSX导出异常排查

需要实现从数据库导出XLSX格式数据的功能，支持用户选择导出列（columns变量），并通过year、station、indicators进行多条件筛选。目前点击下载按钮后，仅能下载包含硬编码表头的空表格，推测需要动态设置表头，但不确定其他问题所在。

相关代码 #

HTML部分 #

<tbody class="table-hover">

<table class="table-fill">
  <thead>
    <tr>
      {% for column in columns %}
      <th>{{ column }}</th>
      {% endfor %}
    </tr>
  </thead>

{% for row in rows %}
    <tr>
      {% for column in columns %}
      <td>{{ row[loop.index0] }}</td>
      {% endfor %}
    </tr>
{% endfor %}
    </tbody>
  </table>
</body>
<a href = "{{url_for('download', columns = request.args.getlist('columns'), year = request.args.getlist('year'), indicators=request.args.getlist('indicators'), station=request.args.getlist('station'))}}">download</a>

app.py部分 #

@app.route("/download", methods=["GET","POST"])
def download():
    if request.method == "GET":
        year = request.form.getlist("year")
        year = [int(i) for i in request.form.getlist("year") if i.isdigit()]
        station = request.form.getlist("station")
        station = [str(i) for i in request.form.getlist("station")]
        indicators = request.form.getlist("indicators")
        indicators = [str(i) for i in request.form.getlist("indicators")]
        columns = request.form.getlist("columns")
        columns = [str(i) for i in request.form.getlist("columns")]
        cur = conn.cursor()
        cur.execute("SELECT {} FROM fact_indicators WHERE fact_year = ANY(%s) AND fact_indicators = ANY(%s) AND fact_station_name = ANY(%s);"
                    .format(",".join(["\"{}\"".format(c) for c in columns])),(year, indicators, station))
        rows = cur.fetchall()
        cur.close
        filename = "fact_T2.xlsx"
        filepath = os.path.join(os.path.dirname(__file__), filename)
        workbook = openpyxl.Workbook()
        worksheet = workbook.active
        worksheet.append([year, station, january, fabruary, may, 1kv, 2kv, 3kv, 4kv])
        for row in rows:
            worksheet.append(list(row))           
        workbook.save(filepath)
        return send_from_directory(os.path.dirname(__file__),  path=filename, as_attachment=True)
    else:
        return redirect(url_for("index"))

1. GET请求参数获取错误 #

下载链接是GET请求，参数通过request.args传递，但后端错误使用request.form.getlist获取参数，导致所有筛选条件和列选择都为空，查不到数据。同时存在重复赋值的冗余代码，需修正：

# 修正参数获取方式
year = [int(i) for i in request.args.getlist("year") if i.isdigit()]
station = request.args.getlist("station")
indicators = request.args.getlist("indicators")
columns = request.args.getlist("columns")

2. 硬编码表头替换为动态表头 #

原代码表头是写死的固定值，需替换为用户选择的columns列表：

# 移除硬编码表头，改用用户选择的columns
worksheet.append(columns)

3. 数据库连接未正确关闭 #

cur.close是方法调用，需加括号才能执行关闭操作：

cur.close()

4. 潜在SQL注入风险 #

直接通过format拼接列名存在注入风险，建议先校验columns是否在允许的列名单中，比如：

# 定义允许导出的列
ALLOWED_COLUMNS = ["fact_year", "fact_station_name", "fact_indicators", "january", "fabruary", "may", "1kv", "2kv", "3kv", "4kv"]
# 过滤非法列
valid_columns = [c for c in columns if c in ALLOWED_COLUMNS]
# 拼接SQL时使用校验后的列
cur.execute("SELECT {} FROM fact_indicators WHERE fact_year = ANY(%s) AND fact_indicators = ANY(%s) AND fact_station_name = ANY(%s);"
            .format(",".join([f'"{c}"' for c in valid_columns])),(year, indicators, station))

5. 文件覆盖问题 #

每次导出使用相同文件名会覆盖之前的文件，建议添加时间戳区分：

from datetime import datetime
# 生成带时间戳的文件名
filename = f"fact_T2_{datetime.now().strftime('%Y%m%d%H%M%S')}.xlsx"

from datetime import datetime
import os
from flask import request, send_from_directory, redirect, url_for
import openpyxl

@app.route("/download", methods=["GET","POST"])
def download():
    if request.method == "GET":
        # 修正：从request.args获取GET请求参数
        year = [int(i) for i in request.args.getlist("year") if i.isdigit()]
        station = request.args.getlist("station")
        indicators = request.args.getlist("indicators")
        columns = request.args.getlist("columns")
        
        # 可选：校验列名合法性，防止SQL注入
        ALLOWED_COLUMNS = ["fact_year", "fact_station_name", "fact_indicators", "january", "fabruary", "may", "1kv", "2kv", "3kv", "4kv"]
        valid_columns = [c for c in columns if c in ALLOWED_COLUMNS]
        if not valid_columns:
            return "请选择合法的导出列", 400
        
        cur = conn.cursor()
        # 使用校验后的列名拼接SQL
        cur.execute("SELECT {} FROM fact_indicators WHERE fact_year = ANY(%s) AND fact_indicators = ANY(%s) AND fact_station_name = ANY(%s);"
                    .format(",".join([f'"{c}"' for c in valid_columns])),
                    (year, indicators, station))
        rows = cur.fetchall()
        # 修正：正确关闭游标
        cur.close()
        
        # 修正：生成带时间戳的文件名，避免覆盖
        filename = f"fact_T2_{datetime.now().strftime('%Y%m%d%H%M%S')}.xlsx"
        filepath = os.path.join(os.path.dirname(__file__), filename)
        
        workbook = openpyxl.Workbook()
        worksheet = workbook.active
        # 修正：使用用户选择的列作为表头
        worksheet.append(valid_columns)
        
        for row in rows:
            worksheet.append(list(row))           
        workbook.save(filepath)
        
        return send_from_directory(os.path.dirname(__file__), path=filename, as_attachment=True)
    else:
        return redirect(url_for("index"))

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