Take the 2-minute tour ×
Information Security Stack Exchange is a question and answer site for Information security professionals. It's 100% free, no registration required.

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();
share|improve this question

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer

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

share|improve this answer
    
Yeah, what he has is safe. Assuming that the data in MyTable isn't "executed" in any manner either. –  CodeNaked Mar 17 '11 at 23:21
    
ASP.NET uses Parameterized queries to protect against SQL injections. There's lots of info on the subject, not sure which links to refer abut it. –  rlb.usa Mar 18 '11 at 17:27
    
@CodeNaked: What do you mean the data in the table isn't executed in any manner? –  Orca Mar 23 '11 at 23:14
    
@Voulnet - 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. –  CodeNaked Mar 23 '11 at 23:18
    
@CodeNaked: Oh, okay, that much I already know, I thought you meant an insecurity in the code above. Since you were the first to actually 'answer' my question, would you mind adding an answer so I can accept it? –  Orca Mar 23 '11 at 23:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.