造成sql注入的功能点_创建一个SQL注入保护功能

造成sql注入的功能点

问题 ( The Problem )

The Problem demonstrated here describes a very common scenario. The IT Security group orders all programmers that all the dynamic input strings that comes from user input to be checked for suspicious SQL injection intentions.

这里演示的问题描述了一个非常常见的情况。 IT安全组命令所有程序员，检查来自用户输入的所有动态输入字符串是否存在可疑SQL注入意图。

SQL injection is a code injection technique used to attack data-driven applications. During the attack, malicious SQL statements are inserted into data entry fields for execution inside the database engine.

SQL注入是一种代码注入技术，用于攻击数据驱动的应用程序。 在攻击过程中，恶意SQL语句将插入到数据输入字段中，以便在数据库引擎内部执行。

SQL injection is a common attack method on websites and can be used to attack any type of SQL database.

SQL注入是网站上常见的攻击方法，可用于攻击任何类型SQL数据库。

The damages that SQL injection causes are many and include, among others:

SQL注入引起的损害很多，其中包括：

1. Disclosure user’s identity and password
   披露用户的身份和密码
2. Tampering with existing data
   篡改现有数据
3. Interfering with the system’s transactions like changing account balances
   干扰系统交易，例如更改帐户余额
4. Disclosure of all data on the system
   公开系统上的所有数据
5. Destruction of the data
   破坏数据
6. Making the data unavailable
   使数据不可用
7. Taking control of the database server by getting administrative privileges
   通过获得管理特权来控制数据库服务器

The great importance for defending against SQL injection attacks was emphasized by a study done in 2012. The observation was that the average web application received 4 SQL injection attacks per month.

2012年进行的一项研究强调了防御SQL注入攻击的重要性。观察发现，平均每个Web应用程序每月都会收到4次SQL注入攻击。

There are many 3^rd party web application firewall (WAF) tools that checks for SQL injection. The solution demonstrated in this article is an applicative one and does not involve any 3^rd party SQL inspection tool.

有很多^第三方的Web应用防火墙（WAF）工具，SQL注入检查。 在这篇文章中表现出的解决方案是一个适用一个，并且不涉及任何^第三方 SQL检查工具。

The programmers have to apply the function for each input, immediately after getting the input parameter and halt execution if the function result indicates SQL injection suspicious input string.

程序员必须在获取输入参数后立即对每个输入应用该函数，如果函数结果指示SQL注入可疑输入字符串，则暂停执行。

建议的解决方案 ( The suggested solution )

The suggested solution presented here involves creating a user defined T-SQL scalar function that checks the input string for any suspicious key words that might indicate the SQL injection intents.

此处提出的建议解决方案涉及创建一个用户定义的T-SQL标量 函数 ，该函数检查输入字符串中是否有任何可疑关键字，这些关键字可能表明SQL注入意图。

The function checks the input string against a set of pre-defined keywords that are known to be used in SQL injection cases.

该函数根据一组已知在SQL注入情况下使用的预定义关键字来检查输入字符串。

The list of keywords is stored inside a special, dedicated table so that any addition of any other keywords and thus strengthening the security protection power of the function by simply adding rows to that table.

关键字列表存储在一个特殊的专用表中，以便任何其他关键字的添加，从而通过简单地向该表添加行来增强功能的安全保护能力。

Here is the list of suspicious words that I have entered along with an explanation of what makes them suspicious for SQL injection purposes.

这是我输入的可疑单词的列表，并解释了使它们出于SQL注入目的可疑的原因。

The initial words selected here comes from my personal experience in the subject and include data modification keywords, SQL data definition language commands, stored procedures and extended stored procedure common prefixes, remark and concatenation signs, control flow keywords , transaction control keywords, data set functions, batch control keywords , server control commands and so on.

此处选择的初始单词来自于我在该主题上的个人经验，包括数据修改关键字，SQL数据定义语言命令，存储过程和扩展存储过程的通用前缀，备注和串联符号，控制流关键字，事务控制关键字，数据集功能，批处理控制关键字，服务器控制命令等。

The words are ordered alphabetically.

单词按字母顺序排列。

Here is my suggestion for the initial words list

这是我对初始单词列表的建议

The Word	The Reason why it is suspicious as SQL injection
Alter	Attempt to change database structure is not allowed
Begin	Begin of TSQL block inside dynamic T-SQL is not allowed
Break	Usage of control flow inside dynamic SQL is not allowed
Checkpoint	Attempt to control transaction behavior is not allowed
Commit	Attempt to control transaction behavior is not allowed
Create	Attempt to change database structure is not allowed
Cursor	Attempt to use cursors within Dynamic T-SQL execution is not allowed
DBCC	Using administrative Database consistency checker commands (DBCC) is not allowed
Deny	Attempt to change any database permissions within Dynamic T-SQL not allowed
Drop	Attempt to change database structure is not allowed
Exec	Attempt to execute within Dynamic T-SQL not allowed
Execute	Attempt to execute within Dynamic T-SQL not allowed
Insert	Attempt to change the existing data within dynamic T-SQL Is not allowed
Go	Attempt for multiple batch scripts within Dynamic T-SQL not allowed
Grant	Attempt to change any database permissions within Dynamic T-SQL not allowed
Opendatasource	Usage of distributed transaction row set function within Dynamic T-SQL not allowed
Openquery	Usage of distributed transaction row set function within Dynamic T-SQL not allowed
Openrowset	Usage of distributed transaction row set function within Dynamic T-SQL not allowed
Shutdown	Attempt to control server behavior is strictly not allowed
Sp_	Any attempt to execute a system stored procedure within Dynamic T-SQL not allowed
Tran	Attempt to control transaction behavior is not allowed
Transaction	Attempt to control transaction behavior is not allowed
Update	Attempt to change the existing data within dynamic T-SQL is not allowed
While	Usage of control flow inside dynamic SQL is not allowed
;	Any attempt to concatenate T-SQL commands within a single T-SQL command is not allowed
—	Entering comment marks inside with dynamic T-SQL command is a common SQL injection technique and is not allowed
Xp_	Any attempt to execute an extended and/or system stored procedure within Dynamic T-SQL not allowed

