I'm Trying to Find how to Exploit my SQL Code

Question

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();

Answer 1

Yeah, what you have is safe. Assuming that the data in MyTable isn't "executed" in any manner either.

If you were to select the MyFirst or MySecond column from MyTable and then pass that to EXEC without using the parameter syntax you outlined above, then that would be bad. Basically, never pass a string input by the user as the first parameter to EXEC or sp_executesql.

Answer 2

Doesn't the truncation SQL injection attack only apply to dynamic/concatenated query constructs?