Using AI for Content Moderation with Azure SQL Database

In my previous posts, I have covered using Azure OpenAI services with Azure SQL Database via REST endpoints. This post with further explore that pathway by creating stored procedures you can use to encapsulate the REST calls and combine them with custom business logic for AI content moderation with Azure SQL Database.

A Quick Introduction

The services to being used in this post are the new Azure AI Content Safety service and the Azure AI Language service. Azure AI Content Safety is a solution designed to detect harmful user-generated and AI-generated content in applications and services. It can detect such content from either text or images. Azure AI Language is a service that provides Natural Language Processing (NLP) features for understanding and analyzing text. Here we can extract and redact sensitive information in documents or text.

Using the Services

Both services have REST endpoints we will be contacting from the database using External REST Endpoint Invocation (lovingly referred to as EREI). The call to the endpoints will be encapsulated inside stored procedures so that we can use them with other objects and services such as Data API Builder or Azure SQL bindings for Azure Functions.

READ ME!

In the following examples, you will need to provide the procedures with the names of your AI Language and AI Content Safety services.

Azure AI Language

First up is Azure AI Language. In this sample, we are using the Analyze Text REST endpoint and asking it to look for and redact any personally identifiable information (PII). The endpoint requires the request body to contain some parameters for this type of analysis. Here is an example for reference:

{
    "kind": "PiiEntityRecognition",
    "analysisInput":
    {
        "documents":
        [
            {
                "id":"1",
                "language": "en",
                "text": "abcdef@abcd.com, this is my phone is 6657789887, and my IP: 255.255.255.255 127.0.0.1 fluffybunny@bunny.net, My Addresses are 1 Microsoft Way, Redmond, WA 98052, SSN 543-55-6654, 123 zoo street chickenhouse, AZ 55664"
            }
        ]
    }
}

The attribute “kind” is set to PiiEntityRecognition tell the endpoint how to analyze the text. Inside the analysisInput, is the document. Here you provide the text and the language of text (which is optional, the service can automatically discover the language).

The endpoint is in the structure of

{Endpoint}/language/:analyze-text?api-version=2023-04-01

Where the Endpoint is your Azure AI Language endpoint which is set on creation of the service.

Create the Stored Procedure

Putting this all together into a stored procedure looks like the following:

CREATE PROCEDURE check4pii @cogserver nvarchar(100), @cogkey nvarchar(100), @message nvarchar(max)
AS

declare @url nvarchar(4000) = N'https://'+ @cogserver +'.cognitiveservices.azure.com/language/:analyze-text?api-version=2023-04-01';
declare @headers nvarchar(300) = N'{"Ocp-Apim-Subscription-Key":"'+ @cogkey +'"}';
declare @payload nvarchar(max) = N'{
                                        "kind": "PiiEntityRecognition",
                                        "analysisInput":
                                        {
                                            "documents":
                                            [
                                                {
                                                    "id":"1",
                                                    "language": "en",
                                                    "text": "'+ @message +'"
                                                }
                                            ]
                                        }
                                    }';
declare @ret int, @response nvarchar(max);

exec @ret = sp_invoke_external_rest_endpoint
@url = @url,
@method = 'POST',
@headers = @headers,
@payload = @payload,
@timeout = 230,
@response = @response output;

SELECT A.[value] as "Redacted Text"
FROM OPENJSON(@response,'$.result.results.documents') AS D
CROSS APPLY OPENJSON([value]) as A
where A.[key] = 'redactedText'

select JSON_VALUE(B.[value],'$.category') as "PII Category",
JSON_VALUE(B.[value],'$.text') as "PII Value",
CONVERT(FLOAT,JSON_VALUE(B.[value],'$.confidenceScore'))*100 as "Confidence Score"
from OPENJSON(
(
    SELECT A.[value]
    FROM OPENJSON(@response,'$.result.results.documents') AS D
    CROSS APPLY OPENJSON([value]
    ) AS A
    where A.[key] = 'entities'
), '$') AS B

GO

Breaking this down, we first declare the pieces that are going to make up the REST request body and header. The next section calls the endpoint via sp_invoke_external_rest_endpoint. Once we receive the response, we pull out the redacted text as well as the PII it found and the classification and confidence score as a percent.

The response message looks like the following for reference:

"result": {
"kind": "PiiEntityRecognitionResults",
"results": {
    "documents": [
    {
    "redactedText": "my email is ***************, this my phone is **********, and my IP: *************** My address ********************************** and SSN ***********",
    "id": "1",
    "entities": [
        {
        "text": "abcdef@abcd.com",
        "category": "Email",
        "offset": 12,
        "length": 15,
        "confidenceScore": 0.8
        },
        {
        "text": "6657789887",
        "category": "EUTaxIdentificationNumber",
        "offset": 46,
        "length": 10,
        "confidenceScore": 0.93
        },
        {
        "text": "255.255.255.255",
        "category": "IPAddress",
        "offset": 69,
        "length": 15,
        "confidenceScore": 0.8
        },
        {
        "text": "1 Microsoft Way, Redmond, WA 98052",
        "category": "Address",
        "offset": 96,
        "length": 34,
        "confidenceScore": 1.0
        },
        {
        "text": "543-55-6654",
        "category": "USSocialSecurityNumber",
        "offset": 139,
        "length": 11,
        "confidenceScore": 0.85
        }

And this is an example of running the stored procedure:

EXEC check4pii @cogserver='cogserver',
               @cogkey='12345678901234567890',
               @message='my email is abcdef@abcd.com, this my phone is 6657789887, and my IP: 255.255.255.255 My address 1 Microsoft Way, Redmond, WA 98052 and SSN 543-55-6654';

with the results being: