Escaping bracket in database name [duplicate]

Question

I'd like to create database in an application with a name provided runtime.

The current solution is:

create database [USER_PROVIDED_NAME] ...

The problem is with that solution is that the user provided name can contain [ or ] leading to syntax error (and possibly SQL injection).

Is there a way to do it safely, without limiting the possible database names?

Answer 1

You can use QUOTENAME function:

select quotename('foo[bar]baz'); -- [foo[bar]]baz]
create database [foo[bar]]baz];

That being said, I would rather reject such names using a whitelist of allowed characters.

Answer 2

If this is controlled in your application, then you can delimit identify and escape the right brackets (]). The left brackets ([) don't need escaping:

CREATE DATABASE [This is a [really]] bad idea for a [database]] name];

Alternatively, if you want to handle this in the SQL layer and the database name is coming from the application, you could use dynamic SQL and QUOTENAME:

DECLARE @DatabaseName sysname = N'This is a [really] bad idea for a [database] name'; --This is your parameter

DECLARE @SQL nvarchar(MAX) = N'CREATE DATABASE ' + QUOTENAME(@DatabaseName) + N';';

EXEC sys.sp_executesql @SQL;

Answer 3

You could use the ANSI escaping double quotes here:

CREATE DATABASE "Name with [stuff]";