SQL 注入与防御实例

浏览数：36 / 时间：2015年06月12日

注入

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‘;