这个单词	原因W¯¯HY 这 是 可疑 的 SQL注入
改变	不允许尝试更改数据库结构
开始	不允许在动态T-SQL内开始TSQL块
打破	不允许在动态SQL中使用控制流
检查站	禁止控制交易行为
承诺	禁止控制交易行为
创造	不允许尝试更改数据库结构
光标	尝试在动态T-SQL执行中尝试使用游标
数据库管理中心	不允许使用管理数据库一致性检查器命令（DBCC）
拒绝	尝试更改动态T-SQL中的任何数据库权限
下降	不允许尝试更改数据库结构
执行力	尝试在动态T-SQL中执行
执行	尝试在动态T-SQL中执行
插	尝试更改动态T-SQL中的现有数据
走	不允许在动态T-SQL中尝试多个批处理脚本
格randint	尝试更改动态T-SQL中的任何数据库权限
开放数据源	不允许在动态T-SQL中使用分布式事务行集功能
打开查询	不允许在动态T-SQL中使用分布式事务行集功能
开放行	不允许在动态T-SQL中使用分布式事务行集功能
关掉	严禁尝试控制服务器行为
Sp_	不允许在动态T-SQL中执行系统存储过程的任何尝试
特兰	禁止控制交易行为
交易	禁止控制交易行为
更新资料	不允许尝试在动态T-SQL中更改现有数据
而	不允许在动态SQL中使用控制流
;	不允许在单个T-SQL命令中连接T-SQL命令的任何尝试
-	使用动态T-SQL命令在内部输入注释标记是一种常见SQL注入技术，不允许使用
Xp_	不允许在动态T-SQL中执行扩展和/或系统存储过程的任何尝试

Here is the Script for the table creation in master database (I called the table ReservedWords)

这是在主数据库中创建表的脚本（我称为表ReservedWords ）

 
use master
go
Create Table ReservedWords 
( id    int identity not null PRIMARY KEY, word  varchar(20))
go
Insert Into ReservedWords 
values (' alter '),
       (' begin '),
	(' break '),
	(' checkpoint '),
	(' commit '),
       (' create '),
	(' cursor '),
       (' dbcc '),
	(' deny '), 
       (' drop '),
	(' escape '),
	(' exec '), 
	(' execute '),
       (' insert '),
       (' go '),
	(' grant '),
	(' opendatasource '),
	(' openquery '),
	(' openrowset '),
	(' shutdown '),
       (' sp_'),
	(' tran '),
	(' transaction '),
	(' update '),
	(' while '),
       (' xp_'),
	(';'),
       ('--')
 

Here is the user defined function T-SQL code:

这是用户定义的函数T-SQL代码：

 
use master
go
Create Function dbo.VerifySQLInjection (@TSQL varchar(max))
Returns bit
AS
BEGIN
    DECLARE @IsSQLInjectionSuspected bit;
    DECLARE @pos int;
    set @pos = 0;
    select @pos += charIndex (lower(word) , lower(@TSQL)) 
    from ReservedWords
    set @IsSQLInjectionSuspected = if ((@pos > 0) ,1 ,0)
    RETURN @IsSQLInjectionSuspected
END
GO
 

Explanation for the code:

代码说明：

I called the function VerifySQLInjection. It gets a varchar(max) Dynamic T-SQL string to be inspected for SQL injection.

我调用了函数VerifySQLInjection 。 它获取一个varchar（max）动态T-SQL字符串，以检查是否有SQL注入。

The value returned is bit. 0 for a non-suspicious SQL injected string and 1 for a suspected one. The function uses the charIndex T-SQL built in function in order to check all the reserved words list inside the searched TSQL string.

返回的值是位。 对于非可疑SQL注入字符串，为0；对于可疑字符串，为1。 该函数使用charIndex T-SQL内置函数来检查搜索到的TSQL字符串中的所有保留字列表。

If one or more word is found inside the string, the charIndex function will return a positive value (the position inside the string) making the result a positive , greater than zero result.

如果在字符串中找到一个或多个单词， char I ndex函数将返回一个正值（字符串内的位置），使结果为正，大于零。

It the result of all charindex inspections turns out to be greater than zero then the function returns 1 and otherwise 0.

如果所有charindex检查的结果都大于零，则该函数返回1，否则返回0。

Here are some Tests for the function execution:

以下是一些有关函数执行的测试：

Statement	Result
SELECT master.dbo.VerifySQLInjection(‘select productname from Northwind.dbo.products’)	0
SELECT master.dbo.VerifySQLInjection(‘alter table Northwind.dbo.products alter column photo varbinary(max)’)	1
SELECT master.dbo.VerifySQLInjection(‘;shutdown’)	1
SELECT master.dbo.VerifySQLInjection(‘exec sp_helpdb’)	1

声明	结果
SELECT大师。 dbo 。 VerifySQLInjection （ '从Northwind.dbo.products中选择产品名称 ）	0
SELECT大师。 dbo 。 VerifySQLInjection （ '更改表Northwind.dbo.products更改列照片varbinary（max）' ）	1个
SELECT大师。 dbo 。 VerifySQLInjection （ '; shutdown' ）	1个
SELECT大师。 dbo 。 VerifySQLInjection （ 'exec sp_helpdb' ）	1个

翻译自: https://www.sqlshack.com/creating-sql-injection-protection-function/

造成sql注入的功能点