"ER_BAD_FIELD_ERROR: Unknown column 'NaN' in 'field list'" upon trying to sign up

[JdyxZ]

Hi all,

I have programmed an app that uses local verification with passport and passport-local modules. I have implemented all the steps following a tutorial on the internet but I'm stuck in the error "ER_BAD_FIELD_ERROR: Unknown column 'NaN' in 'field list" that happens every time the code reaches a done method in my defined LocalStrategy. I use MySQL session to store the session in the database and connect-flash module to store data in global variables (I have read that connect-flash is no longer needed since passport already does the process and I have tried by using the notation {type: smth, message: smth}, but still doesn't work!). Then I use ejs as a view engine with express to render session messages in the views, but when the error prompts, the req.flash() doesn't work at all and global variables don't update. Before submitting the issue, I checked all DATABASE queries are working and checked that the request body is arriving well to routers, so any of these things cannot be the problem. Thank you in advance for your help and hope the fix of this issue helps somebody out there.

Main app js

// Good practice to know my process pid
console.log(`Serving with pid ${process.pid}`);

// External module 
const http = require('http');
const url = require('url');
const express = require('express');
const morgan = require('morgan'); 
const cors = require('cors');
const WebSocketServer = require('websocket').server;
const path = require('path');
const session = require('express-session'); 
const passport = require('passport');
const validator = require('express-validator');
const MySQLSession = require('express-mysql-session')(session);
const ejs = require('ejs'); 
const flash = require('connect-flash');

// Our modules
const SERVER = require("./server.js");
const CREDENTIALS = require("./database/credentials.js");
require('./utils/passport');

// Init server services
SERVER.init();

/***************** EXPRESS JS *****************/

// Create ExpressJS app
const app = express(); // We use ExpressJS to deal with requests, since it allows us to manage request in a simpler way and easily serve files to the client

// App settings
app.set('appName', 'Jabbon');
app.set('port', process.env.PORT || 9014);

// Define session properties
var session_properties = {
  secret: 'JabbonSession',
  resave: false, // avoids overwritting the session
  saveUninitialized: false,
  store: new MySQLSession(CREDENTIALS) // Persistent session
}

// View Engine
app.set('views', path.join(__dirname, 'views'));
app.set('view engine', 'ejs');

// Middleware
app.use(morgan('short')); // To see the request specs
app.use(cors()); // To process cors restrictions
app.use(session(session_properties)); // Initialize session
app.use(flash()); // Allows to easily store data in the session
app.use(passport.initialize());  // Processes signup and login requests
app.use(passport.session()); // Let passport know we are using a session context
app.use(express.urlencoded({extended: false})); // Parses encoded data send with post method through a form
app.use(express.json()); // Parses json data directly to objects

// Global session variables
app.use((req, res, next) =>{
  app.locals.signup_username_error = req.flash('signup_username_error');
  app.locals.signup_password_error = req.flash('signup_password_error');
  app.locals.signup_error = req.flash('signup_error');
  app.locals.login_error = req.flash('login_user_error');
  app.locals.login_error = req.flash('login_error');
  next();
});

// Routers
app.use(require("./routes/routes"));

// Default request folder
app.use(express.static(path.join(__dirname, '../public')));

/***************** HTTP SERVER *****************/

// Create HTTP server
const server = http.createServer(app); // Instead of passing a custom function to manage requests, we pass the express app and let it process the requests for us

// Launch the server
server.listen(app.get('port'), () => SERVER.onReady(app.get('port')));

Routers js

// External modules
const express = require('express');
const passport = require("passport");
const router = express.Router();

// Get routes
router.get('/', (req, res) => {
    res.render("../views/login");
});

router.get('/login', (req, res) => {
    res.render("../views/login");
});

router.get('/signup',  (req, res) => {
    res.render("../views/signup");
});

router.get('/canvas', (req, res) => {
    res.render("../views/canvas");
});

// Post routes
router.post('/signup', passport.authenticate("signup", {
    successRedirect: "/canvas",
    failureRedirect: "/signup",
    failureFlash: true
}));

router.post('/login', passport.authenticate("login", {
    successRedirect: "/canvas",
    failureRedirect: "/login",
    failureFlash: true
}));

// Export module
module.exports = router;

Passport js

// External modules
const passport = require('passport');
const LocalStrategy = require('passport-local').Strategy;

// Our modules
const SERVER = require("../server.js");
const DATABASE = require("../database/database.js");
const CRYPTO = require("./crypto.js");

