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 |
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.
METHOD=”POST”>
<%
response.write “”
response.write “”
response.write “”
response.write
“”
%>
| <%@ 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 %> | |||
|---|---|---|---|
size=”3″>Server Name | size=”3″>Database Name | ||
| ” & gServer & “ | ” %> ” response.write “Value=””” & gServer & “””>” response.write “Value=””” & gLogin & “””>” response.write “Value=””” & gPassword & “””>” oServer.close set Oserver = Nothing %> <% response.write “ | ” & gLogin & “ | ” if gPassword=”” then response.write “ ” else response.write gPassword response.write “ |
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.
<%
response.write
“”
response.write “”
response.write “”
response.write “”
%>
| <%@ 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=;” %>
| |||
|---|---|---|---|
| ” & gServer & “ | ” & sDatabase & “ | ” & gLogin & “ | ” & gPassword & “ |
width=”80%” border=”2″
cellspacing=”1″>
Select
A Table Name from the
List:
cellspacing=”1″>
size=”1″>
<%
Set oSQLdb = oServer.Databases(
sDatabase
)
‘
‘another way to get
tables
‘
For Each oSQLTable In oSQLdb.Tables
Response.Write “”
Next
%><%
response.write
“”
response.write “NAME=””ServerName”” Value=””” & gServer &
“””>”
response.write “NAME=””Login”” Value=””” & gLogin &
“””>”
response.write “NAME=””DatabaseName”” Value=””” & sDatabase &
“””>”
response.write “NAME=””Password”” Value=””” & gPassword &
“””>”
oServer.Close
%>
value=”View
Table”>
SQL
Statement
<% While Not rsTables.EOF %>
<%
rsTables.MoveNext
Wend %>
<% Do While Not rsViews.EOF %>
<%
rsViews.MoveNext
Wend %>