0
votes

I have created a normal registration form with following fields: name,email,dob,gender,password i am using express.js and within post route i access it via req.body.(param)

console.log(req.body) shows all params.

Now i have mysql table with same columns as mentioned before. If i do insert with plain strings (eg: abcs) it works i.e. value is inserted into mysql table. Also the email in form is username in db. When I try inserting all the fields it shows error :( unknown username field ). username varchar(50) is its definition. in the logs i see password field's input is surrounded with single quotes but as for email it becomes: ('user@mail'.'com'). I also used knex, alternate ways as mentioned in mysql docs and got same error(unknown username) .

Can someone tell me how should i store email in mysql db via nodejs+express

db.js

var mysql = require('mysql');

// setup mysql
var dbcon = mysql.createConnection({
    host: 'localhost',
    database: 'test',
    user: 'flip',
    password: 'flop'
});

dbcon.connect(function (err) {
    if (err) throw err;
    console.log("Connected to db");
});
module.exports = dbcon;

routes/index.js:

var dbcon = require('../db');
.
.
router.post('/signup', function (req, res) {
    console.log(req.body);
   /* knex('users').insert( {
        user_id: 1,
        password: req.body.su_password,
        u_firstname: req.body.su_firstname,
        u_lastname: req.body.su_lastname,
        u_gender: req.body.su_gender,
        u_dob: req.body.su_date,
    }).then(function(arg){
        console.log(arg);
    });
    */
    dbcon.connect(function (err) {
        var sqlv = {
            user_id: 1,
            password: req.body.su_password,
            u_firstname: req.body.su_firstname,
            u_lastname: req.body.su_lastname,
            u_gender: req.body.su_gender,
            u_dob: req.body.su_date
        };

        /*var sql = "insert into `users` values (?,??,??,??,??,??,??);";
        var sqlv = [1,req.body.su_email, req.body.su_password, req.body.su_firstname, req.body.su_lastname, req.body.su_gender, req.body.su_date];
        sql = mysql.format(sql,sqlv);
        */
        //var sql ="insert into usertmp (`username`,`password`) values ('"+req.body.su_email+"','"+req.body.su_password+"');";
        dbcon.query("insert into users values ? ;", sqlv, function (err, result) {
            if (err) {
                console.log(err);
                throw err;
            }
            console.log("inserted into users " + result);
        });
    });
res.redirect('/');
});

console.log(req.body) in routes/index.js:

{ su_firstname: 'user',
  su_lastname: 'virus',
  su_email: 'user@mail.com',
  su_gender: 'm',
  su_date: '1999-01-01',
  su_password: 'passowrd00' }

Error(for current example):

{ Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'user_id = 1, password = 'passowrd00', u_firstname = 'user', u_lastname =' at line 1

Error(for insert using actual sql query with single quotes around each value):

Unhandled rejection Error: ER_BAD_FIELD_ERROR: Unknown column 'username' in 'field list'

1
Try and use VARCHAR(255) as a default and only restrict that if you have a very compelling reason. For many things, like email addresses, shorter fields can cause huge hassles for your users. - tadman
Could you please possibly share the source code with us ? It is difficult to figure out how you exactly did it by merely reading your question. - nerdier.js
Another tip is to only collect personal information like gender if it's explicitly required by your service. Most of the time it's never used. Date of birth is only really relevant if you need to confirm someone's age. Most people will just put in January 1, 1980 or something because it's really none of your business. - tadman

1 Answers

0
votes

I figured it out ! It wasn't nodejs/expresjs mistake it was from the database. In the database i had used triggers and forgot to use NEW/OLD prefixes.