Node sql syntax error

Question

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....

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.

Thank you!

year is a MySQL reserved word. You'll need to escape that column with backticks anywhere you use it. — Aaron Dietz
– Aaron Dietz, Commented Nov 22, 2017 at 19:38
Ah didn't know that. Would probably just be easier to change my column name no? — user7392939
– user7392939, Commented Nov 22, 2017 at 20:42
It will be a lot easier in the long run for sure. dYear is my go-to. — Aaron Dietz
– Aaron Dietz, Commented Nov 22, 2017 at 20:49

Imre_G · Accepted Answer · 2017-11-23 08:43:53Z

1

I think you need two things to fix this code.

1) Aaron Dietz is correct. You need backticks on the year column

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.

edited Nov 23, 2017 at 8:43

answered Nov 22, 2017 at 19:49

Imre_G

2,5251 gold badge20 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

user7392939 Over a year ago

You are right...I just assumed the double quotes would interpret my syntax. But now I have a different problem...some of my values like title and code have spaces in them and its throwing errors. I tried this ("courseData[i].title") but its still just printing the string literal.

Imre_G Over a year ago

You need backticks for those as well in MySQL right? (I'm more of a SQL Server guy) So you need to incorporate the backticks in the string part without changing the variables that you are taking from the JavaScript object. See updated answer.

jcaron Over a year ago

Warning this code is vulnerable to SQL injection attacks. Do NOT concatenate values in your SQL query, use parameterized queries.

Imre_G Over a year ago

You are correct. Was too much focused on the original problem. Updating answer now.

user7392939 Over a year ago

@jcaron you are right, but this is basically throw away code, I only need to get the data inserted and I won't need to do this again.

|

Collectives™ on Stack Overflow

Node sql syntax error

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related