dcsimg

Access a Database on Remote SQL

June 14, 1999

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 "&nbsp;" 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 & "&nbsp;</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">&nbsp;
    <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>&nbsp;&nbsp; <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>









The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers