I'm trying to find a way to exploit my stored procedure to test for security problems, I have specifically been testing for SQL truncation-based injection, but I did not succeed so far; I don't think that's necessarily because the code is immune to SQL injection, but maybe because my test cases were not thorough, so is this code exploitable? Example?
USE [MyDB] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[sp_mySP] @Myfirst nvarchar(10), @Mysecond nvarchar(400) AS BEGIN DECLARE @SQLString nvarchar(MAX); DECLARE @ParmDefinition nvarchar(MAX); SET NOCOUNT ON; SET @SQLString = N'insert into MyTable (Myfirst, Mysecond) values (@first,@second)'; SET @ParmDefinition = N'@first nvarchar(10),@second nvarchar(400)'; exec sp_executesql @SQLString,@ParmDefinition, @first = @Myfirst, @second = @Mysecond; END
For the record, here is the .NET code that accesses the database, however I want the stored procedure to be secure by itself with no assumptions about the calling function.
cmd = new SqlCommand("sp_mySP", Con); //The SQL Command cmd.CommandType = CommandType.StoredProcedure; //Parameters SqlParameter first = new SqlParameter("@Myfirst", SqlDbType.NVarChar, 10); SqlParameter second= new SqlParameter("@Mysecond", SqlDbType.NVarChar, 400); first.Value = "value-here";// <-- Definitely not the test cases I used second.Value = "value-here"; cmd.Parameters.Add(first); cmd.Parameters.Add(second); Con.Open(); cmd.ExecuteScalar();