使用 Nodejs 和 Postgres 防止 SQL 注入 [英] Prevent SQL Injection with Nodejs and Postgres

问题描述

我正在开发一个后端来与 PostgreSQL 数据库进行交互，并且正在寻找一些帮助来防止 SQL 注入.我了解 SQL 注入的概念，并在网上找到了一些防止这些攻击的示例，但不确定 SQL 提供程序之间的预防技术是否有所不同.

I'm developing a backend to interact with a PostgreSQL database and am looking for some help preventing SQL injection. I understand the concept of SQL injection, and have found some examples online in preventing those attacks, but not sure if prevention techniques differ between SQL providers.

这是我用来查询数据的函数:

This is the function I use to query data:

var pg = require("pg");

var client = new pg.Client(connectionString);
client.connect();

module.exports = async function newQuery(query) {
        var result = await client.query({
        rowMode: 'array',
        text: query
        });
        return result.rows
}

这里有一些使用该函数的标准查询(query()):

And here are some standard queries using that function (query()):

选择

query("SELECT profilename, profiledescription, approved FROM profiledb 
WHERE usercompany='"+ req.query.userCompany +"';").then(data => {
        res.send(data)
    })

更新

query("UPDATE profiledb SET approved='Approved' WHERE id='"+ req.query.id +"';").then(data =>
    res.send(data)
  )

插入

query("INSERT INTO profiledb (profilename, profiledescription, approved) VALUES ('"+ 
req.query.profileTitle +"', '"+ req.query.profileBody +"', 'Pending');");

我可以使用什么代码来查询数据而不会冒 SQL 注入攻击的风险.

What code can I use to query the data without risking SQL injection attack.

谢谢！！！

推荐答案

使用 参数化查询并将您的请求参数作为values 传递.

Use a parameterized query and pass your request arguments as values.

module.exports = async function newQuery(query, values) {
    var result = await client.query({
        rowMode: 'array',
        text: query,
        values
    });
    return result.rows
}

query("SELECT profilename, profiledescription, approved FROM profiledb WHERE usercompany=$1;", [req.query.userCompany]).then(data => {
    res.send(data)
});

query("UPDATE profiledb SET approved='Approved' WHERE id=$1;", [req.query.id]).then(data => {
    res.send(data)
})

query("INSERT INTO profiledb (profilename, profiledescription, approved) VALUES ($1, $2, 'Pending');", [req.query.profileTitle, req.query.profileBody]);

这篇关于使用 Nodejs 和 Postgres 防止 SQL 注入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！