Hey there, let's work through your login auth problems step by step—you've already got the database connection working, so we just need to fix a couple of syntax and promise handling kinks.

1. SQL Syntax Error: Wrong Placeholder Format #

SQL Server (and the mssql library) doesn't use ? for parameterized queries—it relies on named parameters prefixed with @. Your current query uses ? which is why you're seeing that "Incorrect syntax near '?'" error.

Instead of this:

Select * from Admin where username = ? AND password = ?

You need to write your query like this:

SELECT * FROM Admin WHERE username = @uname AND password = @pass

Then you'll bind your input values to these named parameters using the request.input() method—this also adds type safety and protects against SQL injection.

2. Unhandled Promises & Overcomplicated Closures #

Your nested callback structure is making error handling messy, which is causing those UnhandledPromiseRejectionWarning messages. The mssql library supports promises natively, so switching to async/await will make your code way cleaner and easier to debug.

Also, you had a typo in your session config: saveUninitalized should be saveUninitialized (missing an 'i')—that could lead to unexpected session behavior down the line.

Modified Working Code #

Here's the cleaned-up, fixed version of your app.js code:

const sql = require("mssql");
const express = require("express");
const session = require("express-session");
const bodyParser = require("body-parser");
const path = require("path");

const dbconfig = {
  server: "Server",
  database: "Test",
  user: "########",
  password: "####################",
  port: 1433,
  options: { encrypt: false }
};

const app = express();

// Fixed session typo: saveUninitalized → saveUninitialized
app.use(session({ 
  secret: 'Secret', 
  resave: true, 
  saveUninitialized: true 
}));
app.use(bodyParser.urlencoded({ extended: true }));
app.use(bodyParser.json());

app.get('/', function(request, response) {
  response.sendFile(path.join(__dirname + '/login.html'));
});

// Use async function to support await for promise handling
app.post('/auth', async function(request, response) {
  try {
    const { uname, pass } = request.body;
    console.log(uname, pass);

    if (!uname || !pass) {
      return response.send('Please enter Username and Password');
    }

    // Connect to DB with await (properly handles promise resolution)
    const pool = await sql.connect(dbconfig);
    
    // Create request and bind parameters with explicit types
    const dbRequest = pool.request();
    dbRequest.input('uname', sql.VarChar, uname);
    dbRequest.input('pass', sql.VarChar, pass);
    
    // Execute query with await
    const result = await dbRequest.query(`
      SELECT * FROM Admin WHERE username = @uname AND password = @pass
    `);

    if (result.recordset.length > 0) {
      request.session.loggedin = true;
      request.session.username = uname;
      response.redirect('/home');
    } else {
      response.send('Username and/or Password not found');
    }

    // Clean up the pool connection
    await pool.close();
  } catch (err) {
    console.error('Authentication error:', err);
    response.send('An error occurred while logging you in');
  }
});

// Start the server (don't forget this part!)
const PORT = process.env.PORT || 3000;
app.listen(PORT, () => {
  console.log(`Server running on port ${PORT}`);
});

Key Improvements Explained #

• Parameterized Queries: Switched to @ named parameters and request.input() to fix the SQL syntax error and block SQL injection.
• Async/Await: Replaced messy nested callbacks with async/await to properly handle promises, eliminating unhandled rejection warnings.
• Typo Fix: Corrected the session config typo to ensure session behavior works as expected.
• Cleaner Error Handling: A single try/catch block catches all errors (DB connection issues, query failures) and sends a user-friendly response.
• Destructuring: Used const { uname, pass } = request.body for cleaner, more readable variable extraction.

Quick security note: Storing plain-text passwords in your database is a major risk. You should hash passwords with a library like bcrypt before saving them, then compare the hashed password during login instead of checking plain text.

内容的提问来源于stack exchange，提问作者Some Guy With a PC