Using Mysql with Nodejs and Express (node-mysql)

Question

Im new to node and express and I have a question regarding using mysql. I have a login form that posts to '/login'. Im using the node-mysql module.

 app.get('/site', function(req, res){
    if (req.session.is_logged_in === true) {
        res.render('site/start', {
            title: 'News'
        });
    } else {
        res.redirect('/');
    }
});

app.post('/login', function(req, res){
    client.query('SELECT id, user_name FROM user WHERE email="' + req.body.login + '" AND password="' + Hash.sha1(req.body.password) + '"',
        function (err, results, fields) {
            if (err) {
                throw err;
            }
            if (results[0]) {
                req.session.userInfo = results[0];
                req.session.is_logged_in = true;
                res.render('site/start', {
                    title: 'News'
                });
            }
            else {
                res.redirect('/');
            }
        }
    );
});

Is this a good way to do it? Can i continue this way? And are the sql querys escaped in some way, or do i have to write that functionality myself?

Last question: Im rewriting a site, and i used the mysql db. Are there any benefits to changing it to mongodb?

Any help would be appreciated

Thanks in advance

George

Answer 1

The node-mysql Client object has an escape method that can help with this. You can either call that manually, or use the form of query that accepts parameters. E.g:

client.query('SELECT id, user_name FROM user WHERE email=?',
    [req.body.login], ...

Note using the parameter method doesn't actually submit a parameterized query to mysql, it just takes care of the parameter substitution and escaping for you.

BTW, here's what the escape does:

https://github.com/felixge/node-mysql/blob/master/lib/protocol/SqlString.js#L3

Answer 2

Is this a good way to do it? Can i continue this way? And are the sql querys escaped in some way, or do i have to write that functionality myself?

You should sanitize your SQL query parameters first. For example by utilizing functionality of node-validator module in order to prevent SQL injection attacks.

I'm rewriting a site, and i used the mysql db. Are there any benefits to changing it to mongodb?

In general it depends on the functionality of your site and other stuff. Try to look at this question.

Answer 3

If your site becomes more complex you might be interested in using an ORM for your MySQL stuff. Sequelize uses the node-mysql lib and manages the complete sql stuff for: http://sequelizejs.com