I want to know if there is a possibility to inject sql query into procedure argument. I have particular case:
ALTER PROCEDURE [Test].[Injection]
@Query varchar(250) = null
AS
SET NOCOUNT ON
SET @Query = REPLACE(@Query,'','') COLLATE Latin1_General_CI_AI
... more sql code
SELECT * FROM Customers
WHERE (@Query IS NULL OR (Name COLLATE Latin1_General_CI_AI like '%'+@Query+'%'))
ORDER BY ExternalCode ASC
I want to inject sql query using @Query variable and possibly comment the rest of the code. Procedure is called via Web Service using JDBCConnector. I tried passing (and many others combinations):
'''abc'','''','''');SELECT * FROM [DummyTable][Dummy];--'
as @Query argument but it didn't work out.
No worries, SQL injection is impossible like this.
The way SQL injection works is by sneaking in (injecting) SQL code into the target query.
That is not possible to do with parameters, since SQL parameters are treated as data, not as code. You can pass any SQL code you want inside the parameter, but it will not pose an SQL injection threat.
However - please note that if you are using dynamic SQL inside your stored procedure, and concatenate the parameters into the SQL string, then your query is vulnerable to SQL injection attacks.
This code is not safe!
DECLARE @Sql nvarchar(max) = N'SELECT * FROM Customers
WHERE ('+ @Query +' IS NULL '....
EXEC(@SQL)
To safely run dynamic SQL in SQL Server you can use sp_executeSql and pass the parameters as parameters:
DECLARE @Sql nvarchar(max) = N'SELECT * FROM Customers
WHERE (@TheQuery IS NULL '....
EXEC sp_ExecuteSql
@Sql,
N'@TheQuery varchar(250)',
@TheQuery = @Query
