* has helped me immensely in my coding career by always having the right answers to my questions. This question however might be a bit specific and hopefully it doesn't get harsh responses as it is my first posting to the site.
*通过始终对我的问题得到正确答案，在我的编码生涯中给了我极大的帮助。然而，这个问题可能有点具体，希望它不会得到严厉的回应，因为这是我第一次发布到网站上。
I'm interested in creating a SQL Server 2012 stored procedure that allows users to search various columns. I believe I've created a stored proc that does what I expect it to, but I'm concerned about SQL Injection attempts. Have I covered all my available bases with SQL Injection in mind? If not, please educate me on the ways to circumvent my sample stored proc. Please Note: the one noted has been modified to protect the company's data structure.
我有兴趣创建一个允许用户搜索各个列的SQL Server 2012存储过程。我相信我已经创建了一个存储过程，它可以完成我的预期，但我关注SQL注入尝试。我是否考虑过SQL注入的所有可用基础？如果没有，请教我如何规避我的样本存储过程。请注意：注意到的那个已被修改以保护公司的数据结构。
Stored Procedure:
存储过程：
CREATE PROCEDURE [dbo].[spSearch]
(
@searchTerm varchar(50) = NULL
)
AS
set @searchTerm = LTRIM(RTRIM(@searchTerm));
select a.ID
,a.Col1
,a.Col2
,a.Col3
,a.Col4
,a.Col5
,b.Col1
from table1 a
left join table2 b on a.ID = b.ID
where (a.Col1 like '%' + @searchTerm + '%'
or a.Col2 like '%' + @searchTerm + '%'
or a.Col3 like '%' + @searchTerm + '%'
or a.Col4 like '%' + @searchTerm + '%'
or b.Col1 like '%' + @searchTerm + '%'
or b.Col2 like '%' + @searchTerm + '%'
or b.Col3 like '%' + @searchTerm + '%'
or b.Col4 like '%' + @searchTerm + '%'
or b.Col5 like '%' + @searchTerm + '%'
or b.Col6 like '%' + @searchTerm + '%')
GO
My main concern is the posibility of passing hostile SQL commands into the 50-character varchar parameter and having it execute in any of the various where like clauses.
我主要担心的是将恶意SQL命令传递到50个字符的varchar参数并使其在各种where子句中执行的可能性。
1 个解决方案
#1
0
It really depends on how you call the SP from code. The Stored Procedure is self is not safe from sql injection. When you are using parameterized functions you are probably fine. When you are sending sql queries as string, than you still have a sql injection problem. The main problemen is the string concatenation in your code. String concatenations are never safe if you have parameters from an untrusted source. Do Stored Procedures Protect Against SQL Injection?
这实际上取决于你如何从代码中调用SP。存储过程是自我从sql注入是不安全的。当您使用参数化函数时，您可能没问题。当你以字符串形式发送sql查询时，你仍然有一个sql注入问题。主要问题是代码中的字符串连接。如果您有来自不受信任来源的参数，则字符串连接永远不会安全。存储过程是否可以防止SQL注入？
The basic lesson is to write Stored Procedures without any string concatenations.
基本的教训是编写存储过程而不进行任何字符串连接。
#1
0
It really depends on how you call the SP from code. The Stored Procedure is self is not safe from sql injection. When you are using parameterized functions you are probably fine. When you are sending sql queries as string, than you still have a sql injection problem. The main problemen is the string concatenation in your code. String concatenations are never safe if you have parameters from an untrusted source. Do Stored Procedures Protect Against SQL Injection?
这实际上取决于你如何从代码中调用SP。存储过程是自我从sql注入是不安全的。当您使用参数化函数时，您可能没问题。当你以字符串形式发送sql查询时，你仍然有一个sql注入问题。主要问题是代码中的字符串连接。如果您有来自不受信任来源的参数，则字符串连接永远不会安全。存储过程是否可以防止SQL注入？
The basic lesson is to write Stored Procedures without any string concatenations.
基本的教训是编写存储过程而不进行任何字符串连接。