// Define signup strategy
passport.use('signup', new LocalStrategy(
{
    usernameField: 'name',
    passwordField: 'password',
    passReqToCallback: true
}, 
async (req, name, password, done) => {

    // Check username
    let [status, result] = await DATABASE.validateUsername(name);

    if (status == "ERROR")
    {
        console.log(result);
        return done(null, false, req.flash('signup_error', 'Something wrong happened. Try again.'));
    }

    console.log("here");

    if (result[0].length != 0) return done(null, false, req.flash('signup_username_error', `The username ${name} already exists.`));

    // Check password
    const [check, error] = CRYPTO.check(password);
    if (check == "ERROR") return done(null, false, req.flash('signup_password_error', error));

    // Hash password
    const hashed_password = await CRYPTO.encrypt(password);

    // Push user info into the database
    let user_obj =
    {
        name : name,
        password: hashed_password,
        avatar : "media/images/char1.png",
        room : 1,
        position: 0
    };

    [status, result] = await DATABASE.pushUser(user_obj);

    if (status == "ERROR")
    {
        console.log(result);
        return done(null, false, req.flash('signup_error', 'Something wrong happened. Try again'));
    }

    // Set push query user ID to object and delete password from it
    user_obj.id = result[0].insertId;
    delete user_obj.password;

    // Create new user into the WORLD
    const user = SERVER.world.createUser(user_obj);

    // Pass user id to the serializer
    return done(null, user.id);
}));

// Define signup strategy
passport.use('login', new LocalStrategy(
{
    usernameField: 'name',
    passwordField: 'password',
    passReqToCallback: true
}, 
async (req, name, password, done) => {

    // Hash password
    const hashed_password = await CRYPTO.encrypt(password);  

    // Check user credentials
    let [status, result] = await DATABASE.validateUsername(name, hashed_password);

    if (status == "ERROR")
    {
        console.log(result);
        return done(null, false, req.flash('login_error', 'Something wrong happened. Try again.'));
    }

    if (result[0].length != 0) return done(null, false, req.flash('login_user_error', 'Wrong user or password.'));

    // Pass user id to the serializer
    return done(null, user.id);
}));

// Store user id into the express session
passport.serializeUser((user_id,done) => {
    console.log(user_id);
    done(null, user_id);
});

// Get user id from session
passport.deserializeUser(async (user_id, done) => {
    console.log(user_id);
    const [status, result] = await DATABASE.validateUserID(user_id);

    if(status == "ERROR") return done(result);
    if(result[0].length == 0) return done("ID not valid");

    done(null, result[0].id);
});

Error

::1 - POST /signup HTTP/1.1 302 29 - 25.131 ms
Error: ER_BAD_FIELD_ERROR: Unknown column 'NaN' in 'field list'
    at Sequence._packetToError (C:\University\FOURTH YEAR\ECV\Jabbon\node_modules\mysql\lib\protocol\sequences\Sequence.js:47:14)
    at Query.ErrorPacket (C:\University\FOURTH YEAR\ECV\Jabbon\node_modules\mysql\lib\protocol\sequences\Query.js:79:18)
    at Protocol._parsePacket (C:\University\FOURTH YEAR\ECV\Jabbon\node_modules\mysql\lib\protocol\Protocol.js:291:23)
    at Parser._parsePacket (C:\University\FOURTH YEAR\ECV\Jabbon\node_modules\mysql\lib\protocol\Parser.js:433:10)
    at Parser.write (C:\University\FOURTH YEAR\ECV\Jabbon\node_modules\mysql\lib\protocol\Parser.js:43:10)
    at Protocol.write (C:\University\FOURTH YEAR\ECV\Jabbon\node_modules\mysql\lib\protocol\Protocol.js:38:16)
    at Socket.<anonymous> (C:\University\FOURTH YEAR\ECV\Jabbon\node_modules\mysql\lib\Connection.js:88:28)
    at Socket.<anonymous> (C:\University\FOURTH YEAR\ECV\Jabbon\node_modules\mysql\lib\Connection.js:526:10)
    at Socket.emit (node:events:513:28)
    at addChunk (node:internal/streams/readable:324:12)
    --------------------
    at Pool.query (C:\University\FOURTH YEAR\ECV\Jabbon\node_modules\mysql\lib\Pool.js:199:23)
    at MySQLStore.query (C:\University\FOURTH YEAR\ECV\Jabbon\node_modules\express-mysql-session\index.js:439:33)
    at MySQLStore.set (C:\University\FOURTH YEAR\ECV\Jabbon\node_modules\express-mysql-session\index.js:240:8)
    at Session.save (C:\University\FOURTH YEAR\ECV\Jabbon\node_modules\express-session\session\session.js:72:25)
    at Session.save (C:\University\FOURTH YEAR\ECV\Jabbon\node_modules\express-session\index.js:406:15)
    at ServerResponse.end (C:\University\FOURTH YEAR\ECV\Jabbon\node_modules\express-session\index.js:335:21)
    at ServerResponse.redirect (C:\University\FOURTH YEAR\ECV\Jabbon\node_modules\express\lib\response.js:978:10)
    at allFailed (C:\University\FOURTH YEAR\ECV\Jabbon\node_modules\passport\lib\middleware\authenticate.js:148:20)
    at attempt (C:\University\FOURTH YEAR\ECV\Jabbon\node_modules\passport\lib\middleware\authenticate.js:183:28)
    at strategy.fail (C:\University\FOURTH YEAR\ECV\Jabbon\node_modules\passport\lib\middleware\authenticate.js:305:9)
