React改用useState传递查询参数致后端冻结问题求助

我有一个基于React、Axios、Express、Node和Oracle SQL的应用。之前前端硬编码查询参数时一切正常，但改成用表单结合useState让参数随用户输入更新后，后端出现冻结，必须重启才能恢复。我多次回滚到可用版本排查，还是没找到原因。

前端代码 #

import { useState, useEffect } from 'react';
import axios from 'axios';

const Query3 = () => {
    const [queryInfo, setQueryInfo] = useState([]);
    const [year1, setYear1] = useState("\"");
    const [year2, setYear2] = useState("\"");
    const [area1, setArea1] = useState("\"");
    const [area2, setArea2] = useState("\"");
    const [itemName, setItemName] = useState("\"");
    const queryId = 'query3';

    // const year1 = '1990';
    // const year2 = '1999';
    // const area1 = 'United States of America';
    // const area2 = 'Europe';
    // const itemName = 'Apples';

    useEffect(() => {
        const loadQueryInfo = async () => {
            setArea1("\"Eurpoe\"");                   // These set methods are placed here for testing purposes only.
            setArea2("\"United States of America\""); // They will eventually go in html that is returned.
            setYear1("\"1990\"");
            setYear2("\"1999\"");
            setItemName("\"Apples\"");
            const response = await axios.get(
                `http://localhost:5000/${queryId}`, 
                {params: {
                    area1,
                    area2,
                    year1,
                    year2,
                    itemName
                }});
            const newQueryInfo = response.data.rows.at(0).at(1);
            setQueryInfo([newQueryInfo]);
        }
        loadQueryInfo();
    }, [area1, area2, itemName, queryId, year1, year2]);

    return (
        <>
        <p>This is the query result (Query 3): {queryInfo} </p>
        </>
    );
}

export default Query3;

后端接口代码 #

app.get('/query3', (req,res) => {
    async function fetchQuery3() {
        try{
            const connection = await oracledb.getConnection();
            
            oracledb.outFormat = oracledb.OUT_FORMAT_ARRAY;
            const area1 = req.query.area1;
            const area2 = req.query.area2;
            const year1 = req.query.year1;
            const year2 = req.query.year2;
            const itemName = req.query.itemName;

            const query = 
            `select a1.a1yr year1,
                    a1.area1 area1,
                    a1.ratio1 area1_ratio,  
                    a2.a2yr year2,
                    a2.area2 area2,
                    a2.ratio2 area2_ratio
             from (
                 select c1.area_name area1, c1.year a1yr, c1.value hectares, value_2 tonnes, c1.value/value_2 ratio1
                 from crop_data c1
                     inner join
                         (select c2.year c2_yr, c2.area_name c2_country, c2.item_name, c2.unit unit_2, c2.value value_2
                         from crop_data c2
                         where c2.year between ${year1} and ${year2}
                         and c2.unit = 'ha'
                         and c2.item_name = '${itemName}'
                         and c2.area_name = '${area1}')
                     on c1.year = c2_yr and c1.area_name = c2_country
                 where c1.year between ${year1} and ${year2}
                 and c1.unit = 'tonnes'
                 and c1.item_name = '${itemName}'
                 and c1.area_name = '${area1}'
             ) a1
             inner join (
                 select c1.area_name area2, c1.year a2yr, c1.value hectares, value_2 tonnes, c1.value/value_2 ratio2
                 from crop_data c1
                     inner join
                         (select c2.year c2_yr, c2.area_name c2_country, c2.item_name, c2.unit unit_2, c2.value value_2
                         from crop_data c2
                         where c2.year between ${year1} and ${year2}
                         and c2.unit = 'ha'
                         and c2.item_name = '${itemName}'
                         and c2.area_name = '${area2}')
                     on c1.year = c2_yr and c1.area_name = c2_country
                 where c1.year between ${year1} and ${year2}
                 and c1.unit = 'tonnes'
                 and c1.item_name = '${itemName}'
                 and c1.area_name = '${area2}'
             ) a2
             on a1.a1yr = a2.a2yr`;

            const result = await connection.execute(query);

            try {
                await connection.close();
            }
            catch (err) {
                console.log("\"Encountered an error closing a connection in the connection pool.\"");
            }
            return result;
            
        } catch (error) {
            return error;
        }

    }
    fetchQuery3().then(dbRes => {
        res.send(dbRes);
    })
    .catch(err => {
        res.send(err);
    })

})

问题根源及修复方案 #

1. 前端无限循环导致请求轰炸 #

问题：useEffect的依赖项包含area1、year1等状态变量，但在useEffect内部又调用setArea1、setYear1修改这些状态，触发新一轮useEffect执行，形成无限循环，短时间内大量请求打向后端，耗尽Oracle连接池资源。

修复：移除useEffect内的测试用setState，或用useRef标记首次渲染避免重复触发，同时添加表单输入逻辑：

