如何防止存储过程拼接字符串的sql注入问题？？？

[qq307269906]

ALTER PROCEDURE [dbo].[np_click_detail_add]
@visitor_token BIGINT,
@visitor_session BIGINT,
@remote_ip VARCHAR(15),
@user_id VARCHAR(50),
@view_page VARCHAR(255),
@ref_page VARCHAR(255),
@ref_ad VARCHAR(32),
@create_date DATETIME,
@vistior_type INT

AS
BEGIN

SET NOCOUNT ON

declare @sql nvarchar(2000)
declare @table_name varchar(100)
DECLARE @log_date varchar(8)
SET @log_date = CONVERT(varchar(100), @create_date, 112)

set @table_name='nt_click_log_'+@log_date
set @sql= 'insert '+ @table_name +' (
visitor_token,
visitor_session,
remote_ip,
user_id,
view_page,
ref_page,
ref_ad,
create_date,
create_datetime,
visitor_type
) '
+'values ('+
CONVERT(varchar(20),@visitor_token)+
','+CONVERT(varchar(20),@visitor_session) +
','''+@remote_ip +
''','''+@user_id +
''','''+@view_page +
''','''+@ref_page +
''','''+@ref_ad +
''','''+CONVERT(varchar(8), @create_date, 112)+
''','''+CONVERT(varchar(30),@create_date,121) +
''','+CONVERT(varchar(20),@vistior_type) +')'
BEGIN TRY
exec sp_executesql @sql
END TRY
BEGIN CATCH
exec nt_add_click_table_today @log_date
exec sp_executesql @sql
END CATCH
SET NOCOUNT OFF


这样的存储过程会产生sql注入吗？请问该如何修改呢？？

[还在加载中灬]

其实防注入，只要做好单引号的过滤就行了

--这个应该是什么IP吧，不需要什么单引号，直接过滤
SET @remote_ip=REPLACE(@remote_ip,'''','')

--后面这些，把单引号变成两个单引号，这样就不会被注入了
SET @user_id=REPLACE(@user_id,'''','''''')
SET @view_page=REPLACE(@view_page,'''','''''')
SET @ref_page=REPLACE(@ref_page,'''','''''')
SET @ref_ad=REPLACE(@ref_ad,'''','''''')

[--小F--]

http://bbs.csdn.net/topics/290032853

[--小F--]

http://bbs.csdn.net/topics/290032853

[江南小鱼]

程序调用处理一下就成，别在存储过程折腾了

/// <summary>
        /// 检查是否有注入攻击
        /// </summary>
        /// <param name="Content"></param>
        /// <returns></returns>
        public static string SafeSQL(string Content)
        {
            Content = Content.Replace("'", "''");
            Content = Content.Replace("-", "");
            Content = Content.Replace("*", "");
            Content = Content.Replace(" ", "");
            Content = Content.Replace("\"", "");
            Content = Content.Replace("[", "");
            Content = Content.Replace("]", "");
            Content = Content.Replace("%", "");
            Content = Content.Replace(";", "");
            Content = Content.Replace(":", "");
            Content = Content.Replace("+", "");
            Content = Content.Replace("{", "");
            Content = Content.Replace("}", "");

            return Content;
        }