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
SQL injection is prevented if you use placeholders:
Have you tried to implement Sequelize? From what I read ORMs prevent SQL injection. Also, it’s pretty easy to use 🙂