如何修复express POST处理程序中的SQL语法错误

Question

第一次在这里发帖。

每当我发送带有代码中处理程序的post请求时，我都会不断收到以下错误。

Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check that manual that corresponds to your MariaDB server version for the right syntax to use near '1)' at line 1.

app.post('/',function(req,res,next){
var context = {};
pool.query("INSERT INTO workouts (`name`, `reps`, `weight`,`date`, 'lbs') VALUES (?, ?, ?, ?, ?)", [req.body.name, req.body.reps, req.body.weight, req.body.date, req.body.lbs], function(err, result){
if(err){
next(err);
return;
}
context.results = "Inserted id " + result.insertId;
res.render('table', context);
});

我这样做是为了一个课程项目，我不知道问题出在哪里。谢谢你的帮助！

最新消息：显然，我需要更改日期才能进入"；YYYY-MM-DD"；总体安排谢谢你的帮助！

Answer 1

您正在使用什么sql库？

我认为可能有两件事：

1. 您不需要在列名周围加引号

INSERT INTO workouts 
(name, reps, weight, date, lbs) 
VALUES (?, ?, ?, ?, ?)

2. 根据您的sql库，我认为您需要调整替换(? => $1)：

INSERT INTO workouts 
(name, reps, weight, date, lbs) 
VALUES ($1, $2, $3, $4, $5)

Answer 2

我认为你必须用这种方式

pool.query(
'INSERT INTO workouts (name, reps, weight, date, lbs) VALUES($1, $2, $3, $4, $5)',
[req.body.name, req.body.reps, req.body.weight, req.body.date, req.body.lbs],
if(err){
next(err);
return;
}
context.results = "Inserted id " + result.insertId;
res.render('table', context);
});
});

通过这种方式，您已经为Posgres准备好了语句，顺便说一句，通常情况下，将值从请求直接传递到SQL(SQL注入(是错误的方法。出于调试目的，您可以始终打印SQL，然后直接在SQL中尝试，如果此或任何其他SQL库没有返回正确的错误，则会看到确切的错误。

如果你正在使用MySQL，那么应该是这样的

pool.query(
'INSERT INTO workouts SET name = ?, reps =?, weight=?, date=?, lbs=?',
[req.body.name, req.body.reps, req.body.weight, req.body.date, req.body.lbs],
if(err){
next(err);
return;
}
context.results = "Inserted id " + result.insertId;
res.render('table', context);
});
});

Answer 3

显然是MariaDB或MySQL，看起来你在lbs周围有一个普通的单引号。从单引号更改为'

INSERT INTO workouts (`name`, `reps`, `weight`,`date`, **'lbs'**)

回引号`

INSERT INTO workouts (`name`, `reps`, `weight`,`date`, `lbs`)

Answer 4

`INSERT INTO workouts 
(name, reps, weight, xdate) 
VALUES ('${name}', '${reps}', '${weight}', '${xdate}')`