Link to home
Start Free TrialLog in
Avatar of garethtnash
garethtnashFlag for United Kingdom of Great Britain and Northern Ireland

asked on

MS SQL / ASP Stored Procedure IP Address Redirect

Hello,

I'm trying to write a procedure that --

Checks whether the resource requested is restricted to certain IP addresses -

IF So checks whether the remote address is listed in the IP table

returns either a 0 or a 1

if 0 is returned, the asp page returns a status code of 404 - and the visitor sees the IIS standard 404 page not exist page..

Not too difficult you would think?

But I'm stuck..

Here is my SQL SP

CREATE PROCEDURE [dbo].[DashboardIPCheck]
@ip nvarchar(15)
AS
Declare @access int = 0 
BEGIN
SET NOCOUNT ON
	IF Exists(Select ipaddress From dbo.DashIP where ipaddress = 'Y')
	BEGIN
		IF Exists(Select ipaddress from dbo.DashIPRange where ipaddress = @ip and live = 'Y')
		BEGIN
		Set @access = 1
		END
	END
	ELSE
	BEGIN
	Set @access = 1
	END
	Select @access as access
END

GO

Open in new window



And here is my ASP

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!--#include virtual="/Connections/Recruta2.asp" -->
<%

Dim CMDIpCheck__ip
CMDIpCheck__ip = NULL
if(Request.ServerVariables("REMOTE_ADDR") <> "") then CMDIpCheck__ip = Request.ServerVariables("REMOTE_ADDR")

set CMDIpCheck = Server.CreateObject("ADODB.Command")
CMDIpCheck.ActiveConnection = MM_recruta2_STRING
CMDIpCheck.CommandText = "dbo.DashboardIPCheck"
CMDIpCheck.CommandType = 4
CMDIpCheck.CommandTimeout = 0
CMDIpCheck.Prepared = true
CMDIpCheck.Parameters.Append CMDIpCheck.CreateParameter("@RETURN_VALUE", 3, 4)
CMDIpCheck.Parameters.Append CMDIpCheck.CreateParameter("@ip", 200, 1,15,CMDIpCheck__ip)
set ip = CMDIpCheck.Execute
ip_numRows = 0

IF ip("access") = 0 then
Response.Status = "404" 
End if

ip.Close()
Set ip = Nothing
Set CMDIpCheck = Nothing
%>
<%
If Request("username") <> "" AND Request("password") <> "" then
Dim DashLogin__username
DashLogin__username = NULL
if(Request("username") <> "") then DashLogin__username = Request("username")

Dim DashLogin__password
DashLogin__password = NULL
if(Request("password") <> "") then DashLogin__password = Request("password")

set DashLogin = Server.CreateObject("ADODB.Command")
DashLogin.ActiveConnection = MM_Recruta2_STRING
DashLogin.CommandText = "dbo.DashboardLoginDan"
DashLogin.CommandType = 4
DashLogin.CommandTimeout = 0
DashLogin.Prepared = true
DashLogin.Parameters.Append DashLogin.CreateParameter("@RETURN_VALUE", 3, 4)
DashLogin.Parameters.Append DashLogin.CreateParameter("@username", 200, 1,100,DashLogin__username)
DashLogin.Parameters.Append DashLogin.CreateParameter("@password", 200, 1,100,DashLogin__password)
set Login = DashLogin.Execute
Login_numRows = 0

If Not Login.EOF Then

Session("UN") = Login("username")
Session("PD") = Login("password")
Response.Redirect("/admin/clientlist.asp")

Else

Dim EMessage
Emessage = "Y"

End if

Login.Close()
Set Login = Nothing
End if
%>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Login</title>
<link href="css/gstyle.css" rel="stylesheet" type="text/css" media="all" />
</head>

<body>
<div class="login">
<form id="form1" name="form1" method="post" action="">
  <div class="inputcontainer">
    <ul>
      <li>
        <label>Email :</label>
        <input name="username" type="text" id="username" maxlength="50" value="" />
      </li>
      <li>
        <label>Password :</label>
        <input name="password" type="password" id="password" maxlength="50" />
      </li>
    </ul>
  </div>
  <div class="buttoncontainer">
    <input type="image" id="searchbut" src="img/login.png" alt="login" width="72" height="45"/>
  </div>
  <div class="remembermediv">
    <label>
      <input name="remember" type="checkbox" id="remember" value="Y" />
    </label>
    <p>Remember me</p>
  </div>
  <% IF Request("username") <> "" AND Emessage = "Y" Then %><div class="faillogin">
 Your login failed, please try again or contact the sys admin
  </div><%End if%>
</form>

<div style="clear:both"></div>
</div>
</body>
</html>

Open in new window


Please explain what I've missed..

Thank you
Avatar of pateljitu
pateljitu
Flag of Canada image

Please change your SP as below, basically if there is no record found you have to set @access = 0:


CREATE PROCEDURE [dbo].[DashboardIPCheck]
@ip nvarchar(15)
AS
Declare @access int = 0 
BEGIN
SET NOCOUNT ON
	IF Exists(Select ipaddress From dbo.DashIP where ipaddress = 'Y')
	BEGIN
		IF Exists(Select ipaddress from dbo.DashIPRange where ipaddress = @ip and live = 'Y')
		BEGIN
		     Set @access = 1
		END
                 ELSE  
                     BEGIN
                        Set @access = 0
                     END 
	END
	ELSE
	BEGIN
	Set @access = 0
	END
	Select @access as access
END

GO

Open in new window

Avatar of garethtnash

