CODEWITHSUNDEEP
sql-servert-sqlstored-proceduressql-injection
Ram
Ram

Reputation: 805

sql injection in sql server stored procedure

In my below procedure how can i check SQL injection for the parameters @TECHNOLOGY,@CURE,@APPLICATION?Please help

CREATE PROCEDURE [dbo].[SEARCH]
 @PRODUCTNAME NVARCHAR(500),
 @TECHNOLOGY NVARCHAR(200),
 @CURE NVARCHAR(200),
 @APPLICATION NVARCHAR(200)
AS
 SELECT DISTINCT PM.F_PRODUCT AS ID,
  PM.F_PRODUCT_NAME AS [NAME],
  PM.F_FORMAT AS FMT,
  PM.F_SUBFORMAT AS SFMT,
  STUFF((SELECT DISTINCT ', ' + CAST(F_LANGUAGE AS VARCHAR(200)) FROM T_PDF_MSDS PD
    WHERE PD.F_PRODUCT = PM.F_PRODUCT AND PD.F_FORMAT = PM.F_FORMAT AND PD.F_SUBFORMAT = PM.F_SUBFORMAT
    FOR XML PATH('')),1,1,'') AS LANG,
  PM.F_DOC_PATH AS DPATH,
  CONVERT(VARCHAR,PM.F_PUBLISHED_DATE,120) AS PDATE,
  SUBSTRING(PM.F_CUSTOM1, CHARINDEX(':',PM.F_CUSTOM1)+1, LEN(PM.F_CUSTOM1)) AS TECHNOLOGY,
  SUBSTRING(PM.F_CUSTOM2, CHARINDEX(':',PM.F_CUSTOM2)+1, LEN(PM.F_CUSTOM2)) AS CURE,
  SUBSTRING(PM.F_CUSTOM3, CHARINDEX(':',PM.F_CUSTOM3)+1, LEN(PM.F_CUSTOM3)) AS [APPLICATION],
  'PDF' AS DOC
 FROM T_PDF_MSDS PM
 WHERE
  --(@PRODUCTNAME IS NULL OR REPLACE(REPLACE(REPLACE(REPLACE(PM.F_PRODUCT_NAME,'™','|TM'),'®','|TS'),'©','|CP'),'°','|DEG')
   --LIKE REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@PRODUCTNAME,'[','\['),'_','\_'),'™','|TM'),'®','|TS'),'©','|CP'),'°','|DEG') ESCAPE '\')
 (@TECHNOLOGY = '-1' OR PM.F_CUSTOM1 LIKE '%' + @TECHNOLOGY + '%')
  AND (@CURE = '-1' OR PM.F_CUSTOM2 LIKE '%' + @CURE + '%')
  AND (@APPLICATION = '-1' OR PM.F_CUSTOM3 LIKE '%' + @APPLICATION + '%')
  AND PM.F_AUTHORIZED IN (-1,1,3)
 GROUP BY PM.F_PRODUCT, PM.F_PRODUCT_NAME, PM.F_FORMAT, PM.F_SUBFORMAT, PM.F_DOC_PATH, PM.F_PUBLISHED_DATE, PM.F_CUSTOM1, PM.F_CUSTOM2, PM.F_CUSTOM3
;

is any sql injection is possible in above procedure?

Upvotes: 0

Views: 181

Answers (1)

freefaller
freefaller

Reputation: 19953

SQL Injection is normally when the SQL command is built using unsanitised inputs. For instance...

var sql = "SELECT * FROM MYTABLE WHERE MYCOLUMN = " + txtInput.Text;
var cmd = new SqlCommand(sql);
...

By using a stored procedure as you have it, you are effectively protecting yourself from this.

Thanks to the comment by @DanGuzman who points out that SQL Injection is indeed possible via stored procedure, should you call it in the wrong way...

var sql = "EXEC MySproc '" + txtInput.Text + "'";
var cmd = new SqlCommand(sql);
...

The correct way to do it would be to make sure you use a parameterised command, such as...

var cmd = new SqlCommand("MySproc", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@MyParam", SqlDbType.NVarChar, 200));
cmd.Parameters["@MyParam"].Value = txtInput.Text;
...

In response to the comment by the OP (which I feel is within the realms of the original question)...

It is also possible to be victim of SQL Injection through the use of Dynamic SQL within the stored procedure. For example...

CREATE PROCEDURE [dbo].[MySproc]
   @MyParam NVARCHAR(200),
   @MyParam2 INT
AS
BEGIN
  DECLARE @SQL NVARCHAR(1000)
  SET @SQL = N'SELECT * FROM [MyTable] WHERE [MyColumn] = ''' + @MyParam + N''''
  EXEC sp_executesql @SQL
END

To protect yourself you should parameterise each variable that you're passing to the sp_executesql...

DECLARE @SQL NVARCHAR(1000)
SET @SQL = N'SELECT * FROM [MyTable] WHERE [MyColumn] = @MyParamInner'
EXEC sp_executesql @SQL, N'@MyParamInner NVARCHAR(200)', @MyParam

Note, you should NOT wrap the @MyParamInner with quotes on the where clause, even though it's a character-based variable

If you have multiple values to pass through you'd update it to something like...

DECLARE @SQL NVARCHAR(1000)
SET @SQL = N'SELECT * FROM [MyTable] WHERE [MyColumn] = @MyParamInner AND [MyColumn2] = @MyParam2Inner'
EXEC sp_executesql @SQL, N'@MyParamInner NVARCHAR(200), @MyParam2Inner INT', @MyParam, @MyParam2

Upvotes: 3

Related Questions