import { useState, useEffect, useRef } from 'react';
import axios from 'axios';

const Query3 = () => {
    const [queryInfo, setQueryInfo] = useState([]);
    const [year1, setYear1] = useState("1990");
    const [year2, setYear2] = useState("1999");
    const [area1, setArea1] = useState("Europe");
    const [area2, setArea2] = useState("United States of America");
    const [itemName, setItemName] = useState("Apples");
    const queryId = 'query3';
    const isFirstRender = useRef(true);

    useEffect(() => {
        const loadQueryInfo = async () => {
            // 首次渲染不发起请求，或仅初始化参数
            if (isFirstRender.current) {
                isFirstRender.current = false;
                return;
            }
            try {
                const response = await axios.get(
                    `http://localhost:5000/${queryId}`, 
                    {params: { area1, area2, year1, year2, itemName }}
                );
                const newQueryInfo = response.data.rows.at(0)?.at(1);
                setQueryInfo(newQueryInfo ? [newQueryInfo] : []);
            } catch (err) {
                console.error('请求失败:', err);
            }
        }
        loadQueryInfo();
    }, [area1, area2, itemName, queryId, year1, year2]);

    return (
        <>
            <div className="query-form">
                <input type="text" value={year1} onChange={(e) => setYear1(e.target.value)} placeholder="起始年份" />
                <input type="text" value={year2} onChange={(e) => setYear2(e.target.value)} placeholder="结束年份" />
                <input type="text" value={area1} onChange={(e) => setArea1(e.target.value)} placeholder="区域1" />
                <input type="text" value={area2} onChange={(e) => setArea2(e.target.value)} placeholder="区域2" />
                <input type="text" value={itemName} onChange={(e) => setItemName(e.target.value)} placeholder="物品名称" />
            </div>
            <p>This is the query result (Query 3): {queryInfo} </p>
        </>
    );
}

export default Query3;

2. 后端连接泄漏+SQL注入风险 #

问题：

• 原代码中如果SQL执行出错，会进入catch块但未关闭数据库连接，导致连接占用池资源，最终池满后服务冻结；
• 直接拼接参数到SQL中，不仅存在SQL注入风险，还会因为前端传入带引号的参数（如"Europe"）导致SQL语法错误，加剧连接泄漏。

修复：使用参数化查询，并用try/finally确保连接必关闭：

app.get('/query3', async (req, res) => {
    let connection;
    try{
        connection = await oracledb.getConnection();
        oracledb.outFormat = oracledb.OUT_FORMAT_ARRAY;
        
        const { area1, area2, year1, year2, itemName } = req.query;

        // 用占位符代替直接参数拼接
        const query = 
        `select a1.a1yr year1,
                a1.area1 area1,
                a1.ratio1 area1_ratio,  
                a2.a2yr year2,
                a2.area2 area2,
                a2.ratio2 area2_ratio
         from (
             select c1.area_name area1, c1.year a1yr, c1.value hectares, value_2 tonnes, c1.value/value_2 ratio1
             from crop_data c1
                 inner join
                     (select c2.year c2_yr, c2.area_name c2_country, c2.item_name, c2.unit unit_2, c2.value value_2
                     from crop_data c2
                     where c2.year between :year1 and :year2
                     and c2.unit = 'ha'
                     and c2.item_name = :itemName
                     and c2.area_name = :area1)
                 on c1.year = c2_yr and c1.area_name = c2_country
             where c1.year between :year1 and :year2
             and c1.unit = 'tonnes'
             and c1.item_name = :itemName
             and c1.area_name = :area1
         ) a1
         inner join (
             select c1.area_name area2, c1.year a2yr, c1.value hectares, value_2 tonnes, c1.value/value_2 ratio2
             from crop_data c1
                 inner join
                     (select c2.year c2_yr, c2.area_name c2_country, c2.item_name, c2.unit unit_2, c2.value value_2
                     from crop_data c2
                     where c2.year between :year1 and :year2
                     and c2.unit = 'ha'
                     and c2.item_name = :itemName
                     and c2.area_name = :area2)
                 on c1.year = c2_yr and c1.area_name = c2_country
             where c1.year between :year1 and :year2
             and c1.unit = 'tonnes'
             and c1.item_name = :itemName
             and c1.area_name = :area2
         ) a2
         on a1.a1yr = a2.a2yr`;

        // 传入参数映射，自动处理类型和转义
        const result = await connection.execute(query, {
            year1: parseInt(year1),
            year2: parseInt(year2),
            itemName,
            area1,
            area2
        });

        res.send(result);
    } catch (error) {
        console.error('数据库查询错误:', error);
        res.status(500).send({ error: error.message });
    } finally {
        // 无论成功失败都关闭连接
        if (connection) {
            try {
                await connection.close();
            } catch (err) {
                console.error('关闭连接失败:', err);
            }
        }
    }
})

内容的提问来源于stack exchange，提问作者Brian.H