title: Unable to Set Variable from within ExpressJS res.locals.connection.query() statement
body: <p>I am attempting to create a Single Sign-On feature that checks if a user is already provisioned, and either Creates a user or updates the database with the latest information.</p>
  
  <p>I have gotten the update/insert working, but now I am unable to return the users id (uid or UserID), from the query.</p>
  
  <p>I have left several of the things I have tried commented in the section.</p>
  
  <p>Basically, the query that does the insert/update is nested inside of another, which is inside of the post_assert. I need the value of iresults.insertId for users that were just provisioned and the result of results[0].nid from the first level query to be saved into the 'cookieData' array.</p>
  
  <p>Right now, I'm getting the rest of the cookie (firstName, lastName, username, email, wwwid, country and geo, but for the life of me cannot get the cookieData.userid property to be added to it before it gets sent back to the browser.</p>
  
  <p>This is an expressJS REST API that sends data back to a VueJS Application.</p>
  
  <p>I have tried callbacks as well as global variables, but nothing seems to add the new value to the Array.</p>
  
  <p><div class="snippet" data-lang="js" data-hide="false" data-console="false" data-babel="false">
  <div class="snippet-code">
  <pre class="snippet-code-js lang-js prettyprint-override"><code>sp.post_assert(idp, options, function (err, saml_response) {
          if (err)
              res.redirect('https://www.example.com/');
          var sessionID = saml_response.response_header.id;
          setCookie(res, req, sessionID);
          var refererLocation = req.cookies.referLocation;
          // Set User Data Variables
          const firstName = saml_response.user.attributes.givenName;
          const middleName = null;
          const lastName = saml_response.user.attributes.sn;
          const username = saml_response.user.attributes.uid;
          const email = saml_response.user.attributes.mail;
          const wwid = saml_response.user.attributes.employeeID;
          const country = saml_response.user.attributes.country;
          const geo = saml_response.user.attributes.geographicRegion;
          /*function setCookieData(val) {
            cookieData.userid = val;
          }*/
          // let userid;
          // Check if user exists in DB
          res.locals.connection.query('SELECT * FROM users WHERE username = ?', [username], function (error, results, fields) {
            if (error) throw error;
            // Get the Current Date-Time for insertion
            const accessDateTime = new Date();
            const adtYear = accessDateTime.getFullYear();
            const adtMonth = accessDateTime.getMonth() + 1;
            const adtDay = accessDateTime.getDate();
            const adtHour = accessDateTime.getHours();
            const adtMin = accessDateTime.getMinutes();
            const adtSec = accessDateTime.getSeconds();
            const dts = `${adtYear}-${adtMonth}-${adtDay} ${adtHour}:${adtMin}:${adtSec}`;
            // let userid;
            // If results is empty, then the user who just logged in does not currently have
            // an account provisioned, so set them up an account.
            if (!(results.hasOwnProperty(0))) {
              res.locals.connection.query('INSERT INTO users SET ?', {first_name: firstName, middle_name: middleName, last_name: lastName, username: username, email: email, status: 1, created: dts, access: dts, login: dts}, function (ierror, iresults, ifields){
                if (ierror) throw ierror;
                // Set the User Data Cookie
                // res.locals.userid = iresults.insertId;
                // setUserIdValue(iresults[0].insertId);
                // res.clearCookie('UserInfo');
                // res.cookie('UserInfo', cookieData);
                // cookieData.userid=iresults[0].insertId;
                // res.cookie('UserInfo', cookieData);
                app.locals.userid = iresults[0].insertId;
              });
            // Else, the result was NOT empty, then the user already exists in the DB,
            // so just update their Access and Login DATETIME fields.
            } else {
              res.locals.connection.query('UPDATE users SET login = ?, access = ? WHERE uid = ?', [dts, dts, results[0].uid], function (ierror, iresults, ifields){
                if (ierror) throw ierror;
                // userid = results[0].uid;
                // Set the User Data Cookie
                // res.locals.userid = results[0].uid;
                // setUserIdValue(results[0].uid);
                // res.clearCookie('UserInfo');
                // res.cookie('UserInfo', cookieData);
                // cookieData.userid=results[0].uid;
                // res.cookie('UserInfo', cookieData);
                app.locals.userid = results[0].uid;
              });
            }
          });
          const cookieData = {
            firstName: firstName,
            lastName: lastName,
            username: username,
            email: email,
            wwid: wwid,
            country: country,
            geo: geo,
            userid: app.locals.userid,
          };
          res.cookie('UserInfo', cookieData);
          //Add saml ID to database with expiration date
          if (refererLocation != undefined) {
              res.clearCookie("referLocation");
              res.redirect('https://www.example.com' + refererLocation);
          } else {
              res.redirect('https://www.example.com/uri');
          }
          // Save name_id and session_index for logout
          // Note:  In practice these should be saved in the user session, not globally.
          // name_id = saml_response.user.givenName + "," + saml_response.sn;
          // session_index = saml_response.user.session_index;
          //var first = saml_response.user.attributes.givenName;
          //var last = saml_response.user.attributes.sn;
          //res.send("Hello, " + first + " " + last);
      });</code></pre>
  </div>
  </div>
  </p>
  
  <h1>EDIT (9-11-2018)</h1>
  
  <p>I have broken it down to it's most simplest concept, and am still unable to get it to return the 'userid' value into the cookie. Even after setting everything to ASYNC and AWAIT.</p>
  
  <p><div class="snippet" data-lang="js" data-hide="false" data-console="false" data-babel="false">
  <div class="snippet-code">
  <pre class="snippet-code-js lang-js prettyprint-override"><code>app.post('/api/v1/saml/acs', function (req, res) {
      const options = {
          request_body: req.body,
          allow_unencrypted_assertion: true
      };
      sp.post_assert(idp, options, async function (err, saml_response) {
          if (err)
              res.redirect('https://www.example.com/');
          const sessionID = saml_response.response_header.id;
          const user = saml_response.user.attributes;
          // Set User Data Variables
          const firstName = user.givenName;
          const middleName = null;
          const lastName = user.sn;
          const username = user.uid;
          const email = user.mail;
          const wwid = user.employeeID;
          const country = user.country;
          const geo = user.geographicRegion;
          app.locals.UserData = {
            firstName: firstName,
            lastName: lastName,
            username: username,
            email: email,
            wwid: wwid,
            country: country,
            geo: geo,
            // userid: '',
          };
          // Check if user exists in DB
          await res.locals.connection.query('SELECT * FROM users WHERE username = ?', [app.locals.UserData.username], async function (error, results, fields) {
            if (error) throw error;
            // Get the Current Date-Time for insertion
            const accessDateTime = new Date();
            const adtYear = accessDateTime.getFullYear();
            const adtMonth = accessDateTime.getMonth() + 1;
            const adtDay = accessDateTime.getDate();
            const adtHour = accessDateTime.getHours();
            const adtMin = accessDateTime.getMinutes();
            const adtSec = accessDateTime.getSeconds();
            const dts = `${adtYear}-${adtMonth}-${adtDay} ${adtHour}:${adtMin}:${adtSec}`;
            // If results is empty, then the user who just logged in does not currently have
            // an account provisioned, so set them up an account.
            if (!(results.hasOwnProperty(0))) {
              await res.locals.connection.query('INSERT INTO users SET ?', {first_name: app.locals.UserData.firstName, middle_name: app.locals.UserData.middleName, last_name: app.locals.UserData.lastName, username: app.locals.UserData.username, email: app.locals.UserData.email, status: 1, created: dts, access: dts, login: dts}, async function (ierror, iresults, ifields){
                if (ierror) throw ierror;
                app.locals.UserData.userid = 22;
                // app.locals.UserData.userid = iresults.insertID;
              });
            } else {
              await res.locals.connection.query('UPDATE users SET login = ?, access = ? WHERE uid = ?', [dts, dts, results[0].uid], async function (ierror, iresults, ifields){
                if (ierror) throw ierror;
                app.locals.UserData.userid = 44;
                // app.locals.UserData.userid = results[0].uid;
              });
            }
          });
          // Set User Session Cookie
          res.cookie('UserData', app.locals.UserData);
          // Set SAML Session Cookie
          setCookie(res, req, sessionID);
          // Get the referrer location
          var refererLocation = req.cookies.referLocation;
          // If it is undefined, then send the user back to where they started the Sign On process.
          if (refererLocation != undefined) {
              res.clearCookie("referLocation");
              res.redirect('https://www.example.com' + refererLocation);
          } else {
              res.redirect('https://www.example.com/uri');
          }
      });
  });</code></pre>
  </div>
  </div>
  </p>
  
  <h1>Response to dmfay</h1>
  
  <p>I believe I have followed your advice, but it is still not returning the 'userid' property.</p>
  
  <p>I have added the 'next' parameter to</p>
  
  <pre><code>app.post('/api/va/saml/acs', function (){...});
  </code></pre>
  
  <p>and added a 'next()' call at the end of</p>
  
  <pre><code>sp.post_assert(idp, options, async function(...){
      // logic here with nested calls trying to update app.locals.UserData
      next();
  });
  </code></pre>
  
  <p>I have also tried the 'next()' call outside of the post_assert, and I get this message:</p>
  
  <pre><code>Error [ERR_HTTP_HEADERS_SENT]: Cannot set headers after they are sent to the client
  </code></pre>
  
  <p>See below for the updated code.</p>
  
  <p><div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
  <div class="snippet-code">
  <pre class="snippet-code-js lang-js prettyprint-override"><code>app.post('/api/v1/saml/acs', function (req, res, next) {
      const options = {
          request_body: req.body,
          allow_unencrypted_assertion: true
      };
      sp.post_assert(idp, options, async function (err, saml_response) {
          if (err)
              res.redirect('https://www.example.com/');
          const sessionID = saml_response.response_header.id;
          const user = saml_response.user.attributes;
          // Set User Data Variables
          const firstName = user.givenName;
          const middleName = null;
          const lastName = user.sn;
          const username = user.uid;
          const email = user.mail;
          const wwid = user.employeeID;
          const country = user.country;
          const geo = user.geographicRegion;
          app.locals.UserData = {
            firstName: firstName,
            lastName: lastName,
            username: username,
            email: email,
            wwid: wwid,
            country: country,
            geo: geo,
            // userid: '',
          };
          // Check if user exists in DB
          await res.locals.connection.query('SELECT * FROM users WHERE username = ?', [app.locals.UserData.username], async function (error, results, fields) {
            if (error) throw error;
            // Get the Current Date-Time for insertion
            const accessDateTime = new Date();
            const adtYear = accessDateTime.getFullYear();
            const adtMonth = accessDateTime.getMonth() + 1;
            const adtDay = accessDateTime.getDate();
            const adtHour = accessDateTime.getHours();
            const adtMin = accessDateTime.getMinutes();
            const adtSec = accessDateTime.getSeconds();
            const dts = `${adtYear}-${adtMonth}-${adtDay} ${adtHour}:${adtMin}:${adtSec}`;
            // If results is empty, then the user who just logged in does not currently have
            // an account provisioned, so set them up an account.
            if (!(results.hasOwnProperty(0))) {
              await res.locals.connection.query('INSERT INTO users SET ?', {first_name: app.locals.UserData.firstName, middle_name: app.locals.UserData.middleName, last_name: app.locals.UserData.lastName, username: app.locals.UserData.username, email: app.locals.UserData.email, status: 1, created: dts, access: dts, login: dts}, async function (ierror, iresults, ifields){
                if (ierror) throw ierror;
                app.locals.UserData.userid = 22;
                // app.locals.UserData.userid = iresults.insertID;
              });
            } else {
              await res.locals.connection.query('UPDATE users SET login = ?, access = ? WHERE uid = ?', [dts, dts, results[0].uid], async function (ierror, iresults, ifields){
                if (ierror) throw ierror;
                app.locals.UserData.userid = 44;
                // app.locals.UserData.userid = results[0].uid;
              });
            }
          });
          // Set User Session Cookie
          res.cookie('UserData', app.locals.UserData);
          // Set SAML Session Cookie
          setCookie(res, req, sessionID);
          // Get the referrer location
          var refererLocation = req.cookies.referLocation;
          // If it is undefined, then send the user back to where they started the Sign On process.
          if (refererLocation != undefined) {
              res.clearCookie("referLocation");
              res.redirect('https://www.example.com' + refererLocation);
          } else {
              res.redirect('https://www.example.com/uri');
          }
          next();
      });
  });</code></pre>
  </div>
  </div>
  </p>
  
  <h1>Working Code</h1>
  
  <p>Thank you DMFAY and BennetQuigley. This issue has been resolved.</p>
  
  <p>The working solution is commented below.</p>
  
  <p><div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
  <div class="snippet-code">
  <pre class="snippet-code-js lang-js prettyprint-override"><code>app.post('/api/v1/saml/acs', function (req, res, next) {
      const options = {
          request_body: req.body,
          allow_unencrypted_assertion: true
      };
      sp.post_assert(idp, options, function (err, saml_response) {
          if (err)
              res.redirect('https://www.example.com/');
          // Get the Sessions ID
          const sessionID = saml_response.response_header.id;
          // Set the returned User Info to a Variable
          const user = saml_response.user.attributes;
          // Set the app.locals.UserData variable
          app.locals.UserData = {
            firstName: user.givenName,
            middleName: null,
            lastName: user.sn,
            username: user.uid,
            email: user.mail,
            wwid: user.employeeID,
            country: user.country,
            geo: user.geographicRegion,
          };
          // Check if user exists in DB
          res.locals.connection.query('SELECT * FROM users WHERE username = ?', [app.locals.UserData.username], function (error, results, fields) {
            if (error) throw error;
            // Get the Current Date-Time for Insert/Update of user logon history
            const accessDateTime = new Date();
            const adtYear = accessDateTime.getFullYear();
            const adtMonth = accessDateTime.getMonth() + 1;
            const adtDay = accessDateTime.getDate();
            const adtHour = accessDateTime.getHours();
            const adtMin = accessDateTime.getMinutes();
            const adtSec = accessDateTime.getSeconds();
            const dts = `${adtYear}-${adtMonth}-${adtDay} ${adtHour}:${adtMin}:${adtSec}`;
            // If results is empty, then the user who just logged in does not currently have
            // an account provisioned, so set them up an account.
            if (!(results.hasOwnProperty(0))) {
              res.locals.connection.query('INSERT INTO users SET ?', {
                first_name: app.locals.UserData.firstName,
                middle_name: app.locals.UserData.middleName,
                last_name: app.locals.UserData.lastName,
                username: app.locals.UserData.username,
                email: app.locals.UserData.email,
                status: 1,
                created: dts,
                access: dts,
                login: dts
              }, function (ierror, iresults, ifields){
                if (ierror) throw ierror;
                // set the app.locals.UserData.userid value to the newly inserted ID
                app.locals.UserData.userid = `["${iresults.insertID}"]`;
                // From here to 'next()' has to be repeated in both cases
                // 'next()' must be used to return the values and actions to the parent
                // Call.
                // Set User Session Cookie
                res.cookie('UserData', app.locals.UserData);
                // Set SAML Session Cookie
                setCookie(res, req, sessionID);
                // Get the referrer location
                var refererLocation = req.cookies.referLocation;
                // If it is undefined, then send the user back to where they started the Sign On process.
                if (refererLocation != undefined) {
                    res.clearCookie("referLocation");
                    res.redirect('https://clpstaging.mcafee.com' + refererLocation);
                } else {
                    res.redirect('https://clpstaging.mcafee.com/clp');
                }
                // Tell the callback to move forward with the actions.
                next();
              });
            } else {
              res.locals.connection.query('UPDATE users SET login = ?, access = ? WHERE uid = ?', [dts, dts, results[0].uid], function (ierror, iresults, ifields){
                if (ierror) throw ierror;
                // Set the app.locals.UserData.userid to the Users PK
                app.locals.UserData.userid = results[0].uid;
                // From here to 'next()' has to be repeated in both cases
                // 'next()' must be used to return the values and actions to the parent
                // Call.
                // Set User Session Cookie
                res.cookie('UserData', app.locals.UserData);
                // Set SAML Session Cookie
                setCookie(res, req, sessionID);
                // Get the referrer location
                var refererLocation = req.cookies.referLocation;
                // If it is undefined, then send the user back to where they started the Sign On process.
                if (refererLocation != undefined) {
                    res.clearCookie("referLocation");
                    res.redirect('https://www.example.com' + refererLocation);
                } else {
                    res.redirect('https://www.example.com/uri');
                }
                // Tell the callback to move forward with the actions.
                next();
              });
            }
          });
      });
  });</code></pre>
  </div>
  </div>
  </p>