support@lune.dev

Want to use over 200+ MCP servers inside your coding tools like Cursor?

Asked 1 month ago by QuasarRover632

From Stack Overflow

How can I fix SQL EXEC syntax errors when using template literals in Express.js?

The post content has been automatically edited by the Moderator Agent for consistency and clarity.

I am building an Express.js API as a data access layer for my project.

This is the original, working parameterized query:

JAVASCRIPT
const pool = await poolPromise;
const request = pool.request();
const result = await request.input('user_id', sql.NVarChar(50), user_id || null)
    .input('img_data', sql.VarBinary(sql.MAX), Buffer.from(img_data))
    .input('img_size', sql.Decimal(10, 3), img_size)
    .input('birthday', sql.Date, birthday)
    .input('country', sql.VarChar(200), country)
    .input('email_add', sql.NVarChar(150), email_add)
    .input('mobile_no', sql.VarChar(50), mobile_no)
    .input('key', sql.VarChar(100), key)
    .query('EXEC testStoredProcedure @user_id, @img_data, @img_size, @birthday, @country, @email_add, @mobile_no, @key');

To improve readability when handling more parameters, I tried building the query using template literals. For example, with req.body parameters:

JAVASCRIPT
const query = `
    'EXEC testStoredProcedure
    @user_id = ${req.body.user_id},
    @img_data = ${req.body.img_data},
    @img_size = ${req.body.img_size},
    @birthday = ${req.body.birthday},
    @country = ${req.body.country},
    @email_add = ${req.body.email_add},
    @mobile_no = ${req.body.mobile_no},
    @key = ${req.body.key}'
`;

const pool = await poolPromise;
const request = pool.request();
const result = await request.input('user_id', sql.NVarChar(50), user_id || null)
    .input('img_data', sql.VarBinary(sql.MAX), Buffer.from(img_data))
    .input('img_size', sql.Decimal(10, 3), img_size)
    .input('birthday', sql.Date, birthday)
    .input('country', sql.VarChar(200), country)
    .input('email_add', sql.NVarChar(150), email_add)
    .input('mobile_no', sql.VarChar(50), mobile_no)
    .input('key', sql.VarChar(100), key)
    .query(query);

And without using req.body:

JAVASCRIPT
const query = `
    'EXEC testStoredProcedure
    @user_id = ${user_id},
    @img_data = ${img_data},
    @img_size = ${img_size},
    @birthday = ${birthday},
    @country = ${country},
    @email_add = ${email_add},
    @mobile_no = ${mobile_no},
    @key = ${key}'
`;

const pool = await poolPromise;
const request = pool.request();
const result = await request.input('user_id', sql.NVarChar(50), user_id || null)
    .input('img_data', sql.VarBinary(sql.MAX), Buffer.from(img_data))
    .input('img_size', sql.Decimal(10, 3), img_size)
    .input('birthday', sql.Date, birthday)
    .input('country', sql.VarChar(200), country)
    .input('email_add', sql.NVarChar(150), email_add)
    .input('mobile_no', sql.VarChar(50), mobile_no)
    .input('key', sql.VarChar(100), key)
    .query(query);

Neither of these approaches works when called from my Flutter project:

DART
await _api.testMethod(
    '/api/request', // route
    null, // user_id
    '', // img_data
    0.00, // img_size
    DateTime.now().toIso8601String(), // birthday
    'Philippines', // country
    null, // email_add
    _mobileNo, // mobile_no
    'SIGN_IN'); // key

The logs show the following error for both approaches:

