Page History: SQL 인젝션
Compare Page Revisions
Page Revision: 2010/10/22 16:34
텍 스트 제외 쿼리
{{DECLARE @T varchar(255), @C varchar(255);
DECLARE Table_Cursor CURSOR FOR
SELECT a.name, b.name
FROM sysobjects a, syscolumns b
WHERE a.id = b.id AND a.xtype = 'u' AND
(b.xtype = 99 OR
b.xtype = 35 OR
b.xtype = 231 OR
b.xtype = 167);
OPEN Table_Cursor;
FETCH NEXT FROM Table_Cursor INTO @T, @C;
WHILE (
FETCH_STATUS = 0) BEGIN
EXEC(
'Update ['+@T+'] set ['+@C+']=replace(['+@C+'], ''<script src=http://pcca4.cn></script>'', '''')where ['+@C+'] like ''%<script%</script>'''
);
FETCH NEXT FROM Table_Cursor INTO @T, @C;
END;
CLOSE Table_Cursor;
DEALLOCATE Table_Cursor;}}
----
텍스트 포함 쿼리 - 테이블명과 칼럼명을 수동으로 집어너야됨
Declare @replacestr varchar(10)
Declare @deletestr varchar(100)
Declare @ptr varbinary(16)
Declare @len int
Declare @offset int
Declare @cnt int
Set @deletestr = 'http://dev-server:10001'
Set @replacestr = ''
Select @ptr = textptr(p_contents) From tb_product_list Where p_code='P000000005'
Set @len = len(@deletestr)
Set @cnt = 0
While(1=1) begin --일단 무한 loop로 돌려놓고
Select @offset=patindex('%'+@deletestr+'%', p_contents) From tb_product_list Where p_code='P000000005'
If @offset = 0 Begin
break
End
Else begin
Set @offset = @offset - 1
Updatetext tb_product_list.p_contents @ptr @offset @len @replacestr
Set @cnt = @cnt + 1
End
End
Select convert(varchar(10), @cnt) + ' 개 replace'
----
8000자가 넘는지 검사하는 쿼리
0이면 업는것이고 1이면 한개가 있다는 말
DECLARE @T varchar(255), @C varchar(255);
DECLARE Table_Cursor CURSOR FOR
SELECT a.name, b.name
FROM sysobjects a, syscolumns b
WHERE a.id = b.id AND a.xtype = 'u' AND
(b.xtype = 99 OR
b.xtype = 35 OR
b.xtype = 231 OR
b.xtype = 167);
OPEN Table_Cursor;
FETCH NEXT FROM Table_Cursor INTO @T, @C;
WHILE (FETCH_STATUS = 0) BEGIN
EXEC(
'SELECT count(*) FROM
'+@T+' where
'+@C+' like
% AND len(convert(varchar(8000),
'+@C+'))=
8000 '
);
FETCH NEXT FROM Table_Cursor INTO @T, @C;
END;
CLOSE Table_Cursor;
DEALLOCATE Table_Cursor;
8000자가 안될때 텍스트 형식 으로된거 제거
DECLARE @T varchar(255), @C varchar(255);
DECLARE Table_Cursor CURSOR FOR
SELECT a.name, b.name
FROM sysobjects a, syscolumns b
WHERE a.id = b.id AND a.xtype = 'u' AND
(b.xtype = 99 OR
b.xtype = 35 OR
b.xtype = 231 OR
b.xtype = 167);
OPEN Table_Cursor;
FETCH NEXT FROM Table_Cursor INTO @T, @C;
WHILE (@@FETCH_STATUS = 0) BEGIN
EXEC(
'update
'+@T+' set
'+@C+' = left(
convert(varchar(8000),
'+@C+'),
len(convert(varchar(8000),
'+@C+')) - 6 -
patindex(
%tpircs<%,
reverse(convert(varchar(8000),
'+@C+')))
)
where
'+@C+' like
%'
);
FETCH NEXT FROM Table_Cursor INTO @T, @C;
END;
CLOSE Table_Cursor;
DEALLOCATE Table_Cursor;