— 检查数据库版本
SELECT @@VERSION;
GO

— 检查数据库配置
EXEC sp_configure;
GO

— 检查数据库用户权限
SELECT
dp.name AS principal_name,
dp.type_desc AS principal_type,
o.name AS object_name,
p.permission_name,
p.state_desc
FROM
sys.database_principals dp
JOIN
sys.database_permissions p ON dp.principal_id = p.grantee_principal_id
JOIN
sys.objects o ON p.major_id = o.object_id
WHERE
dp.type IN (‘S’, ‘U’, ‘G’);
GO

— 步骤1：收集数据库对象信息
SELECT
o.name AS object_name,
o.type_desc AS object_type,
o.create_date,
o.modify_date
FROM
sys.objects o
WHERE
o.type IN (‘P’, ‘FN’, ‘IF’, ‘TF’, ‘TR’)
ORDER BY
o.type_desc, o.name;
GO

— 步骤2：检查存储过程代码
SELECT
OBJECT_NAME(object_id) AS procedure_name,
definition
FROM
sys.sql_modules
WHERE
object_id IN (
SELECT object_id FROM sys.objects WHERE type = ‘P’
);
GO

— 步骤3：检查函数代码
SELECT
OBJECT_NAME(object_id) AS function_name,
definition
FROM
sys.sql_modules
WHERE
object_id IN (
SELECT object_id FROM sys.objects WHERE type IN (‘FN’, ‘IF’, ‘TF’)
);
GO

— 步骤4：检查触发器代码
SELECT
OBJECT_NAME(object_id) AS trigger_name,
definition
FROM
sys.sql_modules
WHERE
object_id IN (
SELECT object_id FROM sys.objects WHERE type = ‘TR’
);
GO

