TSQL 'Invalid column name' error on value of sproc parameter

sql-server, t-sql

Solution

That's a nice SQL injection vulnerability there.

Start by rewriting it this way, using bind parameters:

DECLARE @SQL nvarchar(4000)

SET @SQL =
    'SELECT CategoryID, SubCategoryID, ReportedNumber ' +
    'FROM tblStatistics ' +
    'WHERE UnitCode = @UnitCode ' +
    'AND FiscYear = @CurrYear'

EXEC sp_executesql
    @SQL,
    '@UnitCode varchar(10), @CurrYear int',
    @UnitCode = 'COB',
    @FiscYear = 10

Problem

here's my code: ``` DECLARE @SQL varchar(600) SET @SQL = 'SELECT CategoryID, SubCategoryID, ReportedNumber FROM tblStatistics WHERE UnitCode = ' + @unitCode + ' AND FiscYear = ' + @currYEAR EXEC (@SQL) ``` When i run this sproc with unitCode = 'COB' and currYEAR = '10', i get the following error: ``` Invalid column name 'COB'. ``` Does anyone know why? thx!

Original source