RequestError: Incorrect syntax near '='. Unable to query to table when values containing equal sign '=',

[aminwafi]

I tried to query something like this below:

const table = 'test';
const user_id = 'abab==';
const input_parameter = 'USER_ID';
const query = `SELECT * FROM [uat].[dbo].[${table}] WITH(NOLOCK) WHERE ${user_id} = @${input_parameter}`;

The table, user_id and input_parameter is dynamic, which referenced from variables.

However, when i try to query, it will return RequestError: Incorrect syntax near '=' error. When i tried with other table and change the variables it works. So I suspect this is causes by the equal sign in the user_id, is there any workaround for this.

[dhensby]

Just like you've enclosed the ${table} with braces, the user_id needs to be enclosed.

const table = 'test';
const user_id = 'abab==';
const input_parameter = 'USER_ID';
-const query = `SELECT * FROM [uat].[dbo].[${table}] WITH(NOLOCK) WHERE ${user_id} = @${input_parameter}`;
+const query = `SELECT * FROM [uat].[dbo].[${table}] WITH(NOLOCK) WHERE [${user_id}] = @${input_parameter}`;

[aminwafi]

But now its giving invalid column name error for all queries,

RequestError: Invalid column name 'abab==' RequestError: Invalid column name '160000007137377767'

[dhensby]

I'm afraid that the issues are for bugs with the library and not query writing support.

I can't help you with your query, especially as I don't have any of the actual context.

Here are docs for database identifiers (column names, etc): https://learn.microsoft.com/en-us/sql/relational-databases/databases/database-identifiers?view=sql-server-ver16

[aminwafi]

Sorry for opening this as an issue, I can't seem to find the workaround for this in docs or any communities :')

I should have write the question better, the variable im passing is in double quote, hence why the solution you gave return that error.

However managed to resolve this, by single quote the user_id variable in query:

const query = `SELECT * FROM [uat].[dbo].[${table}] WITH(NOLOCK) WHERE '${user_id}' = @${input_parameter}`;

Thank you for your help btw! 😄

[dhensby]

I'm not sure that is doing what you expect it to do - that's a literal value comparison. A bit like doing SELECT * FROM table WHERE 1 = 1 - it's not referencing a column.

[aminwafi]

Actually you're right, I tried with other string variable yesterday and it still returns true.

I tried using LIKE operator as below, and it seems giving the same error: SELECT * FROM [uat].[dbo].[${table}] WITH(NOLOCK) WHERE USER_KEY LIKE ${user_id}

RequestError: Incorrect syntax near '='.

Perhaps, is there any other way you can suggest me to try. I tried to remove double quote and assign single quote globally and it still not resolving the issue:

user_id = user_id.replace(/\"/g, "'") // not working
user_id = `'${user_id}'`; // giving literal value comparison

[aminwafi]

I managed to resolve this already, seems like i confused with my own logic to do the query.

It should be:

const table = 'test';
const userId = 'sample==';
const input_parameter = 'USER_ID';
const data = {[input_parameter]: userId }

const query = `SELECT * FROM [uat].[dbo].[${table}] WITH(NOLOCK) WHERE ${input_parameter} = @${input_parameter}`;

let dbRequest = new sql.Request(pool);
for (const key in data) {
      dbRequest.input(key, data[key]);
}

then do the dbRequest.query(query)

now it works as expected, sorry for the confusion :')