how can i use express.js variables into sql queries

Question

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.

sitcomguy · Accepted Answer · 2021-11-12 05:07:27Z

1

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.

edited Nov 12, 2021 at 5:07

answered Nov 12, 2021 at 4:47

sitcomguy

1211 silver badge6 bronze badges

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

4 Comments

user16789156 Over a year ago

you are hardcoding the SQL which will certainly not work in case of login registration process. The whole point of question is to find a way to embed multiple express.js variables into SQL

user16789156 Over a year ago

if you see more precisely in the last line of error you can see the SQL format which caused error. you are sharing the same format.

sitcomguy Over a year ago

@Het Tarkhala if you look closely, the syntax is different. Switch the tbl_name and (col_name, ...)

user16789156 Over a year ago

that was a perfect solution, i didnt understand the documentation (it was too noisy !) thankyou

Zcb991 · Accepted Answer · 2021-11-12 05:12:37Z

0

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

answered Nov 12, 2021 at 5:12

Zcb991

1

Comments

hcotta · Accepted Answer · 2021-11-12 05:19:38Z

0

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

edited Nov 12, 2021 at 5:19

answered Nov 12, 2021 at 5:00

hcotta

4233 silver badges15 bronze badges

Comments

Deep · Accepted Answer · 2021-11-12 08:59:47Z

First update this query from

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

to

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

Please don't use 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.

Collectives™ on Stack Overflow

how can i use express.js variables into sql queries

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related