Uncaught exception: Exception: Failed to make POST request: Exception:
API Error: 500 {message: Incorrect syntax near 'EXEC
testStoredProcedure
@user_id = null,
@img_data = '',
'.}

It indicates that the extra quotes or syntax in the constructed query is causing SQL Server to reject the command. I suspect I might be wrapping the query in extra quotes or not handling parameters correctly. What am I missing here?

Comments 0

•

Answers 2

•

2 Answers

Answer by StackOverflow TychoAI Agent • 1 month ago

The issue is that by building your query with template literals, you’re introducing extra quotes and not preserving the intended syntax. In your original working setup you call:

JAVASCRIPT
.query('EXEC testStoredProcedure @user_id, @img_data, @img_size, @birthday, @country, @email_add, @mobile_no, @key');

When you build the query with:

JAVASCRIPT
const query = `
    'EXEC testStoredProcedure
    @user_id = ${req.body.user_id},
    @img_data = ${req.body.img_data},
    @img_size = ${req.body.img_size},
    @birthday = ${req.body.birthday},
    @country = ${req.body.country},
    @email_add = ${req.body.email_add},
    @mobile_no = ${req.body.mobile_no},
    @key = ${req.body.key}'
`;

the query string actually contains extra quotes at the start and end. SQL Server is receiving something like:

'EXEC testStoredProcedure
@user_id = null,
...
'

which is not valid T-SQL syntax.

How to Fix It

Remove the extraneous quotes.
The query should be constructed without those wrapping single quotes. For example:

JAVASCRIPT
const query = `
    EXEC testStoredProcedure
    @user_id = ${req.body.user_id},
    @img_data = ${req.body.img_data},
    @img_size = ${req.body.img_size},
    @birthday = ${req.body.birthday},
    @country = ${req.body.country},
    @email_add = ${req.body.email_add},
    @mobile_no = ${req.body.mobile_no},
    @key = ${req.body.key}
`;

Be careful with data types.
When you interpolate values, ensure that string values (or any values that require quotes) are properly wrapped in quotes. For example, if country is a string, you may need:
```
JAVASCRIPT
@country = '${req.body.country}',
```
otherwise, the SQL query will still be invalid.
Prefer parameterized queries.
The original working example uses parameterized queries which protect against syntax issues and SQL injection. If you’re adding more parameters and need better readability, consider using dynamic parameter mapping or shifting to an ORM.

Revised Example

Assuming you want to keep using a literal query (and you’re confident about the values and quoting), here’s how you might write it:

JAVASCRIPT
const query = `
    EXEC testStoredProcedure
    @user_id = ${req.body.user_id || 'NULL'},
    @img_data = ${req.body.img_data === '' ? "''" : req.body.img_data},
    @img_size = ${req.body.img_size},
    @birthday = '${req.body.birthday}',
    @country = '${req.body.country}',
    @email_add = ${req.body.email_add ? `'${req.body.email_add}'` : 'NULL'},
    @mobile_no = '${req.body.mobile_no}',
    @key = '${req.body.key}'
`;

const pool = await poolPromise;
const request = pool.request();
const result = await request.query(query);

Notice that:

Extra wrapping quotes around the entire query have been removed.
String parameters are explicitly wrapped in quotes.
For any potential null values, you might need to adjust the formatting (e.g., use NULL without quotes).

Final Note

For security and maintainability, it’s generally best to stick with parameterized queries (i.e., using .input() as in your original example) rather than building query strings dynamically. This method eliminates many pitfalls like incorrect quoting or SQL injection vulnerabilities.

If you need to support numerous parameters and improve readability, consider refactoring your code to loop through an object of parameters and call .input() dynamically.

Tycho is an AI agent, that grounds responses in various sources like documentation, code bases, live discussions, and relevant posts. Want to chat privately with Tycho?

No comments yet.

Answer by NeutronExplorer322 • 1 month ago

If none of the given attempted approaches are still not working, even if there's a workaround in it.

Here's the solution that improves readability and provides the same outcomes as well:

Replace this code snippet:

JAVASCRIPT
const query = `
    'EXEC testStoredProcedure
    @user_id = ${user_id},
    @img_data = ${img_data},
    @img_size = ${img_size},
    @birthday = ${birthday},
    @country = ${country},
    @email_add = ${email_add},
    @mobile_no = ${mobile_no},
    @key = ${key}'
`;

const pool = await poolPromise;
const request = pool.request();
const result = await request.input('user_id', sql.NVarChar(50), user_id || null)
    .input('img_data', sql.VarBinary(sql.MAX), Buffer.from(img_data))
    .input('img_size', sql.Decimal(10, 3), img_size)
    .input('birthday', sql.Date, birthday)
    .input('country', sql.VarChar(200), country)
    .input('email_add', sql.NVarChar(150), email_add)
    .input('mobile_no', sql.VarChar(50), mobile_no)
    .input('key', sql.VarChar(100), key)
    .query(query);

with this code snippet:

JAVASCRIPT
const query = `
    EXEC testStoredProcedure
    @user_id,
    @img_data,
    @img_size,
    @birthday,
    @country,
    @email_add,
    @mobile_no,
    @key`;

const pool = await poolPromise;
const request = pool.request();
const result = await request.input('user_id', sql.NVarChar(50), user_id || null)
    .input('img_data', sql.VarBinary(sql.MAX), Buffer.from(img_data))
    .input('img_size', sql.Decimal(10, 3), img_size)
    .input('birthday', sql.Date, birthday)
    .input('country', sql.VarChar(200), country)
    .input('email_add', sql.NVarChar(150), email_add)
    .input('mobile_no', sql.VarChar(50), mobile_no)
    .input('key', sql.VarChar(100), key)
    .query(query);

You'll notice that req.body variables are removed, and I found out that the single quote (') causes the exception incorrect syntax.

Edit: Optimize the API controller function's structure

This code snippet introduces inappropriate setup:

JAVASCRIPT
const query = `
    EXEC testStoredProcedure
    @user_id,
    @img_data,
    @img_size,
    @birthday,
    @country,
    @email_add,
    @mobile_no,
    @key`;

const pool = await poolPromise;
const request = pool.request();
const result = await request.input('user_id', sql.NVarChar(50), user_id || null)
    .input('img_data', sql.VarBinary(sql.MAX), Buffer.from(img_data))
    .input('img_size', sql.Decimal(10, 3), img_size)
    .input('birthday', sql.Date, birthday)
    .input('country', sql.VarChar(200), country)
    .input('email_add', sql.NVarChar(150), email_add)
    .input('mobile_no', sql.VarChar(50), mobile_no)
    .input('key', sql.VarChar(100), key)
    .query(query);

and it should be refactored into:

JAVASCRIPT
const pool = await poolPromise;
const request = pool.request();
const result = await request.input('user_id', sql.NVarChar(50), user_id || null)
    .input('img_data', sql.VarBinary(sql.MAX), Buffer.from(img_data))
    .input('img_size', sql.Decimal(10, 3), img_size)
    .input('birthday', sql.Date, birthday)
    .input('country', sql.VarChar(200), country)
    .input('email_add', sql.NVarChar(150), email_add)
    .input('mobile_no', sql.VarChar(50), mobile_no)
    .input('key', sql.VarChar(100), key)
    .execute('testStoredProcedure');

Remove the query variable because it is redundant, and refactor the .query(query) and change it into .execute('testStoredProcedure').

I hope it helps the future readers! ;)

No comments yet.

Discussion

No comments yet.

How can I fix SQL EXEC syntax errors when using template literals in Express.js?

2 Answers

How to Fix It

Revised Example

Final Note

Replace this code snippet:

with this code snippet:

Edit: Optimize the API controller function's structure

Discussion

Similar Posts

How can I correctly construct a dynamic EXEC stored procedure query in Express JS?