0

I have this function below:

let username = req.session.username;
let timeoflogin = new Date().toLocaleString();
let device = req.headers['user-agent'];
let thequery = query;
let location = 'n/a';

var insertAnalyticsLogin = "insert into ANALYTICS (username, location, device, query, timeoflogin) VALUES ('" + username + "', '" + location + "', '" + device + "', '" + thequery + "', '" + timeoflogin + "')"
console.log(insertAnalyticsLogin);
mysqlconn.connect(function(err) {
    if (err) {
        console.error('Database connection failed: ' + err.stack);
        return;
    }
    
    mysqlconn.query(insertAnalyticsLogin, function(err, rows) {
        if (err) {
            console.log(err);
        } else {
            console.log("successfully inserted into analytics")
            res.redirect('/gen_trainerclientlist')
        }
        mysqlconn.end();
    });
});

and inside thequery, it holds my query from the previous function. It looks something like this:

"insert into USERS 
            (FIRST_NAME, LAST_NAME, USERNAME, USER_ROLE, REGISTER_DATE, 
            PHONE_NUMBER, SUB_STATUS, ASSIGNED_TRAINER) 
    VALUES ('" + clientDetails.inputFirstName + "', '" + 
            clientDetails.inputLastName + "', '" + 
            clientDetails.inputUsername + "','CLIENT','" + 
            currentDate + "', '" + clientDetails.inputPhoneNumber + 
            "','INACTIVE','" + req.session.username + "')"

the error I am getting with this is :

sqlMessage: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near

by the sounds of it, what is happening is that it is trying to insert thequery again into my database, or it doesn't like the syntax i am providing it with. So i was wondering what the best way to go about this would be. Should i force thequery to be a string, so it forces everything to just get stringified, or am i going about this wrong entierly?

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
roger
  • 192
  • 11
  • 1
    Can you try to remove all the variables and just do a dummy insert. If that works then there will be some issue with quotes – Raj Parekh Jan 05 '22 at 15:45
  • @RajParekh wow yes you were right about the quotes. just tested it out. so how would I go about fixing this? – roger Jan 05 '22 at 15:49
  • 1
    Would be well worth a read to [Avoid SQL Injection](https://stackoverflow.com/questions/15778572/preventing-sql-injection-in-node-js) and a world of pain when someone deleted your database – RiggsFolly Jan 05 '22 at 15:55
  • oh wow okay thank you, appreciate the advice @RiggsFolly – roger Jan 05 '22 at 16:31
  • A piece of advice from the peer learner: try using queries with async/await, especially with knex.js and Objection.js. You will get rid of a huge headache. Good luck. – jkalandarov Jan 07 '22 at 16:26
  • @jkalandarov thanks for the advice, appreciate it :) – roger Jan 07 '22 at 16:45

0 Answers0