SQL Server

Extract Linked Domains from an HTML Email

Demonstrates the Chilkat Email.LinkedDomains method, which extracts the domain names from the hyperlinks in an HTML email. Lowercase domains are appended to a StringTable without creating duplicates. This example sets an HTML body with several links and lists the distinct domains.

Background: The set of domains a message links to is a strong signal for spam and phishing analysis — legitimate mail usually points at a few expected domains, while abusive mail often hides links to many unrelated or look-alike hosts. LinkedDomains gives you that de-duplicated list directly. Note it only parses the links; it does not fetch them, so no network request is made.

Chilkat SQL Server Downloads

Download Chilkat for SQL Server

SQL Server

--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    --  Demonstrates the LinkedDomains method, which extracts the domain names from the
    --  hyperlinks in an HTML email.  Lowercase domains are appended to a StringTable without
    --  creating duplicates.

    DECLARE @email int
    EXEC @hr = sp_OACreate 'Chilkat.Email', @email OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OASetProperty @email, 'Subject', 'LinkedDomains example'

    --  An HTML body containing several hyperlinks.
    EXEC sp_OAMethod @email, 'SetHtmlBody', NULL, '<html><body>Visit <a href="https://www.example.com/page">Example</a>, <a href="http://sales.contoso.com">Contoso</a>, and <a href="https://www.example.com/other">Example again</a>.</body></html>'

    --  Extract the linked domains into a StringTable.
    DECLARE @domains int
    EXEC @hr = sp_OACreate 'Chilkat.StringTable', @domains OUT

    EXEC sp_OAMethod @email, 'LinkedDomains', @success OUT, @domains
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @email, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @email
        EXEC @hr = sp_OADestroy @domains
        RETURN
      END

    DECLARE @n int
    EXEC sp_OAGetProperty @domains, 'Count', @n OUT
    DECLARE @i int

    SELECT @i = 0
    WHILE @i <= @n - 1
      BEGIN
        EXEC sp_OAMethod @domains, 'StringAt', @sTmp0 OUT, @i
        PRINT @sTmp0
        SELECT @i = @i + 1
      END

    EXEC @hr = sp_OADestroy @email
    EXEC @hr = sp_OADestroy @domains


END
GO