current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Join the Stack Overflow Community
Stack Overflow is a community of 6.4 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 0 down vote favorite

I am using pg and node.js. When a user logs in using the auth0 widget I am passing the email it returns and checking my database to see if the user exist. If the user does not exist I am inserting them into the database. I have managed to get this working with a hack job of a function but I would appreciate some help ironing it out.

Problems I am facing.

  1. When checking the database for the email it is not checking it for the whole email address.

    var emailCheck = "SELECT id from public.user WHERE email=" + req.body.email;

    req.body.email; is actually [email protected]

Returns this error,

column "myemail" does not exist

Even though 

[email protected]

does exist.

  1. regardless of the error it throws it moves on to insert the email address. If it does not exist it inserts it. Since there is a Unique Key on the email it throws an error

    duplicate key value violates unique constraint "uk_user_email"

So to fix this my question is why is it not checking for after the @ sign? And what logic should I follow to change this function to run the first query and only run the second if the first query does not find the email address in question?

checkRegister: function(req, res) {
            pool.connect(function(err, client, done) {
                if (err) {
                    return console.error('error fetching client from pool', err);
                } connection
                var emailCheck = "SELECT id from public.user WHERE email=" + req.body.email;
                var emailInsert = "insert into public.user (user_auth_level,email,account_locked,contract) " +
                    "values ('1','" + req.body.email + "','false','false')"
                client.query(emailCheck, function(err, result) {
                    if (err) {
                        return console.error(err.message);
                    }

                });
                client.query(emailInsert, function(err, result) {
                    if (err) {
                        return console.error(err.message);
                    }

                });
                done(); 
            });
            pool.on('error', function(err, client) {
                console.error('idle client error', err.message, err.stack)
            }); 
        }
share|improve this question
up vote 1 down vote accepted

You need to wrap your value with ' to make it string. Without string wrap, it will be come compare between column. It should be: 

var yourQuery = "SELECT id from public.user WHERE email=" + req.body.email; // SELECT id from public.user WHERE [email protected]
var correntQuery = "SELECT id from public.user WHERE email='" + req.body.email + "'"; // SELECT id from public.user WHERE email='[email protected]'

Nodejs is synchronous, your need to use callback or promise to chain your code like below:

checkRegister: function (req, res) {
    pool.connect(function (err, client, done) {
        if (err) {
            console.error(err);
            // should return response error like 
            return res.status(500).send();
        }
        var emailCheck = "SELECT id from public.user WHERE email=$1";
        client.query(emailCheck, [req.body.email], function (err, result) {
            if (err) {
                console.error(err);
                res.status(500).send();
                return done(); // always close connection
            }
            if (result.rowCount > 0) {
                let user = result.rows[0]
                // return your user
                return done(); // always close connection
            } else {
                var emailInsert = "insert into public.user (user_auth_level, email, account_locked, contract) " +
                    "values ('1', $1,'false','false') RETURNING *"
                client.query(emailInsert, [req.body.email], function (err, result) {
                    if (err) {
                        console.error(err);
                        res.status(500).send();
                        return done(); // always close connection
                    } else {
                        if (result.rowCount > 0) {
                            let user = result.rows[0]
                            // return your user
                            return done(); // always close connection
                        }
                    }

                });
            }
        })
    })
    pool.on('error', function (err, client) {
        console.error('idle client error', err.message, err.stack)
    });
}
share|improve this answer
    
thank you. This is super helpful. Above and beyond. – wuno yesterday
    
my another 2 cent, is always better to use prepared statement instead of concat query string for better security and control like auto escape and data type detection. refer to my updated answer above – Simon yesterday
    
Thank you I have been reading about this for 2 hours. – wuno yesterday
    
Would you please explain to me the $1 if I had 5 values to add would it know $1 is thie first variable item and if there was a second just use $2? If not that how does that work? – wuno yesterday
up vote 1 down vote

For #1, the issue is that you aren't quoting your input value. Since (I assume) you probably don't want to worry about quoting / escaping / etc your own values, I would look into using parameterized queries. This will help protect you against sql injection (and your current code is very exposed to that).

For #2, it doesn't actually "throw" that error. That method is asynchronous, and provides the error to the callback. If you want to run the queries "in order", you'll need to do something like this:

client.query(emailCheck, function(err, result) {
  if (err) {
    // should probably do `return done(err);` here
    return console.error(err.message);
  }
  client.query(emailInsert, function(err, result) {
    if (err) {
      // same thing - probably need done(err) in here
      return console.error(err.message);
    }
    return done();
  });
});

Note how the calls are embedded "inside" of each other (inside of the callbacks, specifically).

share|improve this answer
    
Hey THANKS A TON! This has been killing me. I dont understand what you mean by number one. It inserts the full email just fine. Why does not not check for the whole email? I dont see what the difference is between checking to see if it exist and inserting it. – wuno yesterday
    
Oh, in your check query, you aren't putting single quotes ' around your email -- so the query parser of postgres is running into errors. That's where that first error comes from. – dvlsg yesterday

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.