节点sql语法错误 [英] Node sql syntax error
问题描述
我对使用 node 和 express 有点陌生,我正在尝试在我的 node 应用程序中对 mysql 数据库进行简单的插入,但我不断收到 ER_PARSE_ERROR [i].year, courseData[i].term
....
I'm a little new to using node and express and I'm trying to do a simple insert into mysql database in my node app, but I keep getting getting an ER_PARSE_ERROR [i].year, courseData[i].term
....
我正在尝试查看一系列将每个值插入到它们的列名中的课程对象 (courseData).这是我在的地方
I'm trying to just look over an array of course objects (courseData) inserting each value into their column names. Here is where I'm at
for (var i = 0; i < courseData.length; i++){
var i = 1;
let sql = "INSERT INTO Course (year, term, code, title, "
+ "capacity, enrollment, credits, "
+ "mon, tue, wed, thu, fri, sat, "
+ "instructorLast, instructorFirst, "
+ "startDate, endDate, building, room) "
+ "VALUES (courseData[i].year, courseData[i].term, courseData[i].code, "
+ "courseData[i].title, courseData[i].capacity, courseData[i].enrollment, "
+ "courseData[i].credits, courseData[i].mon, courseData[i].tue, "
+ "courseData[i].wed, courseData[i].thu, courseData[i].fri "
+ "courseData[i].sat, courseData[i].instructorLast, courseData[i].instructorFirst, "
+ "courseData[i].startDate, courseData[i].endDate, courseData[i].building, courseData[i].room); ";
db.query(sql, function(err, result, fields){
console.log(err);
});
}
我尝试过单引号、双引号、表名周围的反引号,但没有任何效果.没有看到任何类似的问题,所以我想我会试试运气.
I have tried single quotes, double quotes, back ticks around my table name but nothing worked. Didn't see any similar questions so I thought I would try my luck.
谢谢!
推荐答案
我认为您需要做两件事来修复此代码.
I think you need two things to fix this code.
1) Aaron Dietz 是对的.您需要在年份列上加反引号
1) Aaron Dietz is correct. You need backticks on the year column
2) 目前您插入的是对象名的字符串值而不是对象.例如,您插入courseData[i].title"而不是对象该部分的实际内容.此外,连接 sql 代码会受到 sql 注入攻击.使用此处描述的转义方法 https://github.com/mysqljs/mysql.
2) Currently your inserting the string value of your objectname instead of your object. For example, your inserting "courseData[i].title" instead of the acutal content of that part of the object. Also, concatenating sql code is subject to sql injection attacks. Use the escaping methods as described here https://github.com/mysqljs/mysql.
for (var i = 0; i < courseData.length; i++){
var i = 1;
let sql = `INSERT INTO Course (dyear, term, code, title,
capacity, enrollment, credits,
mon, tue, wed, thu, fri, sat,
instructorLast, instructorFirst,
startDate, endDate, building, room)
VALUES (?, ? , ?, ?, ?, ?, ?, ?, ?,
?,?,?,?,?,?,?,?,?,?)`;
let inserts = [courseData[i].year, courseData[i].term,
courseData[i].code, courseData[i].title,
courseData[i].capacity, courseData[i].enrollment,
courseData[i].credits, courseData[i].mon,
courseData[i].tue, courseData[i].wed,
courseData[i].thu, courseData[i].fri,
courseData[i].sat, courseData[i].instructorLast,
courseData[i].instructorFirst, courseData[i].startDate,
courseData[i].endDate, courseData[i].building,
courseData[i].room];
sql = mysql.format(sql, inserts);
db.query(sql, function(err, result, fields){
console.log(err);
});
}
让我知道这是否适合您.
Let me know if this works for you.
这篇关于节点sql语法错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!