locked
How are parametrized static stored procedures impervious to sql injection? RRS feed

  • Question

  • Hi I've been researching quite a bit about sql injection and I've heard about this that:
    • a developer should never use dynamic queries
    • pass the user input through parameters
    • use stored procedures that have the statements already compiled in them.
    Now from what I've read the parameters encapsulate the user input there by limiting the scope of the user input so that it cannot alter the queries' structure, and if the queries are compiled in the stored procedure directly that also prevents the structure of the query from being altered.

    Now is there any technical documentation or a specific paper that demonstrates how a parametrized stored procedure with static queries is impervious to sql injection.

    How does the parameters protect the query by verifying the data type? That would work beautifully with numeric parameters since it would immediately throw an exception if you try to pass an alphanumeric character, but what about text.

    Do text and varchar parameters just escape quotes or does it do more than that?

    What I want to know is that if I'm defending a this statement and someone that has some knowledge of security but only has limited knowledge in sql injection how can I explain to him that parameters and static queries make the code impervious to sql injection and that no hacker can inject any other type of character that bypasses my security?

    This question was raised because the other day a security professional mentioned to me the following scenario.

    "If in a few years a new encoding is created which replaces unicode or any other popular encoding, and this new encoding has some sort of special characters that can be injected so that they allow an attacker to bypass the parameters security what do you do?".

    How can I tell this person that the scenario mentioned above is impossible if I have no knowledge if the inner workings of the parameter's implementation?

    Thank You,

    Sunday, October 12, 2008 11:17 PM

Answers

  •     The way SQL injection works is by abusing the fact that T-SQL is a script that needs to be parsed, and when the parameters are concatenated, there is no distinction between what was the original code and what are the parameters, it all resolves into a script. For example:

    EXEC('SELECT * FROM sys.database_principals WHERE name = ''' + @name + '''')

     

        If I abuse the parameter @name, the system has no way to recognize the usage as it will simply execute the resulting script after the concatenation:

     

    SET @name = 'Alice'

    EXEC('SELECT * FROM sys.database_principals WHERE name = ''' + @name + '''')

    ==> Transtales into

    SELECT * FROM sys.database_principals WHERE name = 'Alice'

     

    SET @name = 'Mallory''; DROP TABLE employees; --'

    EXEC('SELECT * FROM sys.database_principals WHERE name = ''' + @name + '''')

    ==> Transtales into

    SELECT * FROM sys.database_principals WHERE name = 'Mallory';

    DROP TABLE employees;-- '

     

      When parameterizing T-SQL modules or statements, the T-SQL compiler will understand exactly what was the intended command and what are the parameters; there is never an in-place substitution hence preventing SQL injection. For example:

    CREATE PROC [sp_demo_injection02]( @name sysname )

    AS

      declare @cmd nvarchar(max)

      declare @parameters nvarchar(max)

      set @cmd = N'SELECT * FROM sys.database_principals WHERE name = @name'

      set @parameters = '@name sysname'

      EXEC sp_executesql @cmd, @parameters, @name = @name

    go

     

      In this example, as @name has already been set when calling the SP, the system knows that the command is SELECT * FROM sys.database_principals WHERE name = @name , and it only needs to access the variable in memory to find out the value for @name, but no reinterpretation (and hence no injection) of the statement takes place.

     

      When using parameterization from a different language such as C# using the proper classes such as  System.Data.SqlClint.SqlParameter will help you to strong type the queries and generate parameterized SQL equivalent to the example above.

     

      I used as a reference the examples I included on the following article: http://blogs.msdn.com/raulga/archive/2007/01/04/dynamic-sql-sql-injection.aspx

     

      The security professional statement you mentioned makes sense when the defense against SQL injection is based on validating the data to detect invalid data; this is one of the reasons why the security recommendation is always to verify for well formed data.

     

       Unless the new hypothetical encoding would enable masking the data as valid one (i.e. Using valid patterns for the data), it should little effect on your data validation, and as we already mentioned, internally as the parameters are not reinterpreted as statements, it should no effect when using SQL parameterization (at least in terms of injection).

     

      Please let us know if this information is what you were expecting or if you have any additional questions.

     

    -Raul Garcia

      SDE/T

      SQL Server Engine

    Monday, October 13, 2008 5:52 PM

All replies

  •     The way SQL injection works is by abusing the fact that T-SQL is a script that needs to be parsed, and when the parameters are concatenated, there is no distinction between what was the original code and what are the parameters, it all resolves into a script. For example:

    EXEC('SELECT * FROM sys.database_principals WHERE name = ''' + @name + '''')

     

        If I abuse the parameter @name, the system has no way to recognize the usage as it will simply execute the resulting script after the concatenation:

     

    SET @name = 'Alice'

    EXEC('SELECT * FROM sys.database_principals WHERE name = ''' + @name + '''')

    ==> Transtales into

    SELECT * FROM sys.database_principals WHERE name = 'Alice'

     

    SET @name = 'Mallory''; DROP TABLE employees; --'

    EXEC('SELECT * FROM sys.database_principals WHERE name = ''' + @name + '''')

    ==> Transtales into

    SELECT * FROM sys.database_principals WHERE name = 'Mallory';

    DROP TABLE employees;-- '

     

      When parameterizing T-SQL modules or statements, the T-SQL compiler will understand exactly what was the intended command and what are the parameters; there is never an in-place substitution hence preventing SQL injection. For example:

    CREATE PROC [sp_demo_injection02]( @name sysname )

    AS

      declare @cmd nvarchar(max)

      declare @parameters nvarchar(max)

      set @cmd = N'SELECT * FROM sys.database_principals WHERE name = @name'

      set @parameters = '@name sysname'

      EXEC sp_executesql @cmd, @parameters, @name = @name

    go

     

      In this example, as @name has already been set when calling the SP, the system knows that the command is SELECT * FROM sys.database_principals WHERE name = @name , and it only needs to access the variable in memory to find out the value for @name, but no reinterpretation (and hence no injection) of the statement takes place.

     

      When using parameterization from a different language such as C# using the proper classes such as  System.Data.SqlClint.SqlParameter will help you to strong type the queries and generate parameterized SQL equivalent to the example above.

     

      I used as a reference the examples I included on the following article: http://blogs.msdn.com/raulga/archive/2007/01/04/dynamic-sql-sql-injection.aspx

     

      The security professional statement you mentioned makes sense when the defense against SQL injection is based on validating the data to detect invalid data; this is one of the reasons why the security recommendation is always to verify for well formed data.

     

       Unless the new hypothetical encoding would enable masking the data as valid one (i.e. Using valid patterns for the data), it should little effect on your data validation, and as we already mentioned, internally as the parameters are not reinterpreted as statements, it should no effect when using SQL parameterization (at least in terms of injection).

     

      Please let us know if this information is what you were expecting or if you have any additional questions.

     

    -Raul Garcia

      SDE/T

      SQL Server Engine

    Monday, October 13, 2008 5:52 PM
  •  

    I understand that was very well explained.

     

    Thank you for your help and thanks for the link.

    Wednesday, October 15, 2008 2:05 PM