SQL Encode string parameter so special characters work

Question

I would like to be able to add a new SQL LOGIN and name it after a person email address. For example "[email protected]". When I pass this to the following stored procedure I get an error (error follows procedure).

The stored proc:

CREATE PROCEDURE [Forms].[AddLogin]
    @Email nvarchar(2048),
    @TenantPassword nvarchar(2048)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    EXEC('CREATE LOGIN ' + @Email + ' WITH PASSWORD = ''' + @TenantPassword + ''', DEFAULT_DATABASE = DunderMifflin')
END

The Error:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.

I'm sure all I need to do is encode the parameter somehow. Any help? If I add a user via the SQL Manager wizard I can specify email addresses so I know that it is a valid Login name.

Answer 1

You should be able to surround it with square brackets, as detailed here:

EXEC('CREATE LOGIN [' + @Email + '] WITH PASSWORD = ''' + @TenantPassword + ''', DEFAULT_DATABASE = DunderMifflin')

Answer 2

Obligatory injection warning: Whatever you do, please don't pass the user's password directly into this routine!! What if the user entered a password of

bill');DROP DATABASE DunderMifflin;--

and then you are basically executing the statement:

CREATE LOGIN [email protected] WITH PASSWORD = 'bill');
DROP DATABASE DunderMifflin;
-- ''', DEFAULT_DATABASE = DunderMifflin')

and that's not good.