https://developer.paypal.com/webapps/developer/docs/classic/ipn/integration-guide/IPNIntro/
For this implementation I'm using a MVC Web API Controller (VB.Net) for the listener, along with some helper classes/extensions/methods. I'm also using a SQL Server 2012 table and stored procedure to create a notifications log, which allows you to:
1) Listen
2) Log
3) React
This is important because it allows the listener to do it's job quickly, and lets you focus on the processing later.
Let's start with our SQL Server database Notifications table.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Notifications](
[NotificationID] [INT] IDENTITY(1,1) NOT NULL,
[Timestamp] [DATETIME] NOT NULL CONSTRAINT [DF_Notifications_Timestamp] DEFAULT (GETDATE()),
[Type] [VARCHAR](50) NOT NULL CONSTRAINT [DF_Notifications_Type] DEFAULT (''),
[IPAddress] [VARCHAR](25) NOT NULL CONSTRAINT [DF_Notifications_IPAddress] DEFAULT (''),
[UrlRequest] [VARCHAR](MAX) NOT NULL CONSTRAINT [DF_Notifications_UrlRequest] DEFAULT (''),
[UserAgent] [VARCHAR](MAX) NOT NULL CONSTRAINT [DF_Notifications_UserAgent] DEFAULT (''),
[Data] [VARCHAR](MAX) NOT NULL CONSTRAINT [DF_Notifications_Message] DEFAULT (''),
[Status] [VARCHAR](MAX) NOT NULL CONSTRAINT [DF_Notifications_Status] DEFAULT (''),
[Method] [VARCHAR](10) NOT NULL CONSTRAINT [DF_Notifications_Method] DEFAULT (''),
[Processed] [BIT] NOT NULL CONSTRAINT [DF_Notifications_Processed] DEFAULT ((0)),
[ProcessedDate] [DATETIME] NULL,
[Notes] [VARCHAR](MAX) NOT NULL DEFAULT (''),
[TransactionID] [VARCHAR](50) NOT NULL DEFAULT (''),
[TransactionType] [VARCHAR](50) NOT NULL DEFAULT (''),
[ItemName] [VARCHAR](128) NOT NULL DEFAULT (''),
[ItemNumber] [VARCHAR](128) NOT NULL DEFAULT (''),
[Option] [VARCHAR](200) NOT NULL DEFAULT (''),
[Email] [VARCHAR](100) NOT NULL DEFAULT (''),
[PaymentStatus] [VARCHAR](25) NOT NULL DEFAULT (''),
CONSTRAINT [PK_Notifications] PRIMARY KEY CLUSTERED
(
[NotificationID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
GO
SET ANSI_PADDING OFF
GO
Next is the Notifications_Save stored procedure to save data in the Notifications table.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Notifications_Save]
(
@NotificationID INT,
@Type VARCHAR(50),
@IPAddress VARCHAR(25),
@UrlRequest VARCHAR(MAX),
@UserAgent VARCHAR(MAX),
@Data VARCHAR(MAX),
@Status VARCHAR(MAX),
@Method VARCHAR(10),
@Processed BIT,
@ProcessedDate DATETIME
)
AS
BEGIN
-- check to see if record exists
IF EXISTS (SELECT NotificationID FROM dbo.Notifications WHERE NotificationID = @NotificationID)
BEGIN
-- update
UPDATE dbo.Notifications
SET Type = @Type,
IPAddress = @IPAddress,
UrlRequest = @UrlRequest,
UserAgent = @UserAgent,
Data = @Data,
Status = @Status,
Method = @Method,
Processed = @Processed,
ProcessedDate = @ProcessedDate
WHERE NotificationID = @NotificationID
END
ELSE
BEGIN
-- insert
INSERT INTO dbo.Notifications
(Type, IPAddress, UrlRequest, UserAgent, Data, Status, Method, Processed, ProcessedDate)
VALUES
(@Type, @IPAddress, @UrlRequest, @UserAgent, @Data, @Status, @Method, @Processed, @ProcessedDate)
-- get identity value
SET @NotificationID = SCOPE_IDENTITY()
END
-- return value
RETURN @NotificationID
END
Next we'll create the Notification model object in our MVC project.
Public Class Notification Public Property NotificationID As Integer = 0 Public Property Timestamp As DateTime = DateTime.MinValue Public Property Type As String = String.Empty Public Property IPAddress As String = String.Empty Public Property UrlRequest As String = String.Empty Public Property UserAgent As String = String.Empty Public Property Data As String = String.Empty Public Property Status As String = String.Empty Public Property Method As String = String.Empty Public Property Processed As Boolean = False Public Property ProcessedDate As DateTime? End Class
Then we'll create the Notifications data layer class for calling the stored procedure.
Imports Microsoft.Practices.EnterpriseLibrary.Data
Imports System.Data.Common
Public Class Notifications
Public Function Save(ByVal Notification As Model.Notification) As Integer
'variables
Dim DB As Database = New DatabaseProviderFactory().CreateDefault()
Dim ReturnValue As Integer = -1
'command
Using cmd As DbCommand = DB.GetStoredProcCommand("dbo.Notifications_Save")
'parameters
With Notification
DB.AddInParameter(cmd, "@NotificationID", DbType.Int32, .NotificationID)
DB.AddInParameter(cmd, "@Type", DbType.String, .Type)
DB.AddInParameter(cmd, "@IPAddress", DbType.String, .IPAddress)
DB.AddInParameter(cmd, "@UrlRequest", DbType.String, .UrlRequest)
DB.AddInParameter(cmd, "@UserAgent", DbType.String, .UserAgent)
DB.AddInParameter(cmd, "@Data", DbType.String, .Data)
DB.AddInParameter(cmd, "@Status", DbType.String, .Status)
DB.AddInParameter(cmd, "@Method", DbType.String, .Method)
DB.AddInParameter(cmd, "@Processed", DbType.Boolean, .Processed)
DB.AddInParameter(cmd, "@ProcessedDate", DbType.DateTime, IIf(.ProcessedDate.HasValue AndAlso Not .ProcessedDate.Equals(DateTime.MinValue), .ProcessedDate, DBNull.Value))
End With
'return value parameter
DB.AddParameter(cmd, "@ReturnValue", DbType.Int32, 4, ParameterDirection.ReturnValue, False, 0, 0, "@ReturnValue", DataRowVersion.Default, Nothing)
'execute query
DB.ExecuteNonQuery(cmd)
'get return value
ReturnValue = DB.GetParameterValue(cmd, "@ReturnValue")
End Using
'return value
Return ReturnValue
End Function
End Class
Next we have a few helper classes/extensions/methods that we are going to be calling from the listener controller.
1) IsBlank (extension method)
2) ContainsValue (extension method)
3) Result (model object)
4) PostContentToUrl (shared method)
The IsBlank extension method seems trivial, especially for such a small example, but this is just one of the many extension methods that I keep in my utilities library to keep my code, clean, simple, and consistent.
<Extension()> _
Public Function IsBlank(Value As String) As Boolean
Dim ReturnValue As Boolean = True
If Value IsNot Nothing Then
ReturnValue = Value.Trim().Length = 0
End If
Return ReturnValue
End Function
The ContainsValue extension method is another one I keep in my library to to simplify the code so you don't have to deal with case sensitivity all over the place.
<Extension()> _
Public Function ContainsValue(Value As String, CompareValue As String) As Boolean
Dim ReturnValue As Boolean = False
If Value IsNot Nothing AndAlso CompareValue IsNot Nothing Then
ReturnValue = Value.Trim().IndexOf(CompareValue.Trim(), StringComparison.OrdinalIgnoreCase) >= 0
End If
Return ReturnValue
End Function
The Result model object is a simple helper class we're going to use when we post our data to the PayPal verification URL, which will allow us to return back a property indicating success or failure, as well as the response body.
Public Class Result Public Property Success As Boolean = False Public Property Message As String = String.Empty End Class
Then we have our PostContentToUrl method, which was written with re-usability in mind. It accepts a URL and a request body. This allows you to use it for POST'ing data generically to any service.
Public Shared Function PostContentToUrl(ByVal Url As String, ByVal Data As String) As Model.Result
'variables
Dim Result As New Model.Result
Try
'create web request
Dim MyRequest As WebRequest = WebRequest.Create(Url)
Dim ByteArray As Byte() = Encoding.UTF8.GetBytes(Data)
'properties
MyRequest.Method = "POST"
MyRequest.ContentType = "application/x-www-form-urlencoded"
MyRequest.ContentLength = ByteArray.Length
'get the request stream
Using DataStream As Stream = MyRequest.GetRequestStream()
'write the data to the request stream
DataStream.Write(ByteArray, 0, ByteArray.Length)
'close the stream object
DataStream.Close()
End Using
'response
Using MyResponse As HttpWebResponse = DirectCast(MyRequest.GetResponse(), HttpWebResponse)
Using MyReader As New StreamReader(MyResponse.GetResponseStream())
Result.Message = MyReader.ReadToEnd()
Result.Success = True
End Using
End Using
Catch ex As Exception
Result.Message = ex.Message
End Try
'return
Return Result
End Function
Next we'll setup a BaseController class to expose several properties for the current HTTP request.
Public Class BaseController
Inherits System.Web.Http.ApiController
Public ReadOnly Property QueryString() As String
Get
Try
Return DirectCast(Request.Properties("MS_HttpContext"), HttpContextBase).Request.QueryString.ToString
Catch ex As Exception
Return String.Empty
End Try
End Get
End Property
Public ReadOnly Property IPAddress() As String
Get
Try
Return DirectCast(Request.Properties("MS_HttpContext"), HttpContextBase).Request.UserHostAddress
Catch ex As Exception
Return String.Empty
End Try
End Get
End Property
Public ReadOnly Property UrlRequest() As String
Get
Try
Return DirectCast(Request.Properties("MS_HttpContext"), HttpContextBase).Request.Url.AbsoluteUri
Catch ex As Exception
Return String.Empty
End Try
End Get
End Property
Public ReadOnly Property RawUrl() As String
Get
Try
Return DirectCast(Request.Properties("MS_HttpContext"), HttpContextBase).Request.RawUrl
Catch ex As Exception
Return String.Empty
End Try
End Get
End Property
Public ReadOnly Property UrlReferrer() As String
Get
Try
Return DirectCast(Request.Properties("MS_HttpContext"), HttpContextBase).Request.UrlReferrer.AbsoluteUri
Catch ex As Exception
Return String.Empty
End Try
End Get
End Property
Public ReadOnly Property UserAgent() As String
Get
Try
Return DirectCast(Request.Properties("MS_HttpContext"), HttpContextBase).Request.UserAgent
Catch ex As Exception
Return String.Empty
End Try
End Get
End Property
Public ReadOnly Property Crawler() As Boolean
Get
Try
Return DirectCast(Request.Properties("MS_HttpContext"), HttpContextBase).Request.Browser.Crawler
Catch ex As Exception
Return False
End Try
End Get
End Property
Public ReadOnly Property Method() As String
Get
Try
Return DirectCast(Request.Properties("MS_HttpContext"), HttpContextBase).Request.HttpMethod
Catch ex As Exception
Return False
End Try
End Get
End Property
End Class
Now that we've got all the core pieces in place let's take a look at the ListenerController, which is the simplest part of the entire process.
Imports System.Net
Imports System.Web.Http
Imports System.IO
Namespace Controllers
<RoutePrefix("listener")>
Public Class ListenerController
Inherits BaseController
<HttpPost>
<Route("ipn")>
Public Function InstantPaymentNotification() As String
'variables
Dim Data As String = String.Empty
Dim Notification As New Model.Notification
'get data
Try
Data = New StreamReader(HttpContext.Current.Request.InputStream).ReadToEnd()
Catch ex As Exception
Data = String.Empty
End Try
'check for valid data
If Data.IsBlank Then
Return String.Empty
End If
'PayPal HTTP POSTs an IPN message to your listener that notifies it of an event.
'Your listener returns an empty HTTP 200 response to PayPal.
'Your listener HTTP POSTs the complete, unaltered message back to PayPal; the message must contain the same fields (in the same order) as the original message and be encoded in the same way as the original message.
'PayPal sends a single word back - either VERIFIED (if the message matches the original) or INVALID (if the message does not match the original).
'Every IPN message you receive from PayPal includes a User-Agent HTTP request header whose value is PayPal IPN ( https://www.paypal.com/ipn ).
'Do not use this header to verify that an IPN really came from PayPal and has not been tampered with.
'Rather, to verify these things, you must use the IPN authentication protocol outlined above.
If Not MyBase.UserAgent.ContainsValue("PayPal") Then
Return String.Empty
End If
'Before you can trust the contents of the message, you must first verify that the message came from PayPal.
'To verify the message, you must send back the contents in the exact order they were received and precede it with the command _notify-validate, as follows:
Dim VerifyUrl As String = IIf(Data.ContainsValue("test_ipn=1"), "https://www.sandbox.paypal.com/cgi-bin/webscr", "https://www.paypal.com/cgi-bin/webscr")
Dim VerifyData As String = String.Format("cmd=_notify-validate&{0}", Data)
Dim Result As Model.Result = Functions.PostContentToUrl(Url:=VerifyUrl, Data:=VerifyData)
'properties
Notification.Type = "IPN"
Notification.IPAddress = MyBase.IPAddress
Notification.UrlRequest = MyBase.UrlRequest
Notification.UserAgent = MyBase.UserAgent
Notification.Data = Data
Notification.Status = Result.Message
Notification.Method = MyBase.Method
'save
Using DB As New Data.Notifications
DB.Save(Notification)
End Using
'Important: After you have authenticated an IPN message (received a VERIFIED response from PayPal), you must perform these important checks before you can assume that the IPN is both legitimate and has not already been processed:
'Check that the payment_status is Completed.
'If the payment_status is Completed, check the txn_id against the previous PayPal transaction that you processed to ensure the IPN message is not a duplicate.
'Check that the receiver_email is an email address registered in your PayPal account.
'Check that the price (carried in mc_gross) and the currency (carried in mc_currency) are correct for the item (carried in item_name or item_number).
'Once you have completed these checks, IPN authentication is complete.
'Now, you can update your database with the information provided and initiate any back-end processing that's appropriate.
'return
Return String.Empty
End Function
End Class
End Namespace
Because we've used <RoutePrefix("listener")> for the controller and <Route("ipn")> for the InstantPaymentNotification function, our IPN listener service would be exposed as follows:
http://yourdomain.com/listener/ipn
At this point you have an IPN listener that can:
1) Receive IPN notifications from PayPal
2) Respond back instantly to PayPal for the verification handshake
3) Log notification activity to the [Notifications] table in your database
This is the point in the process where you could go several different directions, and ultimately how you validate and process the data is going to be dependent on what types of transaction types (txn_type) you support and what types of services you are selling (e.g. products, subscriptions, etc).
With that said, here is an example of how you might do some data processing in SQL (e.g. a scheduled job that runs every minute).
We'll start with a Split table-valued function to help with splitting the IPN data vertically.
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[Split]
(
@List VARCHAR(MAX),
@Delimiter VARCHAR(1)
)
RETURNS @Table TABLE
(
ID INT IDENTITY(1,1),
Value VARCHAR(MAX)
)
AS
BEGIN
-- loop through the list
WHILE (CHARINDEX(@Delimiter, @List) > 0)
BEGIN
-- add the value to the table
INSERT INTO @Table
(Value)
SELECT Value = LTRIM(RTRIM(SUBSTRING(@List, 1, CHARINDEX(@Delimiter, @List)-1)))
-- remove the value from the list
SET @List = SUBSTRING(@List, CHARINDEX(@Delimiter, @List) + 1, LEN(@List))
END
-- insert remaining value from the list
INSERT INTO @Table
(Value)
SELECT Value = LTRIM(RTRIM(@List))
-- return
RETURN
END
GO
Next we'll use a SQL script to process any unprocessed notifications.
/*
Important: After you have authenticated an IPN message (received a VERIFIED response from PayPal), you must perform these important checks before you can assume that the IPN is both legitimate and has not already been processed:
Check that the payment_status is Completed.
If the payment_status is Completed, check the txn_id against the previous PayPal transaction that you processed to ensure the IPN message is not a duplicate.
Check that the receiver_email is an email address registered in your PayPal account.
Check that the price (carried in mc_gross) and the currency (carried in mc_currency) are correct for the item (carried in item_name or item_number).
Once you have completed these checks, IPN authentication is complete.
Now, you can update your database with the information provided and initiate any back-end processing that's appropriate.
*/
-- set nocount on
SET NOCOUNT ON;
-- if status is not verified mark it as processed and move on
UPDATE dbo.Notifications
SET Processed = 1,
ProcessedDate = GETDATE(),
Notes = 'Invalid'
WHERE Type = 'IPN'
AND Processed = 0
AND Status != 'VERIFIED'
-- temp table to get a list of records that are being processed in this batch
DECLARE @Batch TABLE
(
NotificationID INT NOT NULL DEFAULT 0
)
-- temp table to split out the IPN data vertically
DECLARE @Details TABLE
(
BatchID VARCHAR(50) NOT NULL DEFAULT '',
NotificationID INT NOT NULL DEFAULT 0,
Property VARCHAR(50) NOT NULL DEFAULT '',
Value VARCHAR(MAX) NOT NULL DEFAULT ''
)
-- get unprocessed notifications that are verified and save in temp table
INSERT INTO @Batch
(NotificationID)
SELECT NotificationID
FROM dbo.Notifications
WHERE Type = 'IPN'
AND Processed = 0
AND Status = 'VERIFIED'
ORDER BY NotificationID
-- declare variables for cursor
DECLARE @NotificationID INT = 0
DECLARE @Data VARCHAR(MAX) = ''
-- declare cursor
DECLARE MyCursor CURSOR LOCAL FAST_FORWARD
FOR
SELECT n.NotificationID,
n.Data
FROM dbo.Notifications n
JOIN @Batch x ON n.NotificationID = x.NotificationID
ORDER BY n.NotificationID
-- open cursor
OPEN MyCursor
-- get the first result
FETCH NEXT FROM MyCursor INTO @NotificationID, @Data
-- loop through results
WHILE @@FETCH_STATUS = 0
BEGIN
-- variables
DECLARE @BatchID VARCHAR(50) = FORMAT(GETDATE(), 'yyyyMMddHHmmssfffffff') + dbo.GetRandomString(5)
-- split IPN data vertically
INSERT INTO @Details
(BatchID, NotificationID, Property, Value)
SELECT BatchID = @BatchID,
@NotificationID,
Property = (SELECT Value FROM dbo.Split(Value, '=') WHERE ID = 1),
Value = (SELECT dbo.DecodeValue(Value) FROM dbo.Split(Value, '=') WHERE ID = 2)
FROM dbo.Split(@Data, '&')
-- fetch the next record
FETCH NEXT FROM MyCursor INTO @NotificationID, @Data
END
-- close cursor
CLOSE MyCursor
DEALLOCATE MyCursor
-- extract core information that we are interested in for our payment processing
-- txn_id, txn_type, item_name, item_number, option_selection1, custom, payment_status
UPDATE n
SET n.TransactionID = ISNULL(x.txn_id, ''),
n.TransactionType = ISNULL(x.txn_type, ''),
n.ItemName = ISNULL(x.item_name, ''),
n.ItemNumber = ISNULL(x.item_number, ''),
n.[Option] = ISNULL(x.option_selection1, ''),
n.Email = ISNULL(x.custom, ''),
n.PaymentStatus = ISNULL(x.payment_status, '')
FROM @Details d
PIVOT (
MAX(d.Value)
FOR Property IN (txn_id, txn_type, item_name, item_number, option_selection1, custom, payment_status)
) AS x
JOIN dbo.Notifications n ON x.NotificationID = n.NotificationID
-- process non-payments
UPDATE n
SET n.Processed = 1,
n.ProcessedDate = GETDATE(),
n.Notes = 'Non-payment'
FROM dbo.Notifications n
JOIN @Batch x ON n.NotificationID = x.NotificationID
WHERE n.Processed = 0
AND (LEN(n.TransactionID) = 0 OR LEN(n.PaymentStatus) = 0)
-- process duplicate transactions
UPDATE n
SET n.Processed = 1,
n.ProcessedDate = GETDATE(),
n.Notes = 'Duplicate'
FROM dbo.Notifications n
JOIN @Batch x ON n.NotificationID = x.NotificationID
WHERE n.Processed = 0
AND n.TransactionID IN (SELECT y.TransactionID FROM dbo.Notifications y WHERE y.Processed = 1)
-- process non-completed payments
UPDATE n
SET n.Processed = 1,
n.ProcessedDate = GETDATE(),
n.Notes = 'Unknown payment status'
FROM dbo.Notifications n
JOIN @Batch x ON n.NotificationID = x.NotificationID
WHERE n.Processed = 0
AND n.PaymentStatus != 'Completed'
-- process completed payments
UPDATE n
SET n.Processed = 1,
n.ProcessedDate = GETDATE(),
n.Notes = 'Completed'
FROM dbo.Notifications n
JOIN @Batch x ON n.NotificationID = x.NotificationID
WHERE n.Processed = 0
AND n.PaymentStatus = 'Completed'
This is the point where if you have new "completed" payments you could trigger other workflow items in your system, such as email notifications to send a license key, or renewing a subscription, or otherwise giving access to the product/service to the payee.
Remember this is just a starting point and your specific data processing implementation will vary based on your needs.
Matt Pavey is a Microsoft Certified software developer who specializes in ASP.Net, VB.Net, C#, AJAX, LINQ, XML, XSL, Web Services, SQL, jQuery, and more. Follow on Twitter @matthewpavey
0 comments: