英文:
pagination query for postgres and nodejs server
问题
我正在尝试通过定义查询来从我的数据库中获取所有用户，使用了 orderby 和 odertype 然后是 limit 和 offset。但在按照给定的方式编写查询时，我使用了参数化形式，但没有返回任何结果。遇到的错误是在 $1 和 $2 附近存在问题。
const getPagination = (page, size) => {
const limit = size ? +size : 4;
const offset = page ? page * limit : 0;
return { limit, offset };
};
var getUsers = async (req, res) => {
let orderby = req.query.orderby
? req.query.orderby
: "user_id";
console.log(orderby);
let orderDir = req.query.orderDir
? req.query.orderDir
: "DESC";
const { page, size } = req.query;
const { limit, offset } = getPagination(page, size);
const query = 'SELECT * FROM users ORDER BY $1 $2 LIMIT $3 OFFSET $4';
await db.query(query, [orderby, orderDir, limit, offset])
.then(data => {
console.log(data)
res.json(data.rows);
})
.catch(err => {
res.status(500).send({
message:
err.message || "Some error occurred while retrieving users."
});
});
}
注意：在查询中，使用参数化查询时，ORDER BY 子句中的列名和排序方向不能作为参数化的位置参数，你需要动态构建 SQL 查询字符串，然后将参数传递给查询。如果你需要更多帮助，请提出具体的问题。
英文:
I am trying to get all the users from my databse by defing the query using orderby and odertype then limit and offset.But while writing the query as given i have used paramertrized form for it is not returning any results.the error that is encountered was there is problem near $1 And$2.
const getPagination = (page, size) => {
const limit = size ? +size : 4;
const offset = page ? page * limit : 0;
return { limit, offset };
};
var getUsers =async(req,res)=>{
let orderby = req.query.orderby
? req.query.orderby
: "user_id";
console.log(orderby)
let orderDir = req.query.orderDir
? req.query.orderDir
: "DESC";
const { page, size } = req.query;
const { limit, offset } = getPagination(page, size);
const query='SELECT * FROM users ORDER BY $1 $2 LIMIT $3 OFFSET $4'
await db.query(query,[orderBy,orderDir,limit,offset])
.then(data => {
console.log(data)
res.json(data.rows);
})
.catch(err => {
res.status(500).send({
message:
err.message || "Some error occurred while retrieving users."
});
});
}
答案1
得分: 0
可能导致错误的原因是orderBy/orderDir被传递为查询字符串而不是原始SQL字符串。这将生成以下查询：
SELECT *
FROM users
ORDER BY 'colName' 'ASC'
现在，orderBy是一个常量字符串，因此条目根本没有被排序，而方向周围的引号确实会引发错误。
将方向插入查询可能最好的方法是将查询中的$2替换为类似于${lower(orderDir) === 'asc' ? 'ASC' : 'DESC'}的内容，并将查询字符串中的s替换为s。
对于要排序的列，最好是定义orderBy可以接受的值，检查是否提供了正确的输入，然后使用if语句创建要插入查询的字符串。只要插入的字符串是在您的代码中创建的，而不是未经处理的用户输入，就没有SQL注入的风险，查询应该按预期工作。
英文:
The probable reason why you're getting an error is because the orderBy/orderDir are passed as strings into the query instead of as raw sql. This will give queries such as
SELECT *
FROM users
ORDER BY 'colName' 'ASC'
Where the order by is now a constant string thus the entries are not ordered at all and the quotes around the direction will indeed throw an error.
Inserting the direction into the query can probably best be done by replacing $2 in the query with something like ${lower(orderDir) === 'asc' ? 'ASC' : 'DESC'} and replacing the the 's of the query string with `s.
For the column to order by it's probably best if you define which values orderBy can take, check if the correct input is given and then use an if statement to create a string to insert into the query instead. As long as the string being inserted is created in your code rather than being unsanitized user input there is no risk of sql injection and the query should work as intended.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论