CODEWITHSUNDEEP
mysqlnode.jsexpress
georgesamper
georgesamper

Reputation: 5169

Using Mysql with Nodejs and Express (node-mysql)

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

Upvotes: 10

Views: 24305

Answers (3)

Geoff Chappell
Geoff Chappell

Reputation: 2442

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

Upvotes: 14

sdepold
sdepold

Reputation: 6231

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

Upvotes: 5

yojimbo87
yojimbo87

Reputation: 68285

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.

Upvotes: 10

Related Questions