How to prevent SQL injection using a stored procedure while insert record?

Question

I am new to SQL Server, I am trying to insert records into table using a stored procedure as shown below.

I want a suggestion that is using the below stored procedure. Also:

• can I prevent SQL injection?
• is it the right way?

Correct me if I miss anything in below procedure which leads to SQL injection.

Create PROCEDURE [dbo].[spInsertParamTable]
   @CmpyCode nvarchar(50),
   @Code nvarchar(50),
   @DisplayCode nvarchar(50),
   @TotalDigit int,
   @Nos bigint,
   @IdentitY int OUTPUT
AS
BEGIN
   INSERT tblParamTable (CmpyCode, Code, DisplayCode, TotalDigit, Nos)
   VALUES (@CmpyCode, @Code, @DisplayCode, @TotalDigit, @Nos)
END

SELECT @Identity = SCOPE_IDENTITY();
RETURN @Identity

Answer 1

SQL Injection specifically refers to injecting SQL code into an existing SQL query that's built up via string concatenation and executed dynamically. It is almost always of the form:

@dynamicSQL = "select * from sensitivetable where field = " + @injectableParameter
sp_executesql @dynamicSQL

For this particular stored procedure, the worst an attacker could do is insert unhelpful values into your tblParamTable.

However, if these values are then used in a dynamically-built query later on, then this merely becomes a second-order attack: insert values on page 1, see results of dynamic query on page 2. (I only mention this since your table is named tblParamTable, suggesting it might contain parameters for later re-use.)

Answer 2

Can I prevent SQL injection?

You already are - there is no way to "inject" code into your SQL statement since you're using parameters.

Is it the right way?

Well, there's not one "right" way - but I don't see anything seriously wrong with what you're doing. A few suggestions:

• You don't need to RETURN your output parameter value. Setting it is enough.
• You have the last SELECT outside of the BEGIN/END block, which isn't hurting anything but for consistency you should put everything inside BEGIN/END (or leave them out altogether).