Express.JS and Node-MSSQL select error: Invalid Column name on any value that isn't an int

Question

I think the table I'm trying to query isn't normalized (not my dataset). Whenever my where clause includes a value that is not a number I get the error below. It's as if the value of the where clause is confused with the column name itself. Now if I change the column to a field that only contains numbers, it executes without error. Any idea as to why this is happening?

  name: 'ERROR',
  event: 'errorMessage',
  number: 207,
  state: 1,
  class: 16,
  message: "Invalid column name 'somestring'.",
  serverName: 'server1234\\SQLEXPRESS',
  procName: '',
  lineNumber: 1

var express = require('express');
var app = express();

app.get('/', function (req, res) {

 var sql = require("mssql");

 // config for your database
 var config = {
     user: 'username',
     password: 'password',
     server: 'server1234',
     database: 'dbname1234'
 };

 // connect to your database
 sql.connect(config, function (err) {

     if (err) console.log(err);

     // create Request object
     var request = new sql.Request();

     // query to the database and get the records
     request.query('select * from dbo.recordset3 where column4=somestring', function (err, recordset) {

         if (err) console.log(err)

         // send records as a response
         res.send(recordset);

     });
 });
});

var server = app.listen(5000, function () {
 console.log('Server is running..');
});```

You need to put somestring in quotes or mysql thinks you're referencing a column. So your query should be: request.query('select * from dbo.recordset3 where column4="somestring"', function (err, recordset) { — Mark S.
– Mark S., Commented Mar 11, 2020 at 17:45
Thanks Mark! This is what I thought initially. However - I was getting results with ints without using the quotes. For instance, when I used another field that had numerical values (int) it would return the result regardless. The quotes also need to be escaped or it will not work. For record results with datatypes that are not int - it requires escaped quotes. — jake
– jake, Commented Mar 11, 2020 at 18:38

jake · Accepted Answer · 2020-03-11 17:51:11Z

1

Solved! when using where you need to use escaped single quotes.

where column4 = \'somestring\'

answered Mar 11, 2020 at 17:51

jake

111 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Goodlife · Accepted Answer · 2020-10-29 06:41:56Z

0

request.query(('select * from users WHERE username=' + '\'' + req.body.username + '\''), function (err, recordset) {

answered Oct 29, 2020 at 6:41

Goodlife

3,9222 gold badges26 silver badges23 bronze badges

Comments

Swap · Accepted Answer · 2022-03-03 08:19:25Z

0

U should use request.input function to pass values to query to avoid sql injection attempts.

https://www.npmjs.com/package/mssql#input-name-type-value

https://www.npmjs.com/package/mssql#sql-injection

    // create Request object
    var request = new sql.Request();

    //use request.input 
    request.input('column4',sql.VarChar,"somestring");

    // query to the database and get the records
    request.query('select * from dbo.recordset3 where column4=@column4', function (err, recordset) {
        if (err) console.log(err)
             // send records as a response
             res.send(recordset);
         });

answered Mar 3, 2022 at 8:19

Swap

3492 silver badges7 bronze badges

Collectives™ on Stack Overflow

Express.JS and Node-MSSQL select error: Invalid Column name on any value that isn't an int

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related