Can this stored procedure be compromised by SQL Injection:
ALTER PROC [dbo].[usp_CanYouCompromiseMe]
@Pattern VARCHAR(60)
AS
BEGIN
SELECT *
FROM SomeTable
WHERE
SomeColumn LIKE @Pattern+'%'
END
Add a comment
|
2 Answers
No, the procedure cannot be compromised by SQL Injection (to execute arbitrary statements).
But it can be made to misbehave, in certain conditions. If you have an index on SomeColumn and expect it to perform an index seek (maybe you set a minimum length for the pattern so the index is selective enough for a seek), someone can place a % sign at the start of the pattern, so an index seek cannot be used anymore.
Comments
No not in a where clause like that. SQL Injection is a generally a dynamic SQL issue. Note this proc and how I run it (the EXEC statement purposely commented out):
CREATE PROC dbo.usp_CanYouCompromiseMe (@Pattern VARCHAR(60))
AS
DECLARE @sql varchar(1000) =
'SELECT *
FROM (VALUES (''xxx'')) x(SomeColumn)
WHERE SomeColumn LIKE '''+@Pattern+'%'';';
PRINT (@sql);
--EXEC (@sql);
GO
Note this how this:
EXEC dbo.usp_CanYouCompromiseMe 'x%'';SHUTDOWN WITH NOWAIT; PRINT''';
...creates this SQL:
SELECT *
FROM (VALUES ('xxx')) x(SomeColumn)
WHERE SomeColumn LIKE 'x%';SHUTDOWN WITH NOWAIT; PRINT'%';
