This website requires JavaScript.

SQL 注入与防御实例

注入

1. 创建存储

USE TSQL2012; GO IF OBJECT_ID('Sales.ListCustomersByAddress') IS NOT NULL DROP PROCEDURE Sales.ListCustomersByAddress; GO CREATE PROCEDURE Sales.ListCustomersByAddress @address NVARCHAR(60) AS DECLARE @SQLString AS NVARCHAR(4000); SET @SQLString = N' SELECT companyname, contactname FROM Sales.Customers WHERE address = ''' + @address + ''''; -- PRINT @SQLString; EXEC(@SQLString); RETURN; GO

2\. 执行正常查询
EXEC Sales.ListCustomersByAddress @address = N'8901 Tsawassen Blvd.';
3\. 输入一个单引号进去。 此时因有个引号没转义所以报错了
EXEC Sales.ListCustomersByAddress @address = N'''';
SELECT companyname, contactname 
FROM Sales.Customers WHERE address = '''

Msg 105, Level 15, State 1, Line 3

Unclosed quotation mark after the character 
string '''. Msg 102, Level 15, State 1, Line 3

Incorrect syntax near 
'''.
4\. 输入--把最后一个单引号注释掉
EXEC Sales.ListCustomersByAddress @address = N''' -- ';

--拼接语句如下 
SELECT companyname, contactname 
FROM Sales.Customers WHERE address = '' – '
5\. 注入一个SQL语句,然后你懂得。
EXEC Sales.ListCustomersByAddress @address = N''' SELECT 1 -- ';

--拼接语句如下
SELECT companyname, contactname 
FROM Sales.Customers WHERE address = '' SELECT 1 -- '

防止

1. 修改存储,使用 sp_executesql

USE TSQL2012; GO IF OBJECT_ID('Sales.ListCustomersByAddress') IS NOT NULL DROP PROCEDURE Sales.ListCustomersByAddress; GO CREATE PROCEDURE Sales.ListCustomersByAddress @address AS NVARCHAR(60) AS DECLARE @SQLString AS NVARCHAR(4000); SET @SQLString = N' SELECT companyname, contactname FROM Sales.Customers WHERE address = @address'; EXEC sp_executesql @statement = @SQLString , @params = N'@address NVARCHAR(60)' , @address = @address; RETURN; GO

2\. 正常查询
EXEC Sales.ListCustomersByAddress @address = N'8901 Tsawassen Blvd.';
3\. 注入尝试
EXEC Sales.ListCustomersByAddress @address = N'''';
EXEC Sales.ListCustomersByAddress @address = N''' -- ';
EXEC Sales.ListCustomersByAddress @address = N''' SELECT 1 -- ';

其他

QUOTENAME

这个函数用来给字符串两边加括号,引号等符号。

quotename函数有几种写法: quotename('aa') 生成的有效的标识符为 [aa] quotename('aa','') 生成的有效的标识符为 [aa] quotename('aa','''') 生成的有效的标识符为 'aa'

DECLARE @address AS NVARCHAR(60) = '5678 rue de l''Abbaye'; PRINT N'SELECT * FROM [Sales].[Customers] WHERE address = '+ QUOTENAME(@address, '''') + ';';

**Output 参数**
DECLARE @SQLString AS NVARCHAR(4000)
    , @outercount AS int; 
SET @SQLString = N'SET @innercount = (SELECT COUNT(*) FROM Production.Products)';
EXEC sp_executesql 
    @statment = @SQLString
    , @params = N'@innercount AS int OUTPUT'
    , @innercount = @outercount OUTPUT;
SELECT @outercount AS  'RowCount';
### 参考资料
SQL Injection

https://msdn.microsoft.com/en-us/library/ms161953(SQL.105).aspx

sp_executesql (Transact-SQL)

https://msdn.microsoft.com/zh-cn/library/ms188001.aspx

0条评论
avatar