by Jessie Cui
Is that feasible to dynamically create DSN and
check data via internet from any computer? This small application,
named as SQLmap, is valuable for those who is
responsible for building or maintaining a web site and SQL Server
database. This apllication helps to access all servers and then
access all databases stored in each server. Then with the interface
provided you can view tables, records in each table through browser
from any computer, You can even write SQL query to retrieve data
from table that meet the criteria specified.
I have developed
many intranet and internet projects using ASP and SQL Server. After
projects are delivered, it is highly possible that customers suggest
further revisions for the deliverred project as they get more
experience with the project. Any revision should be tested by
accessing remote database. In most cases, we need to immediately
know table data on remote SQL Server for successful test. For
example, I finished shopping cart project with SQL 6.5 database and
ASP in Dallas then ftp the whole project and database to Whasington
D.C. Later, I was asked to modify some pages related to database. As
I need real data to test ASP pages, in this situation, we can ftp
SQLmap (list on table below) to Server in D.C. So
we can view table data on remote SQL Server through
internet.
Allthough there are a bunch of softwares that allow you
perform the same tasks, such as PC Anywhere, and Remote Possible,
etc., you are limited on the machines with those softwares
installed. Therefore, SQLmap extends your
accessibity to all server databases through internet.
I use following files in SQLmap application.
| File Name: |
File Type: |
Description: |
| 1) default.htm |
html file (1k) |
login page to server machine |
| 2) sqlDBlist.asp |
asp file (2k) |
database list on SQL server |
| 3) List.asp |
asp file (5k) |
table, view list and SQL query form |
| 4) showTable.asp |
asp file (3k) |
table structure and table data |
| 5) querySQL.asp |
asp file (1k) |
query result |
| 6) SQLmap.zip |
zip file (5k) |
first five files in this zip file |
| |
Demo
Download
SQLmap.zip
sqlDBlist.asp users may connect to
several servers on their network, depending on the information they
need. Here, we create refrence to the SQL Server OLE Object. SQL
Server 6.5 client utilities provide a number of OLE Object that can
be used to access SQL Server service and list all database on the
server user submitted.
<%@ LANGUAGE =
VBScript %>
<%
'
' Refernce SQL Server OLE
Server
'( SQL Server must be registered on your system for
this to work)
'
Set OServer =
Server.CreateObject("sqlole.sqlserver")
gServer =
request.form("ServerName")
gLogin =
request.form("Login")
gPassword =
request.form("Password")
OServer.Connect
gServer,gLogin,gPassword
%>
<HTML>
<HEAD><TITLE>Database Map</TITLE>
</HEAD>
<BODY BGCOLOR="#FFFFFF"><FONT
FACE="ARIAL,HELVETICA">
<p>
<table
border="1" width="85%">
<tr>
<td><font
color="#110080"
size="3"><em><strong>Server
Name
</strong></em></font></td>
<td><font
color="#110080"
size="3"><em><strong>Database
Name
</strong></em></font></td>
<td><font
color="#110080" size="3"><em><strong>Login ID
</strong></em></font></td>
<td><font
color="#110080" size="3"><em><strong>Password
</strong></em></font></td>
</tr>
<FORM ACTION="list.asp"
METHOD="POST">
<%
response.write "<tr><td>" & gServer &
"</td>"
response.write "<td >"
%>
<SELECT NAME="databaseName"
>
<%
For Each SQLDB In
OServer.Databases
If Not SQLDB.SystemObject
Then
Response.Write "<OPTION VALUE=""" & SQLDB.Name &
""">" &
SQLDB.Name
End
If
Next
response.write
"</SELECT>"
response.write "<INPUT TYPE=""hidden"" NAME=""ServerName""
Value=""" & gServer &
""">"
response.write "<INPUT TYPE=""hidden"" NAME=""Login""
Value=""" & gLogin &
""">"
response.write "<INPUT TYPE=""hidden"" NAME=""Password""
Value=""" & gPassword &
""">"
oServer.close
set Oserver =
Nothing
%>
<INPUT TYPE="submit" VALUE="Sumbit">
<%
response.write "</td>"
response.write "<td>"
& gLogin & "</td>"
response.write
"<td>"
if
gPassword="" then response.write " " else
response.write
gPassword
response.write
"</td></tr>"
%>
</FORM>
</table>
</BODY>
</HTML>
|
list.asp is respond to submitting the
previous page. We create the connection definition 'on the
fly', using the server name and databse name submitted by the user.
So we do not need to set up ODBC Data Source Name
(DSN) for each database. Also, user is allowed to
input SQL query (read only) in this page and get result from remote
SQL server database.
<%@
LANGUAGE="VBSCRIPT" %>
<%
'
' Refernce SQL
Server OLE Server
'
'
Dim OServer
Dim
gServer
Dim gLogin
Dim sDatabase
Dim gPassword
Set
OServer = Server.CreateObject("sqlole.sqlserver")
gServer =
trim(request.form("ServerName"))
sDatabase =
trim(request.form("DatabaseName"))
gLogin =
trim(request.form("Login"))
gPassword =
trim(request.form("Password"))
'
' Connect to the
server
'
OServer.Connect
gServer,gLogin,gPassword
'
'to get all tables and views
from request.form("DatabaseName")
'
Dim rsTables, sql,
rsViews
Set rsTables =
Server.CreateObject("ADODB.recordset")
Set rsViews =
Server.CreateObject("ADODB.recordset")
table_sql = "SELECT
Name FROM Sysobjects WHERE TYPE='U' ORDER BY 1"
view_sql =
"SELECT Name FROM Sysobjects WHERE TYPE='V' ORDER BY
1"
rsTables.Open table_sql, _
"Driver={SQL
Server};Server=" & gServer & ";uid=" & gLogin
& _
";pwd=" & gPassword &
";Database=" & sDatabase & ";DSN=;"
rsViews.Open
view_sql, _
"Driver={SQL Server};Server=" & gServer
& ";uid=" & gLogin & _
";pwd=" & gPassword & ";Database=" & sDatabase
& ";DSN=;"
%>
<html>
<head>
<title>Tables
in the Database
</title>
</head>
<body
bgcolor="#FFFFFF">
<p>
<table
border="1" width="80%" bordercolor="#008000"
cellspacing="0">
<tr>
<td
align="center"><font color="#800080"
size="3"><em><strong>Server Name
</strong></em></font></td>
<td
align="center"><font color="#800080"
size="3"><em><strong>Database Name
</strong></em></font></td>
<td
align="center"><font color="#800080"
size="3"><em><strong>Login ID
</strong></em></font></td>
<td
align="center"><font color="#800080"
size="3"><em><strong>Password
</strong></em></font></td>
</tr>
<%
response.write
"<td>" & gServer &
"</td>"
response.write "<td>" & sDatabase
& "</td>"
response.write "<td>" &
gLogin & "</td>"
response.write "<td>"
& gPassword &
" </td>"
%>
</table>
<table bgcolor="#B9FAFD"
width="80%" border="2"
cellspacing="1">
<tr>
<td
bgcolor="#008000"><p align="center"><font
face="Arial" color="#FFFFFF"><strong>Please
Select
A Table Name from the
List:</strong></font></td>
</tr>
<tr>
<td
align="center">
<form
action="showTable.asp" method="POST">
<table
border="1" bordercolor="#008000"
cellspacing="1">
<tr>
<td
align="center"><font color="#000080"
size="4"><em><strong>Table Name
</strong></em></font><select
name="TableNames"
size="1">
<%
Set oSQLdb = oServer.Databases(
sDatabase
)
'
'another way to get
tables
'
For Each oSQLTable In oSQLdb.Tables
Response.Write "<option value=""" &
_
oSQLTable.Name & """ > " &
_
oSQLTable.Name &
"</option>"
Next
%><%
response.write
"</SELECT>"
response.write "<INPUT TYPE=""hidden""
NAME=""ServerName"" Value=""" & gServer &
""">"
response.write "<INPUT TYPE=""hidden""
NAME=""Login"" Value=""" & gLogin &
""">"
response.write "<INPUT TYPE=""hidden""
NAME=""DatabaseName"" Value=""" & sDatabase &
""">"
response.write "<INPUT TYPE=""hidden""
NAME=""Password"" Value=""" & gPassword &
""">"
oServer.Close
%>
</select> <input type="submit"
value="View
Table"></td>
</tr>
</table>
</form>
</td>
</tr>
<tr>
<td
align="center" bgcolor="#008000"><font face="Arial"
color="#FFFFFF"><strong>Execute a
SQL
Statement</strong></font></td>
</tr>
<tr>
<td><div
align="center"><center><table>
<tr>
<td
ALIGN="CENTER"><b>Tables</b><br>
<select
SIZE="8">
<% While Not rsTables.EOF %>
<option><%=rsTables(0)%></option>
<%
rsTables.MoveNext
Wend %>
</select> </td>
<td
ALIGN="CENTER"><b>Views</b><br>
<select
SIZE="8" >
<% Do While Not rsViews.EOF %>
<option><%=rsViews(0)%></option>
<%
rsViews.MoveNext
Wend %>
</select>
</td>
</tr>
</table>
</center></div>
<form METHOD="POST" ACTION="querySQL.asp" CELLSPACING="4"
align="center">
<table
align="center">
<tr>
<td>SQL
Query:<br>
<textarea TYPE="TEXT" NAME="MySQL"
COLS="50" ROWS="8"></textarea>
</td>
</tr>
<tr>
<td
align="center"
><input TYPE="submit" value="View Query">
</td>
</tr>
</table>
</form>
</td>
</tr>
</table>
</body>
</html>
|
showTable.asp show table structure and
data using the number of Field objects in thedata
source's Fields collection .
<%@
LANGUAGE="VBSCRIPT" %>
<%
Dim DBConn
Dim
RS
Dim gServer
Dim gLogin
Dim sDatabase
Dim
gPassword
Dim iCount
Dim iRow
Set RS =
Server.CreateObject("ADODB.recordset")
gServer =
request.form("ServerName")
sDatabase =
request.form("DatabaseName")
gLogin =
request.form("Login")
gPassword = request.form("Password")
sTable = request.form("TableNames")
RS.Open "select *
from " & sDatabase & ".dbo." &
request.form("TableNames"), _
"Driver={SQL Server};Server="
& gServer & ";uid=" & gLogin &
_
";pwd=" & gPassword &
";Database=" & sDatabase & ";DSN=;"
%>
<html>
<head>
<title>Tables
in the Database
Selected</title>
</head>
<body
background="Granite.gif"
bgcolor="#FFFFFF">
<h1><font
color="#000080"><em>Here is your
Table</em></font></h1>
<p><font
color="#000080" size="3"><em><strong>Table
Name:
<% response.write request.form("TableNames") &
" "%>
</strong></em></font></p>
<%on
error resume next%>
<table
border="1">
<tr>
<td><font color="#800080"
size="4"><em><strong>Field
Name
</strong></em></font></td>
<td><font color="#800080"
size="4"><em><strong>Type
</strong></em></font></td>
<td><font color="#800080"
size="4"><em><strong>Length
</strong></em></font></td>
<td><font color="#800080"
size="4"><em><strong>Precision
</strong></em></font></td>
<td><font color="#800080"
size="4"><em><strong>Scale
</strong></em></font></td>
</tr>
<%
iRow =
RS.Fields.Count
For iCount = 0 to (iRow -
1)
Set Fld =
RS.Fields(iCount)
response.write "<tr>"
response.write "<td> " & Fld.Name &
"</td>"
response.write "<td> " & Fld.Type &
"</td>"
response.write "<td> " & Fld.ActualSize &
"</td>"
If Int(Fld.Precision) >= 255
Then
response.write "<td> 0 </td>"
Else
response.write "<td> " & Int(Fld.Precision) &
"</td>"
End
If
If Int(Fld.NumericScale) >= 255
Then
response.write "<td> 0 </td>"
Else
response.write "<td> " & Int(Fld.NumericScale) &
"</td>"
End
If
response.write
"</tr>"
Next
%>
</table>
<%
'''''''''''create table %>
<p>
<hr>
<%
m=0
RS.MoveFirst
%>
<table BORDER="1" COLS="<% =
RS.Fields.Count%>">
<tr>
<% For Each oField In RS.Fields
%>
<th> <% = oField.Name %>
</th>
<% Next %>
</tr>
<% Do While Not RS.EOF
%>
<TR
<% if m mod 2 = 0 then %> bgcolor="yellow" <%end
if%>>
<%for i = 0 to rs.Fields.Count-1
%>
<TD
>
<%=rs(i)%>
</TD>
<% next
%>
</TR>
<%
m=m+1
RS.MoveNext
%>
</tr>
<% Loop %>
</table>
<%
RS.Close
Set RS = Nothing
%>
</body>
</html>
|
querySQL.asp show result of SQL query
user submitted.
<%@
LANGUAGE="VBSCRIPT" %>
<%
'***********Establish a
connection to the database***************
gServer =
trim(request.form("ServerName"))
sDatabase =
trim(request.form("DatabaseName"))
gLogin =
trim(request.form("Login"))
gPassword =
trim(request.form("Password"))
Dim Conn
Set Conn =
Server.CreateObject("ADODB.Connection")
ConnStr="Driver={SQL Server};Server=" & gServer &
";uid=" & gLogin & ";pwd=" & gPassword &
";Database=" & sDatabase & ";DSN=;"
'
response.write connstr
Conn.Open
ConnStr
sql = Request.Form("querySQL")
Set rs =
Server.CreateObject("ADODB.Recordset")
rs.Open sql,
Conn
%>
<HTML>
<BODY>
<CENTER>
<H1>SQL
Query Results</H1>
Your Query:
<I><%=sql%></I><BR>
</CENTER>
<P>
<TABLE
ALIGN="CENTER" BORDER="1" CELLSPACING="1"
CELLPADDING="3">
<TR>
<%
Dim j
For j = 0
to rs.Fields.Count-1 %>
<!--Print out the column
names-->
<TH
BGCOLOR="#CCCCCC">
<%=rs.Fields.Item(j).Name%>
</TH>
<% next %>
</TR>
<%
Dim i
Do While Not rs.EOF
%>
<TR>
<%
for i = 0
to rs.Fields.Count-1
%>
<TD
BGCOLOR="#EEEEEE">
<%=rs(i)%>
</TD>
<% next %>
</TR>
<%
rs.MoveNext
Loop %>
</TABLE>
</BODY>
</HTML> |
» 
Comment and Contribute
