I have a very simple stored procedure which generates the next sequence as part of a simple Auto Number solution.
The SQL store procedure is
CREATE PROCEDURE [dbo].[NextAutoNumber]
@Key varchar(100),
@UserId varchar(50),
@Return bigint output
AS
BEGIN
SET NOCOUNT ON;
DECLARE @cmd NVARCHAR(1000)
IF (NOT EXISTS (SELECT * FROM sys.sequences WHERE name = @Key))
BEGIN
--CREATE SEQUENCE FOR NEW ENTITY
EXEC('CREATE SEQUENCE [dbo].' + @Key + ' AS BIGINT START WITH 1 INCREMENT BY 1 NO CYCLE')
END
SET @cmd = 'SET @Return = (NEXT VALUE FOR ' + @Key + ')'
EXEC sp_executesql @cmd, N'@Return int output', @Return output;
END
GO
I am calling this stored procedure from a Web Service as below.
using (var conn = new SqlConnection(_connectionString))
using (var cmd = new SqlCommand("NextAutoNumber", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
if (conn.State == System.Data.ConnectionState.Closed)
conn.Open();
cmd.Parameters.Add(new SqlParameter("@Key", SqlDbType.Text)
{
Value = key,
Direction = ParameterDirection.Input
});
cmd.Parameters.Add(new SqlParameter("@UserId", SqlDbType.Text)
{
Value = userId,
Direction = ParameterDirection.Input
});
cmd.Parameters.Add(new SqlParameter("@Return", SqlDbType.BigInt) { Direction = ParameterDirection.Output });
cmd.ExecuteNonQuery();
return (long)cmd.Parameters["@Return"].Value;
}
Now, they have reported that this is susceptible to SQL injection attacks. Any ideas on how to modify this to prevent SQL injection?
I have looked at the following options - such as using a parameterized query but my SQL skills are pretty basic!
