Is SQL Injection possible when parameter's type isn't set?

Question

Does passing SQL Parameters to a stored procedure alone ensure that SQL injection won't happen or the type checks also need to be performed?

As an example -
ADO.NET Code:

    Database DBObject = DataAccess.DAL.GetDataBase();
    DbCommand command = DBObject.GetStoredProcCommand("usp_UpdateDatabase");
    List<DbParameter> parameters = new List<DbParameter>();
    parameters.Add(new SqlParameter("@DbName", txtName.Text));
    parameters.Add(new SqlParameter("@DbDesc", txtDesc.Text));
    command.Parameters.AddRange(parameters.ToArray());
    rowsAffected = DBObject.ExecuteNonQuery(command);

SP:

ALTER PROCEDURE [dbo].[usp_GetSearchResults] 
 -- Add the parameters for the stored procedure here
  @DbName NVARCHAR(50)  = ''
 ,@DbDesc NVARCHAR(50)  = ''

AS
BEGIN
  SET NOCOUNT ON; 
  SELECT     [RegionName]
            ,[AppName]
  FROM  [ApplicationComponent]
  WHERE [DBName]  LIKE ('%' + @DbName+ '%')
  OR    [DBDesc]  LIKE ('%' + @DbDesc+ '%')
END

In the above code, I havent mentioned any parameter types or validation logic. Would it still preevnt SQL injection?

Thanks for the guidance!

Answer 1

Yes, that should still protect you from SQL Injection.

You're not dynamically building the SQL String in your .NET code and you're not using sp_execute to dynamically build and execute a SQL statement in your stored procedure.

Answer 2

No, that should be fine. The value in the LIKE clause is still built up as a string value, rather than being interpreted as part of the SQL statement. It's still being treated as data rather than code, and that's the crucial part of avoiding SQL injection attacks.

Answer 3

Default DbType for an SQLParameter is NVarChar (as per the docs), so this is the type your parameters will have.

Anyway, even if the parameters were of the wrong type, the worst thing you would have would be the type cast exception, not SQL injection.

Answer 4

I would still suggest using typed parameters. While the implementation catches any injection attempts AS FOR NOW, there is no real guarantee that this will be the case down the line - and hopefully your application will lead a long and prosperous lifetime... =)

With regard to SQL Server, the MSDN article on the subject can be found here: http://msdn.microsoft.com/en-us/library/ff648339.aspx