Node.js query not inserting data into Azure SQL database

Question

Here is my code for my index.js. Now I made an express server to be able to use /register for my creating an account. All the data that I insert into front end is coming over fine.

I'm able to connect and see rows if I use a different code. And it goes to my query database just fine and all the values are fine, but it returns invalid column name and the column name returning is saying it is invalid and is the stuff I input on the front end. So am I doing something wrong with the SQL query in the index? The table is already made. Any help on what I'm doing wrong.

Like if I put test on the username and register it returns Invalid column name 'test'

const express = require("express");
const cors = require("cors");
const app = express();

app.use(express.json({
    type: ['application/json', 'text/plain']
  }));
app.use(cors());

const { Connection, Request } = require("tedious");

// Create connection to database
const config = {
  authentication: {
    options: {
      userName: "username", // update me
      password: "password" // update me
    },
    type: "default"
  },
  server: "dragonfitness.database.windows.net", // update me
  options: {
    database: "dragonfitness", //update me
    encrypt: true
  }
};

const connection = new Connection(config);

// Attempt to connect and execute queries if connection goes through
connection.on("connect", err => {
  if (err) {
    console.error(err.message);
  } else {
    console.log("Server Running and Connected")
  }
});

app.post("/register", (req, res)=> {
    const firstname = req.body.firstname;
    const lastname = req.body.lastname;
    const username = req.body.username;
    const password = req.body.password;
    const email = req.body.email;

    queryDatabase(firstname, lastname, username, password, email);
})

connection.connect();

function queryDatabase(firstname, lastname, username, password, email ) {
  console.log("testing");

  var sql = "INSERT dbo.user_info(first_name, last_name, user_name, password, email) VALUES ("+firstname+", "+lastname+", "+username+", "+password+", "+email+")"
  console.log('query========='+sql)
  // Read all rows from table
  const request = new Request(sql,
    (err, result) => {
      if (err) {
        console.error(err.message);
      } else {
        console.log('worked');
      }
    }
  );
  connection.execSql(request);
}

app.listen(1433, () => {
    console.log("running server");
});

score 0 · Answer 1 · answered Mar 30 '22 at 02:37

Instead of concatenating string values in your sql commands, you should use Parametrized Queries.

See the following example:

function inputParameters() {
  // Values contain variables idicated by '@' sign
  const sql = `INSERT INTO ${table} (uniqueIdCol, intCol, nVarCharCol) VALUES (@uniqueIdVal, @intVal, @nVarCharVal)`;
  const request = new Request(sql, (err, rowCount) => {
    if (err) {
      throw err;
    }

    console.log('rowCount: ', rowCount);
    console.log('input parameters success!');
    outputParameters();
  });

  // Setting values to the variables. Note: first argument matches name of variable above.
  request.addParameter('uniqueIdVal', TYPES.UniqueIdentifier, 'ba46b824-487b-4e7d-8fb9-703acdf954e5');
  request.addParameter('intVal', TYPES.Int, 435);
  request.addParameter('nVarCharVal', TYPES.NVarChar, 'hello world');

  connection.execSql(request);
}

Node.js query not inserting data into Azure SQL database

1 Answers1