MySQL and node.js JOIN statement with user input

Question

I have two tables in MySQL workbench which are event and location. I'm trying to join both tables and extract the information from them based on user input. The problem is that I'm getting a MySQL syntax error and I don't know how to solve it.

app.get("/coordinates/:id", (req,res) =>{
    console.log(req.params.id);
    let sql = `SELECT Latitude, Longitude FROM event`
     + ` JOIN location ON location.EventID = event.Id`;
      db.query(sql, (err,result) =>{
    if(err){
        console.log(err.message);
    }
    console.log(result)
    res.status(200).json({
        data: result
     })
   })
})

The following code works fine but I want instead of event.Id I want to substitute it with event.${req.params.id}, but I'm getting this 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 ':4' at line 1 and in all honesty I'm kinda lost as to what to do. Any help would be greatly appreciated!

how you want to join on user selection can you give a example? — Ahmed Yousif
– Ahmed Yousif, Commented Nov 10, 2019 at 17:31

Ahmed Yousif · Accepted Answer · 2019-11-10 17:42:16Z

1

this is because generated statement is wrong syntactically let say that user input is 12 then the statement will be

SELECT Latitude, Longitude FROM event JOIN location ON location.EventID = event.12

so if you want to join on for example 12 so statement should be

SELECT Latitude, Longitude FROM event JOIN location ON location.EventID = 12

so it should be ${req.params.id} instead of event.${req.params.id}

but I think this statement will be wrong semantically may be you should filter after joining as following

SELECT Latitude, Longitude FROM event JOIN location ON location.EventID = event.id where event.id = ${req.params.id}

or according to what do you want to get

edited Nov 10, 2019 at 17:42

answered Nov 10, 2019 at 17:36

Ahmed Yousif

2,3481 gold badge13 silver badges17 bronze badges

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

3 Comments

Tachevv Over a year ago

Hey Ahmed, thanks for the feedback. The filtering after joining the tables worked as expected. Turns out that I had one more mistake when trying to call this route. Now everything works. Cheers!

Ahmed Yousif Over a year ago

great but take care of SQL injection this example may lead to SQL injection issue you need to handle it

Ahmed Yousif Over a year ago

check this link veracode.com/blog/secure-development/…

Collectives™ on Stack Overflow

MySQL and node.js JOIN statement with user input

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related