skip to Main Content

I’m trying to learn Node.js and I’m currently making an Express app (using ejs) that will return values from a MySQL database, depending on the URL the user visits.

For example, visiting http://localhost:3000/test will list all users from the db table users. And the link http://localhost:3000/test/3 will return information about the user with the id number 3.

My code for: /test

app.get("/test", (req, res) => {
    let sql = "SELECT * FROM users ORDER BY name";
    db.query(sql, function (err, results, fields) {
        if (err)
            logmessage(err.message);
        
        res.render("test", { data: results });
    });
});

And here is the code for /test/:id

app.get("/test/:id", (req, res) => {
    var userId = req.params.id;

    let sql = "SELECT * FROM users WHERE id = " + userId;
    db.query(sql, function (err, results, fields) {
        if (err || !results.length) {
            res.redirect("/");
        } else {
            res.render("test_user", { data: results });
        }
    });
});

My question is: is this safe? When I previously worked in PHP development, I used to prepare statements before making any queries.

What happens if a user changes the URL from: http://localhost:3000/test/3 and inserts some SQL injection code at the end of the url? Can the database be breached?

This will be for a live app on the web, so it’s important no SQL injection can be made. I also want to add a form later on (req.body instead of req.params) that I also need to sanitize.

Or is there a built-in "prepared statement" already in Node?

2

Answers


  1. SQL injection is prevented if you use placeholders:

    let sql = "SELECT * FROM users WHERE id = ?";
    db.query(sql, [userId], function (err, results, fields) {...});
    
    Login or Signup to reply.
  2. Have you tried to implement Sequelize? From what I read ORMs prevent SQL injection. Also, it’s pretty easy to use 🙂

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search