Invalid Object Error when using `node-mssql` due to square brackets missing.

[IAMSolaara]

I'm using mssql version 11.0.1 (could reproduce on 10.0.4 aswell) to do queries against a SQL Server 2022 DB. The tables I'm using have some weird names (which we cannot control) and need to be in square brackets when mentioned in queries.

Expected behaviour:

I expected the square brackets in my query to be preserved. The query holds this statement: (some data omitted in angle brackets)

from dbo.[<COMPANY_NAME_THAT_CONTAINS_SPACES>$Customer$<A_UUID_V4_IDENTIFIER>] x

Actual behaviour:

I get an error: (some data omitted in angle brackets)

    info: ErrorMessageToken {
      name: 'ERROR',
      handlerName: 'onErrorMessage',
      number: 208,
      state: 1,
      class: 16,
      message: "Invalid object name 'dbo.<COMPANY_NAME_THAT_CONTAINS_SPACES>$Customer$<A_UUID_V4_IDENTIFIER>'.",
      serverName: '<DB_NAME>',
      procName: '',
      lineNumber: 1
    }

Configuration:

// paste relevant config here
  const db_config = {
    user: db_username,
    password: db_password,
    server: db_host,
    database: db_database,
    options: {
      encrypt: true,
      trustServerCertificate: true,
    },
  };

Software versions

• NodeJS: v22.2.0
• node-mssql: 11.0.1
• SQL Server: Microsoft SQL Server 2022 (RTM-CU8) (KB5029666) - 16.0.4075.1 (X64)

[dhensby]

I'm going to need to see some actual code to reproduce the problem, because I'm not aware that the system will be re-writing any of your queries if you're including quoted values.

[IAMSolaara]

Hello. I can't post the query but I can post how I'm calling it: in db.ts:

async function get_connection(): Promise<ConnectionPool> {
  // Lettura credenziali da variabili d'ambiente
  const db_username = process.env.DB_USERNAME!;
  const db_password = process.env.DB_PASSWORD!;
  const db_host = process.env.DB_HOST!;
  const db_database = process.env.DB_DATABASE!;

  // Configurazione del database
  const db_config = {
    user: db_username,
    password: db_password,
    server: db_host,
    database: db_database,
    options: {
      encrypt: true,
      trustServerCertificate: true,
    },
  };

  // Connessione al database
  let pool = await sql.connect(db_config);

  return pool;
}

export default get_connection;

In the place I'm calling the query (it's called by a NextJS server component):

  let pool = await get_connection();

  let req = await pool
              .request()
              .input("client_id", client_id)
              .query(query);  //this call throws

[dhensby]

If you can't provide a proof of concept or minimum reproducible code, then I can't help in determining if this is a bug in the library or some other error.

Have you done any debugging to see if the query is making it to the underlying driver in tact?

[IAMSolaara]

I've done a console.log() right before calling .query() and the query is correct at that point, but I don't really know how to debug any further.

[dhensby]

You'll have to start digging deeper into the library's code, using a debugger would be best, but you could pepper the underlying code with log statements too