ASP SQL Injection 방어 함수

ASP 1.0는 1996년 12월 IIS 3.0의 일부로 출시되었다
ASP 2.0은 1997년 9월 IIS 4.0의 일부로 출시되었다
ASP 3.0은 2000년 11월 IIS 5.0의 일부로 출시되었다

보안취약성은 asp가 이미 지원이 단종되고, 알려진 보안 취약성이 많기 때문에 앞으로 asp로 계속 운영하고 보안에 투자하는 금액보다 타언어로 전환되는 비용이 더 효과적이다.

<%
Function ReForm (sString , nMaxLen , isNum )
'// Request 로 들어온 변수를 처리한다.
'// sString : 넘겨받는 변수 (string)
'// nMaxLen : 최대 길이 (number)  (최대길이를 검사하지 않은경우 0)
'// isNum  : 숫자인지 아닌지 (1 : only number , 0 : 숫자판별 안함)

	Dim temp
	Dim nErr
	temp = Trim (sString ) & ""

	if isNum = 1 then	'숫자판별
		if isNumeric (temp) = False then
			response.write ( temp & " is Not Number " )
			response.End
		End if
	end if


	if nMaxLen > 0 then	'최대길이 판별
		if len(temp) > nMaxLen then
			response.write ( temp & " is over Maxlength " & nMaxLen  )
			response.end
		end if
	end if



	'// injection 관련 키워드 제거(항목 추가 가능)
	temp = Replace ( temp , "'" , "" )
	temp = Replace ( temp , "--" , "" )
	temp = Replace ( temp , "--, #" , " " )
	temp = Replace ( temp , "/* */" , " " )
	temp = Replace ( temp , "' or 1=1--" , " " )
	temp = Replace ( temp , "union" , " " )
	temp = Replace ( temp , "select" , " " )
	temp = Replace ( temp , "delete" , " " )
	temp = Replace ( temp , "insert" , " " )
	temp = Replace ( temp , "update" , " " )
	temp = Replace ( temp , "drop" , " " )
	temp = Replace ( temp , "on error resume" , " " )
	temp = Replace ( temp , "execute" , " " )
	temp = Replace ( temp , "windows" , " " )
	temp = Replace ( temp , "boot" , " " )
	temp = Replace ( temp , "-1 or" , " " )
	temp = Replace ( temp , "-1' or" , " " )
	temp = Replace ( temp , "../" , " " )
	temp = Replace ( temp , "unexisting" , " " )
	temp = Replace ( temp , "win.ini" , " " )
	
	ReForm = temp

End Function

%>

SQL Injection 방어 함수 적용

<%

	param1 = ReForm(request.Form("param1"),0,0)

%>​

파라미터 처리

array_split_item = Array(";", "/*", "*/", "@@", "char", "nchar", "varchar", "nvarchar", "alter", "begin", "cast", "create", "cursor", "declare", "delete", "drop", "end", "exec","execute", "fetch", "insert", "kill", "open","select", "sys", "sysobjects", "syscolumns","table", "update", "<script", "</script>", "'")

' 2014-02-07 일자로 인해 하이픈 제거
' 2014-02-07 이메일주소로 인해 @ 제거

for each item in Request.QueryString
for array_counter = lbound(array_split_item) to ubound(array_split_item)
item_position1 = InStr(lcase(Request(item)), array_split_item(array_counter))
item_position2 = InStr(lcase(Request.QueryString), array_split_item(array_counter))
'Response.Write(array_split_item(array_counter) & "<BR>")
if (item_position1 > 0) or (item_position2 > 0) then
     response.write "<script Language=javascript>"
  response.write "alert('잘못된 값이 존재하여 페이지를 종료 합니다.(" & array_split_item(array_counter) & ")');"
  response.write "history.go(-1)"
  response.write "</script>"
  Response.End()
end if
next
Next

for each item in Request.Form
for array_counter = lbound(array_split_item) to ubound(array_split_item)
item_position1 = InStr(lcase(Request(item)), array_split_item(array_counter))
item_position2 = InStr(lcase(Request.Form), array_split_item(array_counter))
'Response.Write(array_split_item(array_counter) & "<BR>")
if (item_position1 > 0) or (item_position2 > 0) then
     response.write "<script Language=javascript>"
  response.write "alert('잘못된 값이 존재하여 페이지를 종료 합니다.(" & array_split_item(array_counter) & ")');"
  response.write "history.go(-1)"
  response.write "</script>"
  Response.End()
end if
next
Next 

SQL

Create FindReplace stored procedure

create PROCEDURE FindReplace
(
@TABLE VARCHAR(200),
@Field VARCHAR(200),
@WHERE VARCHAR(100),
@Find VARCHAR(500),
@REPLACE VARCHAR(500)
)
AS
DECLARE @query VARCHAR(8000)
SET @query = 'UPDATE ' + @TABLE +
' SET ' + @Field + '= REPLACE(CONVERT(varchar(8000),'
+ @Field + '),''' + @Find + ''',''' + @REPLACE +''')'
IF(@WHERE <> '')
SET @query = @query + ' WHERE '+@WHERE

EXECUTE (@query)
GO

Create RemoveStringFinal Procedure

create Procedure RemoveStringFinal
@FIND VARCHAR(500),
@REPLACE VARCHAR(500)
as

DECLARE @TABLE_NAME VARCHAR(500)
DECLARE @COLUMN_NAME VARCHAR(500)
DECLARE @DATA_TYPE VARCHAR(500)
DECLARE db_cursor CURSOR FOR
select TABLE_NAME, COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS


 
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @TABLE_NAME, @COLUMN_NAME, @DATA_TYPE


 
WHILE @@FETCH_STATUS = 0
BEGIN
if @DATA_TYPE = 'varchar' or @DATA_TYPE = 'text' or @DATA_TYPE = 'ntext' or @DATA_TYPE = 'nvarchar'
begin

print @TABLE_NAME 
        print @COLUMN_NAME 
print @DATA_TYPE 

          EXEC FindReplace @TABLE_NAME,@COLUMN_NAME,'',@FIND,@REPLACE 

      end      

  FETCH NEXT FROM db_cursor INTO @TABLE_NAME, @COLUMN_NAME, @DATA_TYPE 
END

CLOSE db_cursor
DEALLOCATE db_cursor


Run stored procedure example

EXEC RemoveStringFinal ' ',''
EXEC RemoveStringFinal ' ',''
EXEC RemoveStringFinal ' ',''
EXEC RemoveStringFinal ' ',''
EXEC RemoveStringFinal ' ',''