agent-app/rag-server/api/psql/routes.js

const router = require('express').Router();
const bodyParser = require('body-parser')
router.use(bodyParser.urlencoded({
	extended: false
}));

router.use(bodyParser.json({ limit: '10mb' }));

// 跨域参数配置
// const allowCrossDomain = function (req, res, next) {
//     res.header('Access-Control-Allow-Origin', '*');
//     res.header('Access-Control-Allow-Methods', 'GET,PUT,POST,DELETE');
//     res.header('Access-Control-Allow-Headers', 'Content-Type');
//     res.header('Access-Control-Allow-Credentials', 'true');
//     next();
// };
// router.use(allowCrossDomain)

function goWrong(response,msg){
    response.status(500)
    response.json({
        code:500,
        mess:msg
    })
    return
}
const psqlDB = require("../../psql.service");

/**
 * @api {post} /psql/select psql查询接口
 * @apiSampleRequest /api/psql/select
 * @apiVersion 0.5.0
 * @apiName psqlSELECT
 * @apiGroup psql
 * @apiParam {String} sql='SELECT * FROM "VrPanoLog" limit $1;' 仅限SQL的SELECT和WITH查询
 * @apiParam {String} [params='[5]' ] 参数数组
 * @apiSuccess {data} data 结果
 * @apiSuccessExample Success-Response:
 *     HTTP/11 200 OK
 *     {
 *       "code": 200,
 *       "mess": "成功",
 *       "data": [
 * ]
 *     }
 * @example
 curl -X POST 'http://127.0.0.1:1337/api/psql/select' -H 'Content-Type: application/json' --data '{"sql":"SELECT * FROM \"_User\" limit 5","params":[1]}'
 curl -X POST 'https://dev.fmode.cn/api/psql/select' -H 'Content-Type: application/json' --data '{"sql":"SELECT * FROM \"_User\" limit 5","params":[1]}'
 let response = await fetch(`https://dev.fmode.cn/api/psql/select`, {
        headers: {
            "Content-Type": "application/json"
        },
        body: JSON.stringify({"sql":"SELECT * FROM \"_User\" limit 5","params":[1]}),
        method: "POST",
        mode: "cors",
        credentials: "omit"
    });

 let result = await response?.json();
 console.log(result)

 */

router.post('/select', async function (req, response) {

    // 0.接收参数，并且校验
    let sql = req.body.sql;
    let token = req.body.token;
    let params = req.body.params;
    if(!sql){
        goWrong(response,"缺少参数sql")
        return;
    }

    // 2.分析查询参数
    try{
        if(params){
            params = JSON.parse(params)
        }
        if(params&&params.length<1){
            goWrong(response,"params参数必须为数组")
            return;
        }

        let biggerSQL = sql.toUpperCase();
        let checkArray = biggerSQL.split(" ")

        if( // 过滤修改操作
            biggerSQL.startsWith("DROP") 
            || biggerSQL.startsWith("DELETE") 
            || biggerSQL.startsWith("UPDATE") 
            || biggerSQL.startsWith("CREATE") 
            || biggerSQL.startsWith("ALTER") 
            || (checkArray).indexOf("DROP")>=0 
            || (checkArray).indexOf("DELETE")>=0 
            || (checkArray).indexOf("UPDATE")>=0 
            || (checkArray).indexOf("CREATE")>=0 
            || (checkArray).indexOf("ALTER")>=0 
            || (checkArray).indexOf("PG_")>-1  // 屏蔽系统方法
            || (checkArray).indexOf("/ETC")>-1  // 屏蔽目录读取
            || (checkArray).indexOf("GETPGUSERNAME")>-1 
            || (checkArray).indexOf("CURRENT_SCHEMA")>-1 
            || (checkArray).indexOf("CURRENT_USER")>-1 
            || (checkArray).indexOf("SYSTEM(")>-1 
        ){
            goWrong(response, "不可执行非法查询语句："+sql);
            return;
        }

        let badStringList = [
            "exec","execute","insert","create","drop","grant","use","group_concat","column_name","concat","pg_read_file","information_schema.columns","table_schema","delete","update","chr","mid","master","truncate","char","declare"
            // ,";","--","//","/","#"
        ]
        if(badStringList.find(item=>checkArray.indexOf(item.toUpperCase())>-1)){
            goWrong(response, "包含非法关键字："+sql);
            return;
        }

        if( // 限定查询操作
            biggerSQL.startsWith("WITH") 
            || biggerSQL.startsWith("SELECT")
            || biggerSQL.startsWith("(SELECT")
            ){
                let data = await NovaQLSELECT(sql,params);
                response.json({
                    code:200,
                    data:data
                });
                return;
        }else{
            goWrong(response, "不可执行非法查询语句："+sql);
            return;
        }
    }catch(err){
        goWrong(response, err.toString());
        return;
    }
})

async function NovaQLSELECT(sql,params){
    return new Promise((resolve,reject)=>{
        psqlDB.any(sql,params).then((data) => {
            resolve(data)
        })
        .catch((err) => {
            reject(err);
        });
    })
}

module.exports = router;