SQL : Getting Incorrect Syntax near '=' While using CASE statement with variables-CodePudding

I am trying to perform the below query where some variables are being used. This below SQL string is a part of Stored Procedure. Idea is to dynamically set the Target columns and it's values based on FileKey.

DECLARE @TargetColNames nvarchar(max)='';
DECLARE @SourceColNames nvarchar(max)='';
DECLARE @SourceColNamesInsert nvarchar(max)='';
DECLARE @UpdateColumns nvarchar(max)='';

SELECT 
CASE WHEN @FileKey IN ('s_1','s_2') THEN                
  @TargetColNames = @TargetColNames   ' [CreatedUser], [UpdatedUser], 
  [CreatedDateTime],[UpdatedDateTime],[IsDeleted],[DeletedOn]'     
  ELSE 
  @TargetColNames = @TargetColNames   ' [CreatedUser], [UpdatedUser], 
  [CreatedDateTime], [UpdatedDateTime]' END,
@SourceColNames = concat('CreatedUser','UpdatedUser','CreatedDateTime', 
'UpdatedDateTime'),
@SourceColNamesInsert = concat(''',@User, ''',''',@User, ''', 
'Getdate()', 'Getdate()' ),
CASE WHEN @FileKey IN ('s_1','s_2') THEN
  @UpdateColumns = concat('Target.UpdatedUser= ''',@User,''','Target. 
  [IsDeleted]=0','Target.[DeletedOn]=null')
ELSE 
   @UpdateColumns = concat('Target.UpdatedUser= ''',@User,''', 
   'Target.UpdatedDateTime=Getdate()')
END

The above SQL string is giving an error as

Msg 102, Level 15, State 1, Procedure uspDynamicStageToPropLayer1, Line 165 [Batch Start Line 
5]
Incorrect syntax near '='.

Where I am missing here? May be this is quite a silly mistake!!

CodePudding user response：

Also, by doing concat with your quoted parts, you might allow sql-injection into your query even building dynamically. What if a user's name (or forced parameter HAS a leading single quote, then garbage injection such as

@User = ';drop table X --

Having said that, some of the stuff could be more simplified, such as

SELECT
   @TargetColNames = @TargetColNames 
         ' [CreatedUser], [UpdatedUser], [CreatedDateTime], [UpdatedDateTime]'
         CASE WHEN @FileKey IN ('s_1','s_2') 
              THEN ', [IsDeleted], [DeletedOn]'
              else '' 
         end

For the insert, you will probably get a failure. If you look at a possible result of the @User

@SourceColNamesInsert = concat(''',@User, ''',''',@User, ''', 
'Getdate()', 'Getdate()' ),

will result with the value below which is NOT what I think is intended. Notice no comma's between values because the triple ' is creating start and end literals, and leaves no actual comma between column insert values.

',@User, '',@User, 'Getdate()Getdate()

But instead ...

select concat('''@User'', ''@User''', ', Getdate(), Getdate()' );

which will result in...

'@User', '@User', Getdate(), Getdate()

The ''' actually creates an opening quoted string immediately with the value after it, then the '' (double) closes the quoted string, but also adds the comma separator before the next '' (double) to start second user and ''' (triple) to close the second @User, THEN adding comma and both getdate() calls.

'@User', '@User', Getdate(), Getdate()

Now, if the value for @User was 'Bob', and your intent was to have the string output as

'Bob', 'Bob', Getdate(), Getdate()

change to select concat('''', @User, ''', ','''', @User, '''', ', Getdate(), Getdate()' );

The '''' (quad) means I want to open a string, do a single quote (by the inner two ''), and close this as its own string. THEN get the VALUE of the @User. Then follow by an open string ' with '' for closing the quote around the name, then open single quote to start next, the comma before starting the next quote for the second user and closing it as well via '''', then the VALUE of the user again, and finally closing the second user with close quote ''''. Finally adding a comma and getdate() calls. Yes, stupid tricky in the quoting.

An easier implementation without CONCAT() is just using between each explicit part such as

select ''''   @User   ''''   ', '   ''''   @User   ''''   ', Getdate(), getdate()' ;

where each '''' is a single quote thus resulting in

'   Bob   '   ,   '   Bob   '   , Getdate(), getdate()

resulting in

'Bob', 'Bob', Getdate(), getdate()

I'll leave the final UpdateColumns to you to confirm your intended output.

But, as mentioned, BEWARE OF POSSIBLE SQL-INJECTION when you are dynamically building sql-statements with embedded parameter values as this APPEARS to be doing.