— 检查存储过程中的SQL注入风险
SELECT
OBJECT_NAME(object_id) AS procedure_name,
definition
FROM
sys.sql_modules
WHERE
definition LIKE ‘%EXEC(”SELECT%”%’ OR
definition LIKE ‘%EXEC(”INSERT%”%’ OR
definition LIKE ‘%EXEC(”UPDATE%”%’ OR
definition LIKE ‘%EXEC(”DELETE%”%’;
GO

— 检查存储过程中的权限问题
SELECT
OBJECT_NAME(object_id) AS procedure_name,
definition
FROM
sys.sql_modules
WHERE
definition LIKE ‘%GRANT%’ OR
definition LIKE ‘%REVOKE%’ OR
definition LIKE ‘%DENY%’;
GO

— 检查存储过程中的性能问题
SELECT
OBJECT_NAME(object_id) AS procedure_name,
definition
FROM
sys.sql_modules
WHERE
definition LIKE ‘%SELECT *%’ OR
definition LIKE ‘%LIKE ”%”%’ OR
definition LIKE ‘%ORDER BY NEWID()%’;
GO

学习交流加群风哥QQ113257174。

Part04-生产案例与实战讲解

4.1 SQLServer代码审计实战案例

— 案例1：检查存储过程中的SQL注入风险
— 有问题的存储过程
CREATE PROCEDURE dbo.usp_get_user_by_name
@user_name NVARCHAR(50)
AS
BEGIN
DECLARE @sql NVARCHAR(1000);
SET @sql = ‘SELECT * FROM dbo.fgedu_user_info WHERE user_name = ”’ + @user_name + ””;
EXEC(@sql);
END;
GO

— 修复后的存储过程
CREATE PROCEDURE dbo.usp_get_user_by_name
@user_name NVARCHAR(50)
AS
BEGIN
SELECT * FROM dbo.fgedu_user_info WHERE user_name = @user_name;
END;
GO

— 案例2：检查存储过程中的性能问题
— 有问题的存储过程
CREATE PROCEDURE dbo.usp_get_order_by_date
@start_date DATETIME,
@end_date DATETIME
AS
BEGIN
SELECT * FROM dbo.fgedu_order WHERE order_date BETWEEN @start_date AND @end_date;
END;
GO

— 修复后的存储过程
CREATE PROCEDURE dbo.usp_get_order_by_date
@start_date DATETIME,
@end_date DATETIME
AS
BEGIN
SELECT
order_id,
order_code,
order_date,
total_amount,
order_status
FROM
dbo.fgedu_order
WHERE
order_date BETWEEN @start_date AND @end_date;
END;
GO

— 案例3：检查存储过程中的异常处理
— 有问题的存储过程
CREATE PROCEDURE dbo.usp_insert_user
@user_name NVARCHAR(50),
@user_email NVARCHAR(100)
AS
BEGIN
INSERT INTO dbo.fgedu_user_info (user_name, user_email) VALUES (@user_name, @user_email);
END;
GO

— 修复后的存储过程
CREATE PROCEDURE dbo.usp_insert_user
@user_name NVARCHAR(50),
@user_email NVARCHAR(100)
AS
BEGIN
BEGIN TRY
INSERT INTO dbo.fgedu_user_info (user_name, user_email) VALUES (@user_name, @user_email);
END TRY
BEGIN CATCH
DECLARE @error_message NVARCHAR(4000) = ERROR_MESSAGE();
RAISERROR(@error_message, 16, 1);
END CATCH;
END;
GO

— 创建代码审计存储过程
CREATE PROCEDURE dbo.usp_code_audit
AS
BEGIN
— 检查存储过程中的SQL注入风险
SELECT
‘SQL注入风险’ AS audit_type,
OBJECT_NAME(object_id) AS object_name,
‘存在动态SQL执行，可能导致SQL注入’ AS issue_description
FROM
sys.sql_modules
WHERE
definition LIKE ‘%EXEC(”SELECT%”%’ OR
definition LIKE ‘%EXEC(”INSERT%”%’ OR
definition LIKE ‘%EXEC(”UPDATE%”%’ OR
definition LIKE ‘%EXEC(”DELETE%”%’;

— 检查存储过程中的性能问题
SELECT
‘性能问题’ AS audit_type,
OBJECT_NAME(object_id) AS object_name,
‘使用SELECT *，可能导致性能问题’ AS issue_description
FROM
sys.sql_modules
WHERE
definition LIKE ‘%SELECT *%’;

— 检查存储过程中的异常处理
SELECT
‘异常处理’ AS audit_type,
OBJECT_NAME(object_id) AS object_name,
‘缺少异常处理，可能导致错误传播’ AS issue_description
FROM
sys.sql_modules
WHERE
object_id IN (
SELECT object_id FROM sys.objects WHERE type = ‘P’
)
AND definition NOT LIKE ‘%TRY%’ AND definition NOT LIKE ‘%CATCH%’;
END;
GO

— 执行代码审计
EXEC dbo.usp_code_audit;
GO

— 审计结果示例
/*
audit_type object_name issue_description
————- ——————– ——————————————
SQL注入风险 usp_get_user_by_name 存在动态SQL执行，可能导致SQL注入
性能问题 usp_get_order_by_date 使用SELECT *，可能导致性能问题
异常处理 usp_insert_user 缺少异常处理，可能导致错误传播
*/

风哥提示：定期执行代码审计，及时发现并修复代码中的问题。from SQLServer视频:www.itpux.com。

Part05-风哥经验总结与分享

5.1 SQLServer代码审计最佳实践

1. 制定代码审计规范，明确审计内容和标准

2. 定期进行代码审计，一般每季度或半年进行一次

3. 使用自动化工具辅助代码审计，提高审计效率

4. 建立代码审计结果跟踪机制，确保问题得到及时修复

5. 对开发人员进行代码规范培训，提高代码质量

6. 结合版本控制系统，对代码变更进行审计

7. 建立代码审计知识库，积累审计经验和最佳实践

5.2 SQLServer代码审计常见问题与解决方案

— 问题1：代码审计工作量大
— 解决方案：使用自动化工具，编写审计脚本

— 问题2：审计结果难以跟踪
— 解决方案：建立审计结果跟踪系统，记录问题和修复情况

— 问题3：开发人员对审计结果不重视
— 解决方案：将代码审计结果与绩效考核挂钩，提高开发人员的重视程度

— 问题4：审计标准不明确
— 解决方案：制定详细的审计标准文档，明确审计内容和判断标准

— 问题5：审计工具功能有限
— 解决方案：结合多种工具，或开发自定义审计工具

更多学习教程公众号风哥教程itpux_com。