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注入吗?请问该如何修改呢??
@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,'''','''''')