Have Geek Brain

SQL Encodes a string parameter, so special characters work

I would like to be able to add a new SQL login and name it after the person's email address. For example, " billg@example.com ". When I pass this to the next stored procedure, I get an error (error follows procedure).

Stored procedure:

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

Error:

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

I'm sure all I have to do is encode the parameter somehow. Any help? If I add a user through the SQL Manager wizard, I can provide the email addresses so that I know it is a valid login.

+1


a source to share


2 answers


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



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


a source


Mandatory Warning: Whatever you do, do not skip the user password directly into this procedure! What to do if the user entered a password

bill');DROP DATABASE DunderMifflin;--

and then you basically execute the statement:



CREATE LOGIN billg@microsoft.com WITH PASSWORD = 'bill');
DROP DATABASE DunderMifflin;
-- ''', DEFAULT_DATABASE = DunderMifflin')

and that's not good.

0


a source






More articles:


All Articles