::1 - GET /signup HTTP/1.1 200 1596 - 7.588 ms
Error: ER_BAD_FIELD_ERROR: Unknown column 'NaN' in 'field list'
    at Sequence._packetToError (C:\University\FOURTH YEAR\ECV\Jabbon\node_modules\mysql\lib\protocol\sequences\Sequence.js:47:14)
    at Query.ErrorPacket (C:\University\FOURTH YEAR\ECV\Jabbon\node_modules\mysql\lib\protocol\sequences\Query.js:79:18)
    at Protocol._parsePacket (C:\University\FOURTH YEAR\ECV\Jabbon\node_modules\mysql\lib\protocol\Protocol.js:291:23)
    at Parser._parsePacket (C:\University\FOURTH YEAR\ECV\Jabbon\node_modules\mysql\lib\protocol\Parser.js:433:10)
    at Parser.write (C:\University\FOURTH YEAR\ECV\Jabbon\node_modules\mysql\lib\protocol\Parser.js:43:10)
    at Protocol.write (C:\University\FOURTH YEAR\ECV\Jabbon\node_modules\mysql\lib\protocol\Protocol.js:38:16)
    at Socket.<anonymous> (C:\University\FOURTH YEAR\ECV\Jabbon\node_modules\mysql\lib\Connection.js:88:28)
    at Socket.<anonymous> (C:\University\FOURTH YEAR\ECV\Jabbon\node_modules\mysql\lib\Connection.js:526:10)
    at Socket.emit (node:events:513:28)
    at addChunk (node:internal/streams/readable:324:12)
    --------------------
    at Pool.query (C:\University\FOURTH YEAR\ECV\Jabbon\node_modules\mysql\lib\Pool.js:199:23)
    at MySQLStore.query (C:\University\FOURTH YEAR\ECV\Jabbon\node_modules\express-mysql-session\index.js:439:33)
    at MySQLStore.set (C:\University\FOURTH YEAR\ECV\Jabbon\node_modules\express-mysql-session\index.js:240:8)
    at Session.save (C:\University\FOURTH YEAR\ECV\Jabbon\node_modules\express-session\session\session.js:72:25)
    at Session.save (C:\University\FOURTH YEAR\ECV\Jabbon\node_modules\express-session\index.js:406:15)
    at ServerResponse.end (C:\University\FOURTH YEAR\ECV\Jabbon\node_modules\express-session\index.js:335:21)
    at ServerResponse.send (C:\University\FOURTH YEAR\ECV\Jabbon\node_modules\express\lib\response.js:232:10)
    at done (C:\University\FOURTH YEAR\ECV\Jabbon\node_modules\express\lib\response.js:1035:10)
    at tryHandleCache (C:\University\FOURTH YEAR\ECV\Jabbon\node_modules\ejs\lib\ejs.js:280:5)
    at exports.renderFile [as engine] (C:\University\FOURTH YEAR\ECV\Jabbon\node_modules\ejs\lib\ejs.js:491:10)

More info about the error

--> (82) ComQueryPacket {
  command: 3,
  sql: 'INSERT INTO `sessions` (`session_id`, `expires`, `data`) VALUES (\'Iib0P8GpNyf0OZF39dnx2jTaJtelf4UM\', NaN, \'{\\"cookie\\":{\\"originalMaxAge\\":null,\\"expires\\":null,\\"httpOnly\\":true,\\"path\\":\\"/\\"},\\"flash\\":{}}\') ON DUPLICATE KEY UPDATE `expires` = VALUES(`expires`), `data` = VALUES(`data`)'
}

<-- (82) ErrorPacket {
  fieldCount: 255,
  errno: 1054,
  sqlStateMarker: '#',
  sqlState: '42S22',
  message: "Unknown column 'NaN' in 'field list'"
}
}

[JdyxZ]

I'm really silly. This is really hard to say...

I have just found the error after a lot of pain and time searching and you are not going to believe it. I overwrote the method getTime() of Date build-in class of Javascript with my implementation because I found it useless. Since express-sql-session uses this method in case no expiration time for the session is defined, this results in an error upon creating and processing a new expiration time. Indeed, here is the problem:

node_modules\express-mysql-session\index.js line 220

// Use whole seconds here; not milliseconds.
expires = Math.round(expires.getTime() / 1000);

data = JSON.stringify(data);

As you see, getTime() is invoked

My Date.getTime() implementation

Date.prototype.getTime2 = function() 
{
    return `${this.getHours().toString().padStart(2,"0")}:${this.getMinutes().toString().padStart(2, "0")}`
};

Moral: Never overwrite built-in methods.