ASP

 

MS -SQL 테이블 생성

create table usert (
    id int identity(1,1) primary key,
    name varchar(50) null,
    sex  varchar(10) null,
    memo nvarchar(200) null

);

 

 

1. DB 연결 루틴 만들기

 

dbcon0.asp 

<%
  ' "ODBC 를 사용할 때"
  ' dsn="aspbasic1dsn"
  ' dsnid="aspbasic"
  ' dsnpasswd="aspbasic"

  ' Set db =Server.CreateObject("ADODB.Connection")
  ' db.Open("DSN="+dsn +";UID="+dsnid+";PWD="+dsnpasswd)

  ' ""DBQ를 사용할때
  ' ""dbcon.asp 파일을 자동으로 찾는 루틴이다. '''
  path1=request.ServerVariables("PATH_TRANSLTED")
  path2=Split(path1, "\")
  i=Unbound(path2)
  path3=replace(path1, path2(i), "")

  ' path3=replace(path3, path2(i-1)+"\,")
  ' if path2(i-1)="shop" then
  ' path3 =replace(path3, path2(i-2) + "\", "")
  ' ElseIf path2(i-1)="admin" then
  ' path3=replace(path3, path2(i-2),"\", "")
  ' end If

  db_path =path3 + "aspbasic.mdb"

  db_path ="c:\inetpub\wwwroot\aspbasic1\aspbasic.mdb"

  Set db =Sever.CreateObject("ADODB.Connection")
  db.Open("Driver={Microsoft Access Driver (*.mdb)}; DBQ="+db_path)

%>

 

dbcon.asp

<%

     dim dbConnect, db '변수 선언
     ' DB 연결 접속 정보 변수 저장
     'dbConnect = "Provider=SQLOLEDB; Data Source=NITRO-PC\SQLEXPRESS;Initial Catalog=macaronics; User id=braverokmc;password=1111"
      dbConnect = "Provider=SQLOLEDB; Data Source=CJH-PC;Initial Catalog=braverokmc; User id=braverokmc;password=1111"
     ' 연결 객체 생성
     set db = server.createObject("ADODB.Connection")
     ' 데이터베이스 접속
     db.open dbConnect



     '
     ' sql="select * from usert where id=1 "
     ' Set RS = db.Execute(sql)
     '
     ' Response.write RS("name")



%>

 

 

2. 등록 폼 만들기 

 

inputform.asp

<!--#include file=./include/header.asp  -->
<script>
function checkInput(theForm){

    if(theForm.name.value.length==0){
      alert("이름을 입력하세요.");
      theForm.name.focus();
      return false;
    }

    if(theForm.sex.value.length==0){
      alert("성별을 입력하세요. ");
      theForm.sex.foucus();
      return false;
    }

    return true;

}

</script>
</head>
<body>

<p>&nbsp;</p>
<p class="text-center"><font size="5"><b>등록폼</b></font></p>
<div class="rows">
  <div class="col-xs-12 col-sm-12">
      <div class="col-xs-3 col-sm-3"></div>
      <div class="col-xs-6 col-sm-6">

        <form method="post" action="input.asp" onsubmit="return checkInput" class="form">
          <table class="table">
                  <tr>
                    <th>이름</th>
                    <td>
                      <input type="text" name="name" class="form-control"  />
                    </td>
                  </tr>

                  <tr>
                    <th>성별</th>
                    <td>
                      <input type="text" name="sex" class="form-control"  />
                    </td>
                  </tr>
                  <tr>
                     <th>메모</th>
                    <td>
                        <textarea name="memo" class="form-control" cols="10" rows="10"></textarea>
                    </td>
                  </tr>
                    <tr>
                        <td colspan="2" class="text-center">
                              <input type="submit" value="등록하기"  class="btn-success" />
                        </td>
                    </tr>
          <table>
        </form>

      </div>

  </div>
</div>


</body>
</html>

 

 

등록 루틴 만들기

 

 

 

header.asp

<%@Language="VBScript" CODEPAGE="65001" %>
<% Response.CharSet="utf-8"
   Session.codepage="65001"
   Response.codepage="65001"
   Response.ContentType="text/html;charset=utf-8"
%>
<html lang="ko">
<head>
<title>macaronics</title>
   <meta http-equiv="content-type" content="text/html"; charset="utf-8" >
   <meta http-equiv="X-UA-Compatible" content="IE=edge">
   <meta name="viewport" content="width=device-width, initial-scale=1">
   <!-- 위 3개의 메타 태그는 *반드시* head 태그의 처음에 와야합니다; 어떤 다른 콘텐츠들은 반드시 이 태그들 *다음에* 와야 합니다 -->
   <title>macaronics</title>
   <!-- 부트스트랩 -->
   <!-- 합쳐지고 최소화된 최신 CSS -->
   <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.2/css/bootstrap.min.css">
   <!-- 부가적인 테마 -->
   <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.2/css/bootstrap-theme.min.css">
   <!-- 합쳐지고 최소화된 최신 자바스크립트 -->
   <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.2/js/bootstrap.min.js"></script>
   <!-- IE8 에서 HTML5 요소와 미디어 쿼리를 위한 HTML5 shim 와 Respond.js -->
   <!-- WARNING: Respond.js 는 당신이 file:// 을 통해 페이지를 볼 때는 동작하지 않습니다. -->
   <!--[if lt IE 9]>
     <script src="https://oss.maxcdn.com/html5shiv/3.7.2/html5shiv.min.js"></script>
     <script src="https://oss.maxcdn.com/respond/1.4.2/respond.min.js"></script>
   <![endif]-->

 

Injection.asp

Xss 보안 , SQL Injection 보안

<%
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

Function Check_sql(str)
   Dim result_str
   SQL_Val = str
   SQL_Val = Replace(SQL_Val, ";", " ")
   SQL_Val = Replace(SQL_Val, "@variable", " ")
   SQL_Val = Replace(SQL_Val, "@@variable", " ")
   SQL_Val = Replace(SQL_Val, "+", " ")
   SQL_Val = Replace(SQL_Val, "print", " ")
   SQL_Val = Replace(SQL_Val, "set", " ")
   SQL_Val = Replace(SQL_Val, "%", " ")
   SQL_Val = Replace(SQL_Val, "<script>", " ")
   SQL_Val = Replace(SQL_Val, "<SCRIPT>", " ")
   SQL_Val = Replace(SQL_Val, "script", " ")
   SQL_Val = Replace(SQL_Val, "SCRIPT", " ")
   SQL_Val = Replace(SQL_Val, "or", " ")
   SQL_Val = Replace(SQL_Val, "union", " ")
   SQL_Val = Replace(SQL_Val, "and", " ")
   SQL_Val = Replace(SQL_Val, "insert", " ")
   SQL_Val = Replace(SQL_Val, "openrowset", " ")
   SQL_Val = Replace(SQL_Val, "xp_", " ")
   SQL_Val = Replace(SQL_Val, "decare", " ")
   SQL_Val = Replace(SQL_Val, "select", " ")
   SQL_Val = Replace(SQL_Val, "update", " ")
   SQL_Val = Replace(SQL_Val, "delete", " ")
   SQL_Val = Replace(SQL_Val, "shutdown", " ")
   SQL_Val = Replace(SQL_Val, "drop", " ")
   SQL_Val = Replace(SQL_Val, "--", " ")
   SQL_Val = Replace(SQL_Val, "/*", " ")
   SQL_Val = Replace(SQL_Val, "*/", " ")
   SQL_Val = Replace(SQL_Val, "XP_", " ")
   SQL_Val = Replace(SQL_Val, "DECLARE", " ")
   SQL_Val = Replace(SQL_Val, "SELECT", " ")
   SQL_Val = Replace(SQL_Val, "UPDATE", " ")
   SQL_Val = Replace(SQL_Val, "DELETE", " ")
   SQL_Val = Replace(SQL_Val, "INSERT", " ")
   SQL_Val = Replace(SQL_Val, "SHUTDOWN", " ")
   SQL_Val = Replace(SQL_Val, "DROP", " ")

   result_str = removeXSS(SQL_Val)
   Check_sql = result_str
End Function

Function removeXSS(get_String)
   get_String = Replace(get_String, "&", "&amp;")
   get_String = Replace(get_String, "<xmp", "<x-xmo", 1, -1, 1)
   get_String = Replace(get_String, "javascript", "<x-javascript", 1, -1, 1)
   get_String = Replace(get_String, "script", "<x-script", 1, -1, 1)
   get_String = Replace(get_String, "iframe", "<x-iframe", 1, -1, 1)
   get_String = Replace(get_String, "document", "<x-document", 1, -1, 1)
   get_String = Replace(get_String, "vbscript", "<x-vbscript", 1, -1, 1)
   get_String = Replace(get_String, "applet", "<x-applet", 1, -1, 1)
   get_String = Replace(get_String, "embed", "<x-embed", 1, -1, 1)
   get_String = Replace(get_String, "object", "<x-object", 1, -1, 1)
   get_String = Replace(get_String, "frame", "<x-frame", 1, -1, 1)
   get_String = Replace(get_String, "grameset", "<x-grameset", 1, -1, 1)
   get_String = Replace(get_String, "layer", "<x-layer", 1, -1, 1)
   get_String = Replace(get_String, "bgsound", "<x-bgsound", 1, -1, 1)
   get_String = Replace(get_String, "alert", "<x-alert", 1, -1, 1)
   get_String = Replace(get_String, "onblur", "<x-onblur", 1, -1, 1)
   get_String = Replace(get_String, "onchange", "<x-onchange", 1, -1, 1)
   get_String = Replace(get_String, "onclick", "<x-onclick", 1, -1, 1)
   get_String = Replace(get_String, "ondblclick","<x-ondblclick",  1, -1, 1)
   get_String = Replace(get_String, "enerror", "<x-enerror", 1, -1, 1)
   get_String = Replace(get_String, "onfocus", "<x-onfocus", 1, -1, 1)
   get_String = Replace(get_String, "onload", "<x-onload", 1, -1, 1)
   get_String = Replace(get_String, "onmouse", "<x-onmouse", 1, -1, 1)
   get_String = Replace(get_String, "onscroll", "<x-onscroll", 1, -1, 1)
   get_String = Replace(get_String, "onsubmit", "<x-onsubmit", 1, -1, 1)
   get_String = Replace(get_String, "onunload", "<x-onunload", 1, -1, 1)
   get_String = Replace(get_String, "<", "&lt;")
   get_String = Replace(get_String, ">", "&gt;")
   removeXSS = get_String
End Function

Function mSecurity(get_String)
    Dim result1, result2
    result1=removeXSS(get_String)
    result2=Check_sql(result1)
    mSecurity=result2
End Function

%>
<!--
SQL Injection 방어 함수 적용
(ReForm 함수 사용)
<%
	param1 = ReForm(request.Form("param1"),0,0)
%>
-->

 

 

input.asp

<!--#include file=./include/header.asp  -->
<!-- SQL Injection 공격 방어 함수 -->
<!--#include file=./include/Injection.asp -->
</head>
<body>
<%
 name=mSecurity(request("name"))
 sex=mSecurity(request("sex"))
 memo=mSecurity(request("memo"))
%>
<!--#include virtual=./asp-shoppingMall/braverokmc/dbcon.asp -->
<%
    sql1="insert into  usert(name, sex, memo) values('"&name & "','"& sex & "' , '" & memo & "')"
    Set rs1=db.Execute(sql1)

%>
<p>&nbsp;</p>
<p class="text-center">등록결과</p>

<div class="rows">
  <div class="col-xs-12 col-sm-12">
      <div class="col-xs-3 col-sm-3"></div>
      <div class="col-xs-6 col-sm-6">
          <p class="text-center">다음과 같이 등록되었습니다.</p>
          <%
             sql2="select * from usert where name='"& name &"'"
             Set rs2 =db.Execute(sql2)

          %>
          <table class="table">
              <tr>
                <td>이름 :</td>
                 <td><%= rs2("name") %></td>
              </tr>

              <tr>
                <td>성별 :</td>
                <td><%= rs2("sex") %></td>
              </tr>
              <tr>
                <td>메모 : </td>
                <td>
                <%= rs2("memo")%> 
                </td>
              </tr>
              <tr>
                <td colspan="2">
                   <a href="inputform.asp">이전화면으로</a>
                </td>
              </tr>
          </table>

      </div>

  </div>

</div>



</body>
</html>

 

 

목록보기 

<!--#include file=./include/header.asp  -->
</head>
<body>
<!--#include file=dbcon.asp -->
<p>&nbsp;</p>
<%
   id=request("id")
   name=request("name")
   sex=request("sex")
   memo=request("memo")

   ' 총 레코드 개수'
   sql="Select count(*) as totalcount from usert "
   Set rs=db.Execute(sql)

   ' 목록 불러오기
   sql2 =" select * from usert order by id asc "
   Set DbRec=db.Execute(sql2)
%>
<div class="rows">
  <div class="col-xs-12 col-sm-12">
      <p><h2 class="text-center">목록 보기</h2></p>

      <div class="col-xs-3 col-sm-3"></div>
      <div class="col-xs-6 col-sm-6">

          <p>레코드 수  : <%= rs("totalcount") %></p>
          <p>
            <a href="inputform.asp">추가</a>
          </p>

          <table class="table">
            <tr>
              <td>번호</td>
              <td>이름</td>
              <td>성별</td>
              <td>메모</td>
            </tr>

            <%
              For i =1 to rs("totalcount")
            %>
            <tr>
              <td><%= DbRec("id") %></td>
              <td><%= DbRec("name") %></td>
              <td><%= DbRec("sex") %></td>
              <td><%= DbRec("memo") %></td>
            </tr>
            <%

              DbRec.movenext
              Next
            %>

          </table>

      </div>

  </div>
</div>

</body>
</html>

 

 

 

 

 

 

 

 

 

 

 

about author

PHRASE

Level 60  라이트

인민, 오직 인민만이 세계역사를 만드는 원동력이다. -모택동

댓글 ( 0)

댓글 남기기

작성