how can i use express.js variables into sql queries-CodePudding

I am new in node.js I am trying to make a login and registration system using express.js, vanilla JS, CSS, HTML, and MySql.

below is code that handles routing as well as parses HTTP Post requests made by the client which consists of username and password. username and password are stored inside fields.username and fields.password

functions userRegistration(...) and userLogin(...) are defined inside db.js ( shown at the end ); they are exported from db.js and are imported in users.js

users.js

router.post('/register', (req, res, next) => {

  const form = new formidable.IncomingForm();
  form.parse(req, function(err, fields, files) {
    var userInfoArray = [fields.username, fields.password]
    db.userRegistration(userInfoArray)
  })
});

router.post('/login', (req, res, next) => {

  const form = new formidable.IncomingForm();
  form.parse(req, function(err, fields, files) {
    var userInfoArray = [fields.username, fields.password]
    db.userLogin(userInfoArray)
  })
});

I have created userinfo table to store usernames and passwords. As shown below I am getting username and password by HTTP post method. Which I am parsing using formidable module which gives me fields.username and fields.password values. I am using query(...) method to query database providing actual SQL as string named registrationQueryString (to insert an entry into table) and loginQueryString(to find whether provided username and password are in the database or not)

db.js

var username, password;
var userInfoArray = [username, password]
const registrationQueryString = "INSERT INTO (username, password) userinfo VALUES ( ?, ? )"
const loginQueryString = "SELECT (username, password) FROM userinfo WHERE EXISTS (SELECT (username, password) FROM userinfo WHERE username = (?) AND password = (?))"
function userRegistration (userInfoArray){
    dbConnection.query( registrationQueryString, userInfoArray, function(err, results, fields) {
        if (err) throw err
        console.log(results)
    })
}
function userLogin (userInfoArray){
    dbConnection.query( loginQueryString, userInfoArray, function(err, results, fields) {
        if (err) throw err
        console.log(results)
    })
}

It is giving me an error

"wer" is random value I provided as username and password

Error: ER_PARSE_ERROR: 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 '(username, password) userinfo VALUES ( 'wer', 'wer' )' at line 1
    at Query.Sequence._packetToError (F:\dr.server\node_modules\mysql\lib\protocol\sequences\Sequence.js:47:14)
    at Query.ErrorPacket (F:\dr.server\node_modules\mysql\lib\protocol\sequences\Query.js:79:18)
    at Protocol._parsePacket (F:\dr.server\node_modules\mysql\lib\protocol\Protocol.js:291:23)
    at Parser._parsePacket (F:\dr.server\node_modules\mysql\lib\protocol\Parser.js:433:10)
    at Parser.write (F:\dr.server\node_modules\mysql\lib\protocol\Parser.js:43:10)
    at Protocol.write (F:\dr.server\node_modules\mysql\lib\protocol\Protocol.js:38:16)
    at Socket.<anonymous> (F:\dr.server\node_modules\mysql\lib\Connection.js:88:28)
    at Socket.<anonymous> (F:\dr.server\node_modules\mysql\lib\Connection.js:526:10)
    at Socket.emit (events.js:400:28)
    at addChunk (internal/streams/readable.js:290:12)
    --------------------
    at Protocol._enqueue (F:\dr.server\node_modules\mysql\lib\protocol\Protocol.js:144:48)
    at Connection.query (F:\dr.server\node_modules\mysql\lib\Connection.js:198:25)
    at Object.userRegistration (F:\dr.server\db.js:29:18)
    at F:\dr.server\routes\users.js:12:8
    at zalgoSafe (F:\dr.server\node_modules\dezalgo\dezalgo.js:20:10)
    at f (F:\dr.server\node_modules\once\once.js:25:25)
    at IncomingForm.<anonymous> (F:\dr.server\node_modules\formidable\src\Formidable.js:183:9)
    at IncomingForm.emit (events.js:400:28)
    at IncomingForm._maybeEnd (F:\dr.server\node_modules\formidable\src\Formidable.js:612:10)
    at QuerystringParser.<anonymous> (F:\dr.server\node_modules\formidable\src\plugins\querystring.js:36:10) {
  code: 'ER_PARSE_ERROR',
  errno: 1064,
  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 '(username, password) userinfo VALUES ( 'wer', 'wer' )' at line 1",
  sqlState: '42000',
  index: 0,
  sql: "INSERT INTO (username, password) userinfo VALUES ( 'wer', 'wer' )"
}

I know that something is wrong with my SQL queries but I am not able to figure it out. Any help will be welcomed. If this information isn't enough I can give specific code through github.

CodePudding user response：

Not able to add a comment, yet. Have you tried to change your SQL statement to:

INSERT INTO tbl_name (col_name, col_name) VALUES ('value', 'value')

"INSERT INTO userinfo (username, password) VALUES ( 'wer', 'wer' )"

https://dev.mysql.com/doc/refman/8.0/en/insert.html

Edit:

In your post:

It is giving me an error

I know that something is wrong with my SQL queries but I am not able to figure it out.

The code above is not meant to be the answer. As I previously mentioned, not able to comment, yet. The above code is just a pointer to the correct answer. You have to change your MySQL query to the syntax above. The link provided gives you the correct syntax.

CodePudding user response：

"insert into userinfo(username, password) values('wer', 'wer');"

CodePudding user response：

You can use template literals to do so.

var username, password;
const registrationQueryString = `INSERT INTO (username, password) userinfo VALUES ('${username}', '${password}')`

And then use registrationQueryString as a raw query.

Bear in mind this is an ES2015 feature and should be available in any recent version of Node.js. More about template literals at MDN Web Docs

CodePudding user response：

First update this query from

INSERT INTO (username, password) userinfo VALUES ( ?, ? )

INSERT INTO userinfo (username, password) VALUES ( ?, ? )

Then stop using this type of query

SELECT (username, password) FROM userinfo WHERE EXISTS (SELECT (username, password) FROM userinfo WHERE username = (?) AND password = (?))

Replace it with the following

const ObjQr = [
        req.body.username,
        req.body.password
    ];
const SsQl = "CALL SpSelectUser(?,?)";
    query(SsQl, ObjQr, (err,results,fields) => {
        if(err) {
            res.send('ERROR');
        }
        else {
            const Info= {
                UserInfo : results[0]
            };
            res.json(Info);
        }
    });

Try to use StoredProcedure instead of direct query to avoid race condition and Sql-Injection.