search

tediousjs / node-mssql

Microsoft SQL Server client for Node.js
https://tediousjs.github.io/node-mssql
MIT License
2.22k stars 464 forks source link

`CREATE DATABASE` Failing When Using the ES6 Tagged Template Literal Approach #1680

Closed jasel-lewis closed 2 weeks ago

jasel-lewis commented 2 weeks ago

I've noticed an issue when using ES6 Tagged Template Literals with a CREATE DATABASE command.

Expected behavior:

Running the below, I expect a database with the name my_db to be created.

const dbname = 'my_db';
await pool.request().query`CREATE DATABASE ${dbname}`;

Actual behavior:

When running the above, I get a RequestError with the message "Incorrect syntax near '@param1'" and the stacktrace:

RequestError: Incorrect syntax near '@param1'.
    at handleError (/opt/nodejs/node_modules/mssql/lib/tedious/request.js:384:15)
    at Connection.emit (node:events:517:28)
    at Connection.emit (/opt/nodejs/node_modules/tedious/lib/connection.js:957:18)
    at RequestTokenHandler.onErrorMessage (/opt/nodejs/node_modules/tedious/lib/token/handler.js:284:21)
    at Readable.<anonymous> (/opt/nodejs/node_modules/tedious/lib/token/token-stream-parser.js:18:33)
    at Readable.emit (node:events:517:28)
    at addChunk (node:internal/streams/readable:368:12)
    at readableAddChunk (node:internal/streams/readable:341:9)
    at Readable.push (node:internal/streams/readable:278:10)
    at next (node:internal/streams/from:98:31)

Alternate Attempts

Alternate 1

const dbname = 'my_db';
await pool.request().query`CREATE DATABASE [${dbname}]`;

Fail. Running the above, a database is created with the name @param1.

Alternate 2

const dbname = 'my_db';
await pool.request()
    .input('dbname', sql.VarChar, dbname)
    .query('CREATE DATABASE @dbname');

Fail. Running the above, I get a RequestError with message "Incorrect syntax near '@dbname'" and the stacktrace:

RequestError: Incorrect syntax near '@dbname'.
    at handleError (/opt/nodejs/node_modules/mssql/lib/tedious/request.js:384:15)
    at Connection.emit (node:events:517:28)
    at Connection.emit (/opt/nodejs/node_modules/tedious/lib/connection.js:957:18)
    at RequestTokenHandler.onErrorMessage (/opt/nodejs/node_modules/tedious/lib/token/handler.js:284:21)
    at Readable.<anonymous> (/opt/nodejs/node_modules/tedious/lib/token/token-stream-parser.js:18:33)
    at Readable.emit (node:events:517:28)
    at addChunk (node:internal/streams/readable:368:12)
    at readableAddChunk (node:internal/streams/readable:341:9)
    at Readable.push (node:internal/streams/readable:278:10)
    at next (node:internal/streams/from:98:31)

Alternate 3

const dbname = 'my_db';
await pool.request().query(`CREATE DATABASE [${dbname}]`);

Success. Sort of. Running the above, a database is created with the name my_db. The problem with this, however, is we're not parameterizing/sanitizing the database name (as correctly identified by this issue which is a proposal to remove the capability).

Alternate 4

const dbname = 'my_db';
await pool.request().query`
    DECLARE @_dbname VARCHAR(128) = ${dbname};
    EXEC('CREATE DATABASE ' + @_dbname);`;

Success. This approach dynamically generates the SQL and EXECutes it, but at the cost of having to introduce an additional line of SQL to introduce a second level of abstraction (the @_dbname parameter placeholder).

Additional Information

I have encountered what appears to be similar behavior when calling for CREATE USER...FOR LOGIN... and CREATE LOGIN...WITH PASSWORD... SQL actions. I have not exhaustively explored each of these like I have (and wrote out) for CREATE DATABASE above. I'm simply trying to start with getting just one of these figured out, first (the CREATE DATABASE) and then go from there.

Software versions

jasel-lewis commented 2 weeks ago

Closing because additional research has led me to believe that parameterization is not generally an option that can be used with SQL queries involving, and similar to, CREATE DATABASE.... It was also realized that this issue had become more of a "which is the best practice" inquiry, which is not appropriate use of a GitHub Issue.