Zeebo Khan Zeebo Khan - 1 month ago 8
C# Question

I'm confused About Parameterized SQL vs Stored Procedures Having Dynamic SQL i.e. are they the same?

I have created the following procedure:

CREATE PROC dynamicColumnsWithCheck
(
@colName VARCHAR(MAX),
@colCheck VARCHAR(MAX),
@condition VARCHAR(MAX)
)

AS

BEGIN

DECLARE @sqlString NVARCHAR(MAX)

SET @sqlString = N'SELECT ' + @colName + ' FROM Student WHERE ' + @colCheck + ' = ''' + @condition + ''''

EXEC sp_executesql @sqlString

END


It works perfect for me but my question is that, Is this parameterized SQL?
or Is it the dangerous dynamic SQL?
I'm confused about the security of this procedure after searching and learning a bit about SQL injection.

Answer Source

It may be injected like:

  declare @sqlstring varchar(MAX)
  declare @colName varchar(500) = 'Password'
  declare @colCheck varchar(500) = 'StudentName' 
  declare @condition varchar(500) = 'Zeebo'' or ''''='''

  SET @sqlString = N'SELECT ' + @colName + ' FROM Student WHERE ' + @colCheck + ' = ''' + @condition + ''''


  PRINT (@sqlString)

Result:

SELECT Password FROM Student WHERE StudentName = 'Zeebo' or ''=''

The ''='' will always be valeted to be true, so it will return you everything, which will be disaster for user passwords.

So simple way to avoid SQL injection is to treat the user variable as a separate unit from the entire statement. For example, you could use QuoteName to wrap the user variable, so the dynamic part will be parsed as the input only, and will not affect the whole query.

For instance:

declare @sqlstring varchar(MAX)
      declare @colName varchar(500) = 'Password'
      declare @colCheck varchar(500) = 'StudentName' 
      declare @condition varchar(500) = 'Zeebo'' or ''''='''

      SET @sqlString = N'SELECT ' + QUOTENAME(@colName) + ' FROM Student WHERE ' + QUOTENAME(@colCheck) + ' = ''' + QUOTENAME(@condition) + ''''

Back to use the previous injection method, the user input for @condition will be treated as a PURE string, not play any role in building the final @sqlString. And the result will be:

SELECT [Password] FROM Student WHERE [StudentName] = '[Zeebo' or ''=']'

Which is not even a correct sql statement.