使用 JDBCConnector 和 StoredProcedures 的 T-SQL SQL 注入

问题描述

我想知道是否有可能将 sql 查询注入到过程参数中.我有特殊情况:

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

我想使用@Query 变量注入 sql 查询，并可能对其余代码进行注释.过程是使用 JDBCConnector 通过 Web 服务调用的.我尝试过(以及许多其他组合):

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];--'

作为@Query 参数，但没有成功.

as @Query argument but it didn't work out.

推荐答案

别担心，SQL 注入是不可能的.

No worries, SQL injection is impossible like this.

SQL 注入的工作方式是将 SQL 代码潜入(注入)目标查询中.这对参数来说是不可能的，因为 SQL 参数被视为数据，而不是代码.您可以在参数中传递您想要的任何 SQL 代码，但它不会构成 SQL 注入威胁.

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.

但是 - 请注意，如果您在存储过程中使用动态 SQL，并将参数连接到 SQL 字符串中，那么您的查询容易受到 SQL 注入攻击.

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.

此代码不安全！

DECLARE @Sql nvarchar(max) = N'SELECT * FROM Customers
WHERE ('+ @Query +' IS NULL '....

EXEC(@SQL)

要在 SQL Server 中安全地运行动态 SQL，您可以使用 sp_executeSql 并将参数作为参数传递:

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

这篇关于使用 JDBCConnector 和 StoredProcedures 的 T-SQL SQL 注入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！