NodeJS sessions, cookies and mysql

Question

I'm trying to build an auth system and I have app.js

var express = require('express')
  , MemoryStore = require('express').session.MemoryStore
  , app = express();

app.use(express.cookieParser());
app.use(express.session({ secret: 'keyboard cat', store: new MemoryStore({ reapInterval: 60000 * 10 })}));
app.use(app.router);

and the route.index as

var express = require('express')
  , mysql = require('mysql')
  , crypto = require('crypto')
  , app = module.exports = express();

app.get('/*',function(req,res){
    var url = req.url.split('/');
    if (url[1] == 'favicon.ico')
        return;

    if (!req.session.user) {
        if (url.length == 4 && url[1] == 'login') {     
            var connection = mysql.createConnection({
                host     : 'localhost',
                user     : 'user',
                password : 'pass',
            });
            var result = null;
            connection.connect();
            connection.query('use database');
            var word = url[3];
            var password = crypto.createHash('md5').update(word).digest("hex");
            connection.query('SELECT id,level FROM users WHERE email = "'+url[2]+'" AND password = "'+password+'"', function(err, rows, fields) {
              if (err) throw err;
                for (i in rows) {
                    result = rows[i].level;
                }
                req.session.user = result;
            });
            connection.end();
        }
    }

console.log(req.session.user)

when I access http://mydomain.com/login/user/pass a first time it shows in the last console call but a second time access the cookie is clean

Before you do **anything** else, please read up on [proper SQL escaping](https://github.com/felixge/node-mysql#escaping-query-values). What you're doing here is extremely dangerous. You cannot insert arbitrary user-supplied data into your query string. You should always use safe placeholders like `?` to represent the escaped content. — tadman, Mar 06 '13 at 20:09
Yes, after understanding how cookies and sessions works I will manage to protect from sql injection, but now I'm concentrated in understanding sessions and cookies. Tks for the link. — Gonçalo Silva Dias, Mar 06 '13 at 20:13
I just hope you don't promote this code on to the public internet before you figure that out. — tadman, Mar 06 '13 at 20:50

score 2 · Accepted Answer · edited May 23 '17 at 12:04

Why do you not just use Express's session handling? if you use the express command line tool as express --sessions it will create the project template with session support. From there you can copy the session lines into your current project. There more information in How do sessions work in Express.js with Node.js? (which this looks like it may be a duplicate of)

As for sanitizing your SQL, you seem to be using the library, which will santitize your inputs for your if you use parameterized queries (ie, ? placeholders).

Final thing, you are using Express wrong (no offence). Express's router will let you split alot of your routes (along with allowing you to configure the favicon. See Unable to Change Favicon with Express.js (second answer). Using the '/*' route will just catch all GET requests, which greatly limits what the router can do for you.

(continued from comments; putting it here for code blocks) Now that you have an app with session support, try these two routes:

app.get('/makesession', function (req, res) {
    req.session.message = 'Hello world';
    res.end('Created session with message : Hello world');
});
app.get('/getsession', function (req, res) {
    if (typeof req.session.message == 'undefined') {
        res.end('No session');
    } else {
        res.end('Session message: '+req.session.message);
    }
});

If you navigate in your browser to /makesession, it will set a session message and notify you that it did. Now if you navigate to /getsession, it will send you back the session message if it exists, or else it will tell you that the session does not exist.

I created the app with session support. About the mysql, this app is just for tests and I'm trying to understand how the routes work also. For routing I have "subapp = require('./routes/subapp')" and "app.use(subapp);" where inside that subapp I have the get "/subapp" — Gonçalo Silva Dias, Mar 06 '13 at 20:37
I assume you edited your question since then, as I no longer see you trying to set a cookie. I assume then that it's working now? — Nick Mitchinson, Mar 06 '13 at 20:50
Nick, still the same problem. Now I installed a new app with "express --sessions sessao" and inserted in the routes/index.js the line "res.cookie('session', 'user', 'teste');" and in routes/user.js file the line "console.log(req.session)" and the console.log didn't have the req.session.user value — Gonçalo Silva Dias, Mar 06 '13 at 20:59
Express will handle the cookies for you. Remove the cookie lines. I'm going to add something into the answer. — Nick Mitchinson, Mar 06 '13 at 21:06

score 0 · Answer 2 · answered Mar 06 '13 at 20:18

0

You need to save your cookie value in the response object:

res.cookie('session', 'user', result);

http://expressjs.com/api.html#res.cookie

answered Mar 06 '13 at 20:18

Hector Correa

26,290
8
57
73

Hector I replaced that line with yours and now gives me this error: Error: Can't set headers after they are sent. – Gonçalo Silva Dias Mar 06 '13 at 20:24
You probably already issues are res.send(). The call to set the cookie should be before that call. If I were you I would create a smaller version of your code to understand cookies and Express and then mix the DB access. – Hector Correa Mar 06 '13 at 20:28
I had a res.end() outside the result of the mysql that was causing that error but even when I put inside the mysql result the cookie in the second access is still empty. { cookie: { path: '/', _expires: null, originalMaxAge: null, httpOnly: true } } – Gonçalo Silva Dias Mar 06 '13 at 20:41

NodeJS sessions, cookies and mysql

2 Answers2