ASKER

Thanks Pateljitu, I didn't notice that..

But I'm still not seeing the IIS standard 404 page, do I need to do more than just --


IF ip("access") = 0 then
Response.Status = "404" 
End if

Open in new window


Thanks
Please use code as provided, have changed both SP and ASP page:

SP:
CREATE PROCEDURE [dbo].[DashboardIPCheck]
@ip nvarchar(15),
@access int output    
AS
-- Declare @access int = 0 
BEGIN
SET NOCOUNT ON
	IF Exists(Select ipaddress From dbo.DashIP where ipaddress = 'Y')
	BEGIN
		IF Exists(Select ipaddress from dbo.DashIPRange where ipaddress = @ip and live = 'Y')
		BEGIN
			Set @access = 1
		END
        ELSE  
        BEGIN
			Set @access = 0
        END 
	END
	ELSE
	BEGIN
	Set @access = 0
	END
	
END

GO

Open in new window



ASP:
<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!--#include virtual="/Connections/Recruta2.asp" -->
<%

Dim CMDIpCheck__ip
CMDIpCheck__ip = NULL
if(Request.ServerVariables("REMOTE_ADDR") <> "") then CMDIpCheck__ip = Request.ServerVariables("REMOTE_ADDR")

set CMDIpCheck = Server.CreateObject("ADODB.Command")
CMDIpCheck.ActiveConnection = MM_recruta2_STRING
CMDIpCheck.CommandText = "dbo.DashboardIPCheck"
CMDIpCheck.CommandType = 4
CMDIpCheck.CommandTimeout = 0
CMDIpCheck.Prepared = true
CMDIpCheck.Parameters.Append CMDIpCheck.CreateParameter("@RETURN_VALUE", 3, 2, 4, access)
CMDIpCheck.Parameters.Append CMDIpCheck.CreateParameter("@ip", 200, 1,15,CMDIpCheck__ip)
CMDIpCheck.Execute()

dim intAccessCode
intAccessCode = objStorageKeyCmd.Parameters("@access").Value

IF cint(intAccessCode) = 0 then
Response.Status = "404" 
End if

ip.Close()
Set ip = Nothing
Set CMDIpCheck = Nothing
%>
<%
If Request("username") <> "" AND Request("password") <> "" then
Dim DashLogin__username
DashLogin__username = NULL
if(Request("username") <> "") then DashLogin__username = Request("username")

Dim DashLogin__password
DashLogin__password = NULL
if(Request("password") <> "") then DashLogin__password = Request("password")

set DashLogin = Server.CreateObject("ADODB.Command")
DashLogin.ActiveConnection = MM_Recruta2_STRING
DashLogin.CommandText = "dbo.DashboardLoginDan"
DashLogin.CommandType = 4
DashLogin.CommandTimeout = 0
DashLogin.Prepared = true
DashLogin.Parameters.Append DashLogin.CreateParameter("@RETURN_VALUE", 3, 4)
DashLogin.Parameters.Append DashLogin.CreateParameter("@username", 200, 1,100,DashLogin__username)
DashLogin.Parameters.Append DashLogin.CreateParameter("@password", 200, 1,100,DashLogin__password)
set Login = DashLogin.Execute
Login_numRows = 0

If Not Login.EOF Then

Session("UN") = Login("username")
Session("PD") = Login("password")
Response.Redirect("/admin/clientlist.asp")

Else

Dim EMessage
Emessage = "Y"

End if

Login.Close()
Set Login = Nothing
End if
%>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Login</title>
<link href="css/gstyle.css" rel="stylesheet" type="text/css" media="all" />
</head>

<body>
<div class="login">
<form id="form1" name="form1" method="post" action="">
  <div class="inputcontainer">
    <ul>
      <li>
        <label>Email :</label>
        <input name="username" type="text" id="username" maxlength="50" value="" />
      </li>
      <li>
        <label>Password :</label>
        <input name="password" type="password" id="password" maxlength="50" />
      </li>
    </ul>
  </div>
  <div class="buttoncontainer">
    <input type="image" id="searchbut" src="img/login.png" alt="login" width="72" height="45"/>
  </div>
  <div class="remembermediv">
    <label>
      <input name="remember" type="checkbox" id="remember" value="Y" />
    </label>
    <p>Remember me</p>
  </div>
  <% IF Request("username") <> "" AND Emessage = "Y" Then %><div class="faillogin">
 Your login failed, please try again or contact the sys admin
  </div><%End if%>
</form>

<div style="clear:both"></div>
</div>
</body>
</html>
                                  

Open in new window

umm--


Microsoft OLE DB Provider for SQL Server error '80040e14'
The formal parameter "@ip" was not declared as an OUTPUT parameter, but the actual parameter passed in requested output.
/Default.asp, line 17
oops, can you change the order in code:

Line 15 - 17:
CMDIpCheck.Parameters.Append CMDIpCheck.CreateParameter("@ip", 200, 1,15,CMDIpCheck__ip)
CMDIpCheck.Parameters.Append CMDIpCheck.CreateParameter("@RETURN_VALUE", 3, 2, 4, access)
CMDIpCheck.Execute()
Thanks Pateljitu,

I've checked that the correct values are being returned by the SQL SP, they are

I included a Response.Redirect under the response.status and when i accessed from an ip address not listed, i get redirected to the URL in the Response.Redirect...

I was hoping that using Response. Status, would mean that no Redirect was needed?


Thank you -

GTN
ASKER CERTIFIED SOLUTION
Avatar of pateljitu
pateljitu
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you