• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 353
  • Last Modified:

Use IP address as validation in webservice with stored procedure

Hi,

I have a stored procedure where I ask for 2 input parameters for the query to execute. It is going to used in connection with a web service, and I would like to improve the security.
What I want:

When the web service is called I would like to request the 2 input parameters and also retrieve the ip address. The IP address should then match the column IP Address in my Supplier_table.

Maybe use : ConnectionProperty('client_net_address')

What do I need to change in my query to get this to work?


My tables:

Customer_table:
- CustomerNo varchar(50)
- ProductionDate datetime
- DiscontinuedDate datetime
- StopProductionDate datetime

tmtB_table:
Park_GSRN varchar(50)
SupplierID int
StartDate datetime
StopDate datetime

Supplier_table:
- ID int
- supplierGUID uniqueidentifier
- IP_Address varchar(50)


Here is my SP so far:

 
GO
/****** Object:  StoredProcedure [dbo].[CustomerWebServiceGetByDate6]    Script Date: 05/07/2011 22:09:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[CustomerWebServiceGetByDate6]
	@InputGUID as uniqueidentifier,
	@InputDate as datetime = '1753-01-01 00:00:000'
as
                                            
if(@InputDate is null) set @InputDate = GETDATE()	--this will handle the if statement
set @InputDate = convert(datetime, floor(convert(float, @InputDate)))	-- and this will truncate the time portion of the datetime


declare @maxStop datetime
select @maxStop = MAX(StopDate) from tmtB WHERE StopDate <= @InputDate



CREATE TABLE #tmtBalance
(Park_GSRN varchar(50))


;with CTE 
as
(
	
	SELECT
	Park_GSRN,
	ProductionDate,
	DiscontinuedDate,
	StopProductionDate,
	StartDate,
	StopDate,
	SupplierID,
	ROW_NUMBER() over (partition by Park_GSRN order by StopDate desc) RN 
	FROM (Customer LEFT JOIN tmtB ON Customer.CustomerNo = tmtB.Park_GSRN) LEFT JOIN Vendor ON tmtB.SupplierID=supplier.id
	where ((ProductionDate <= @InputDate and ProductionDate IS NOT NULL)
						and 
                        (StopProductionDate IS NULL or StopProductionDate >= @InputDate)
                        and
                        (DiscontinuedDate >= @InputDate or DiscontinuedDate IS NULL)
                        and
	Startdate <= @InputDate and supplierGUID = @InputGUID
	and (StopDate >= @InputDate or StopDate is null or 
	@InputDate >= @maxStop --entire subquery replaced here
))
)
INSERT INTO #tmtBalance
select Park_GSRN from CTE A
where A.RN = 1
order by A.Park_GSRN

DROP TABLE #tmtBalance

Open in new window

0
Madsing
Asked:
Madsing
2 Solutions
 
dexion432Commented:
ALTER procedure [dbo].[CustomerWebServiceGetByDate6]
      @InputGUID as uniqueidentifier,
      @InputDate as datetime = '1753-01-01 00:00:000'
as
   

to

ALTER procedure [dbo].[CustomerWebServiceGetByDate6]
      @InputGUID as uniqueidentifier,
      @InputDate as datetime = '1753-01-01 00:00:000'
        @IP as varchar(15) = '0.0.0.0'
as
   


and

use @IP in the where expression to filter your supplier table with the ip
0
 
MlandaTCommented:
GO
/****** Object:  StoredProcedure [dbo].[CustomerWebServiceGetByDate6]    Script Date: 05/07/2011 22:09:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[CustomerWebServiceGetByDate6]
      @InputGUID as uniqueidentifier,
      @InputDate as datetime = '1753-01-01 00:00:000',
        @IPAddress as varchar(15)
as
                                           
if(@InputDate is null) set @InputDate = GETDATE()      --this will handle the if statement
set @InputDate = convert(datetime, floor(convert(float, @InputDate)))      -- and this will truncate the time portion of the datetime


declare @maxStop datetime
select @maxStop = MAX(StopDate) from tmtB
inner join Supplier on tmtB.SupplierID = Supplier.ID
WHERE StopDate <= @InputDate and Supplier.IP_Address = @IPAddress



CREATE TABLE #tmtBalance
(Park_GSRN varchar(50))


;with CTE
as
(
      
      SELECT
      Park_GSRN,
      ProductionDate,
      DiscontinuedDate,
      StopProductionDate,
      StartDate,
      StopDate,
      SupplierID,
      ROW_NUMBER() over (partition by Park_GSRN order by StopDate desc) RN
      FROM (Customer LEFT JOIN tmtB ON Customer.CustomerNo = tmtB.Park_GSRN) LEFT JOIN Vendor ON tmtB.SupplierID=supplier.id
      where ((ProductionDate <= @InputDate and ProductionDate IS NOT NULL)
                                    and
                        (StopProductionDate IS NULL or StopProductionDate >= @InputDate)
                        and
                        (DiscontinuedDate >= @InputDate or DiscontinuedDate IS NULL)
                        and
      Startdate <= @InputDate and supplierGUID = @InputGUID
      and (StopDate >= @InputDate or StopDate is null or
      @InputDate >= @maxStop --entire subquery replaced here
        and Supplier.IP_Address = @IPAddress
))
)
INSERT INTO #tmtBalance
select Park_GSRN from CTE A
where A.RN = 1
order by A.Park_GSRN

DROP TABLE #tmtBalance
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now