Prob with FAQ: Checking for SQL injection

[Creto]

Regarding:

http://www.tek-tips.com/faqs.cfm?fid=5267

I set up the sp stated first in the first part with no problems. However, when I try creating the second sp, I'm getting syntax errors.

CREATE PROCEDURE spNewAnswer
 --String data that will become part of dynamic SQL statement.
   @UserInput1 AS varchar(8000),
   @UserInput2 AS varchar(8000),
   @wAnswer varchar(255)
AS
   DECLARE @BadKeywords AS varchar(8000)
   --Be sure to place a space between each parameter to be tested.
  EXEC spInjectionAttack 
      @UserInput1 + ' ' +  @UserInput2,
      @BadKeywords OUTPUT
   IF Len(@BadKeywords)>0 BEGIN
     RAISEERROR ('Possible SQL injection attack:'
         +Char(13)+@BadKeywords,15,-1)
      RETURN
   END

DECLARE @KEY int
INSERT INTO tblSurveyAnswers (svaAnswers)
	VALUES
		(
		@wAnswer
		)
SELECT @KEY = @@IDENTITY
SELECT svaUID, svaAnswers from tblSurveyAnswers WHERE svaUID = @KEY

GO

This is the error I get

Error 170: Line 10: Incorrect syntax near '+'.
Line 13: Incorrect syntax near 'RAISEERROR'.

I can't figure out why it's erroring out on the concatenation.

 

[vongrunt]

raiserror expects string/variable, not expression. Use variable to compose error message:

declare @foo varchar( some_length )
...

IF Len(@BadKeywords)>0 BEGIN
	set @foo = 'Possible SQL injection attack:' +Char(13)+@BadKeywords
	RAISEERROR ( @foo,15,-1)
	RETURN
END

 

[Creto]

Still gives me the same error.

 

[vongrunt]

raiserror with one "e"... plus it is possible you'll have to prepare EXEC string the same way.

 

[SQLSister]

BTW, please do not use @@identity. Use scope_identity() instead. The reason is that if you ever put a trigger on the table you are refernceing which in turn inserts a record to a table with an identity field, you will get the second identity not the first returned from @@identity.

Questions about posting. See faq183-874

 

[Creto]

Found the issue. Turns out the server was set to set a string to null when a string is concatenated with null.

Thanks for the ideas.