Using JSON Objects with SQL Server 2016 and Above by Matt Luckham

This article first appeared in the SQLServerGeeks Magazine.
Author: Matt Luckham
Subscribe to get your copy.

JSON has become the standard transmission protocol between front ends and your cloud API. Typically, the cloud API is responsible for consuming the JSON Object and then passing that data through to the database layer.

At my company, Care Control, we use SQL Server 2019 as our production database architecture. We made a decision that the database would be responsible for consuming data requests as JSON objects, and always return a JSON objects as a result.

This consistency made our Cloud API (we use .Net Core) more simplistic, with more a role as a pass through platform with the occasional responsibility for enriching the JSON object data with further data.

Simplified Login Process
We use Microsoft .Net Core to communicate with our production databases. I have extracted and simplified our login process for this example. The process describes a use case of a user “login in” to our system through the client web front end.

Step 1 – Define Login JSON Object
The first step is to define the object that the front end will produce to post to the login end point. For this example, the login object is as follows:-

{
  "LoginDetails": {
    "Username": "",
    "PasswordHash": ""
  }
}

This password hash field will hold a SHA 256 bit hashed password. Most front end libraries support 256 bit hash encryption. It is important that the hashing happens at the front end and we don’t pass the actual password anywhere.

Never store the real password in the database!

Step 2 – Cloud API Enrichment
We pass the object to an open api. We use .Net Core with a Swagger (Open API) interface. For this call we want the Cloud API to enrich the JSON object with the calling IP Address. It is important that the cloud API adds this detail, rather than the front end to improve security:-

JSON = await JsonManager.ConstructLoginDetails(Convert.ToString(ParsedJSON.
CreateSession.Username), 
                    Convert.ToString(ParsedJSON.CreateSession.PasswordHash), 
                    Convert.ToString(session.OriginIPAddress));

This results in an object that now looks like this:-

{
  "LoginDetails": {
    "Username": "MLUCKHAM",
    "PasswordHash": "a7c96262c21db9a06fd49e307d694fd95f624569f9b35bb3ffacd880440f9787",
    "OriginIPAddress": "192.168.1.25"
  }
}

Step 3 – Call ValidateUser Stored Procedure
We then pass the object into the ValidateUser stored procedure to validate the login details. In our .Net Core Web API we use the sqlClient.SQLCommand object:-

This is our full C# code to call a generic stored procedure:-

public async Task<string> RunProcedure(Enums.ConnectionType Type,
string ProcName, SqlParameter[] parameters)
        {
            string connectionString;
            string result;

            // build the connection string for this calling type
            connectionString = GetConnectionString(Type);

            // create the new connection based off our connection string.
            // we're doing this within a using block for disposing reasons. 
            using (var Conn = new SqlConnection(connectionString)) {

                // open SQL connection with the database.
                Conn.Open();

                // create the new database command.
                SqlCommand cmd = new SqlCommand(ProcName, Conn);

                // set the command type to procedure.
                cmd.CommandType = CommandType.StoredProcedure;

                // Implement the parameters here.
                foreach (SqlParameter p in parameters)
                    cmd.Parameters.Add(p);

		  // We use ExecureXMLReader as we are pulling back a single large blob
                using (var reader = cmd.ExecuteXmlReader())
                {
                    if (reader.Read()) // Don't assume we have any rows.
{
                        result = reader.Value; // Handles nulls and empty strings.
                    }
                    else
                    {
                        result = "Error";
                    }
                }

                // results
                Conn.Close();
            }

            return result;
        }

Step 4 – Stored Procedure – ValidateUser
The stored procedure is the entry point to the database for the end point. In this example I have simplified the various operations that this procedure could perform, to focus on the substance of this article – validating the username and password.

This is our stored procedure. We will then break it down afterwards:-

CREATE PROCEDURE [Business].[ValidateUser]
(
	@dataObject VARCHAR(MAX)
)
AS
BEGIN

	DECLARE @ERROR VARCHAR(255) = '';
	DECLARE @RETURN INT = 0;
	DECLARE @ERROR VARCHAR(255) = '';
	DECLARE @RETURN INT = 0;
	DECLARE @UserName VARCHAR(50)
	DECLARE @PasswordHash VARCHAR(150)
	DECLARE @OriginIPAddress VARCHAR(20)
	DECLARE @UserID INT = -1
	DECLARE @TwoFactor VARCHAR(5) = 'FALSE'
	DECLARE @TrustIP VARCHAR(5) = 'FALSE'


	SET NOCOUNT ON;

	BEGIN TRY		

		SELECT
			@UserName = d.Username,
			@PasswordHash = d.PasswordHash,
			@OriginIPAddress = d.OriginIPAddress
		FROM [Business].[tbfLoginDetails](@dataObject) as d

		IF TRIM(ISNULL(@UserName,'')) = ''
			RAISERROR('Username cannot be spaces or blank ',18,1)
	
		IF TRIM(ISNULL(@PasswordHash,'')) = '' 
			RAISERROR('Password cannot be spaces or blank',18,1)


		IF EXISTS (SELECT * FROM [Business].[Users] as U
			INNER JOIN [Business].[UserStatus] as US ON US.StatusID = U.StatusID
							WHERE Username = @UserName AND US.StatusDescription <> 'Deactive' )
		BEGIN

			SELECT @UserID = UserID FROM [Business].[Users] as U WHERE Username = @UserName

			IF NOT EXISTS (SELECT * FROM [Business].[Users] as U
					WHERE UserID = @UserID AND HashPassword = @PasswordHash)
				RAISERROR('Password incorrect',18,1)

			-- Check if user has IP restriction
			IF EXISTS (SELECT * FROM [Business].[Users_IPAddress_Inclusion] as UI 
					INNER JOIN [Business].[Users] as U ON U.UserID = UI.UserID WHERE U.Username  = @UserName)
				IF NOT EXISTS (SELECT * FROM [Business].[Users_IPAddress_Inclusion] as UI 
						INNER JOIN [Business].[Users] as U ON U.UserID = UI.UserID 
WHERE U.Username  = @UserName AND IPAddress = @OriginIPAddress)
					RAISERROR('Invalid origin IP Address',18,1)

			-- Call is valid - return object
			SELECT Business.BuildLoginResponseObj(@UserName, @TrustIP, @TwoFactor)
		END
		ELSE
			RAISERROR('Username does not exist',18,1)

	END TRY

	BEGIN CATCH
		set @ERROR = 'ERROR:' + ERROR_MESSAGE()

		IF ISNULL(@UserID,0) > 0
			INSERT INTO Business.Users_AccessLog (OriginIPAddress, UserID, Result, AccessDate)
			SELECT @OriginIPAddress, @UserID, @ERROR, GETDATE()

		SELECT [dbo].[PostAPIResult]('Failed', '', @ERROR)
	END CATCH
END

Stored Procedure Breakdown

Entry Point
CREATE PROCEDURE [Business].[ValidateUser]
(
	@dataObject VARCHAR(MAX)
)
AS
BEGIN

	DECLARE @ERROR VARCHAR(255) = '';
	DECLARE @RETURN INT = 0;
	DECLARE @ERROR VARCHAR(255) = '';
	DECLARE @RETURN INT = 0;
        DECLARE @UserName VARCHAR(50)
	DECLARE @PasswordHash VARCHAR(150)
		DECLARE @OriginIPAddress VARCHAR(20)
		DECLARE @UserID INT = -1
		DECLARE @TwoFactor VARCHAR(5) = 'FALSE'
		DECLARE @TrustIP VARCHAR(5) = 'FALSE'

		SET NOCOUNT ON; 

The first few lines introduces the single parameter that is going to be passed – our data object. We declare a variable to hold our Error Value and a Return Value. We also declare other variables that we are going to use. Of course, we set NoCount to be On to give us that extra little performance boost.

Extract Data

The first part of the stored procedure is to extract the data from the data object. We create a table value function to provide a pseudo schema for our JSON Object. This is out TVF:-

CREATE FUNCTION [Business].[tbfLoginDetails](@JsonData VARCHAR(MAX)) -- Jsondata parametre
RETURNS @t TABLE (											-- return temp table with these fields
			Username VARCHAR(50),
			PasswordHash VARCHAR(150),
			OriginIPAddress VARCHAR(20)

			)
AS
BEGIN

	INSERT INTO @t								
	SELECT 
		LoginDetails.Username,
		LoginDetails.PasswordHash,
		LoginDetails.OriginIPAddress

	FROM OPENJSON(@JsonData)		
	WITH
		(LoginDetails NVARCHAR(MAX) AS JSON) as ObjJson
	CROSS APPLY OPENJSON (ObjJson.LoginDetails)
	WITH 
		(Username VARCHAR(50),
		PasswordHash VARCHAR(150),
		OriginIPAddress VARCHAR(20)) as LoginDetails

    RETURN
END

We use his TVF in our stored procedure as below:-

BEGIN TRY		
		SELECT
			@UserName = d.Username,
			@PasswordHash = d.PasswordHash,
			@OriginIPAddress = d.OriginIPAddress
		FROM [Business].[tbfLoginDetails](@dataObject) as d

		IF TRIM(ISNULL(@UserName,'')) = ''
			RAISERROR('Username cannot be spaces or blank ',18,1)
IF TRIM(ISNULL(@PasswordHash,'')) = '' 
			RAISERROR('Password cannot be spaces or blank',18,1)

We do some basic checking that the returned values are valid. Note the RAISERROR commands. This will jump code to our Catch Block as long as the Severity (second param) is greater than 10 and less than 20. We use 18 as a default.

Checking the Values
The next part of the Stored Procedure will check the login credentials:-

IF EXISTS (SELECT * FROM [Business].[Users] as U
					INNER JOIN [Business].[UserStatus] 
as US ON US.StatusID = U.StatusID
					WHERE Username = @UserName AND US.StatusDescription <> 'Deactive' )
BEGIN

	SELECT @UserID = UserID FROM [Business].[Users] as U WHERE 
Username = @UserName

	IF NOT EXISTS (SELECT * FROM [Business].[Users] as U
					WHERE UserID = @UserID AND 
HashPassword = @PasswordHash)
		RAISERROR('Password incorrect',18,1)

Here we are first checking that the Username matches and the user status is not deactive. If it passes this test we extract the UserID from the Users Table (will we use this later) and then verify the password hash. Remember, the password hash is a hexadecimal number so case sensitivity is not important.

Checking the IP Address
The system supports restricting access to set IP Addresses. The next check verifies that the IP Address passed is valid:-

   
-- Check if user has IP restriction
IF EXISTS (SELECT * FROM [Business].[Users_IPAddress_Inclusion]
 as UI 
					INNER JOIN [Business].[Users]
as U ON U.UserID = UI.UserID WHERE U.Username  = @UserName)
	IF NOT EXISTS (SELECT * FROM [Business].[Users_IPAddress_Inclusion]
as UI 
INNER JOIN [Business].[Users]
as U ON U.UserID = UI.UserID WHERE 
U.Username  = @UserName AND
IPAddress = @OriginIPAddress)
		RAISERROR('Invalid origin IP Address',18,1)

Here we first check if the User has an IP Address Inclusion. We then check if the IP Address passed is in that inclusion list. If the IP Address is not valid we raise an error.

-- Call is valid - return object
		SELECT Business.BuildLoginResponseObj(@UserName, @TrustIP, @TwoFactor)
END
ELSE
		RAISERROR('Username does not exist',18,1) 

The final part of procedure is to return a predefined login object based on a set of parameters. We return a standard object from any login. This is the JSON constructed:-

{
   "LoginResponse":{
      "UserID":28,
      "UserFirstName":"Matty",
      "UserSurname":"Luckham",
      "CurrentStatus":"Active",
      "StartView":"",
      "DefaultLanguage":"Eng",
      "TrustedIP":"FALSE",
      "TwoFactorRequired":"FALSE",
      "StaffID":1,
      "SystemSettings":[
         {
            "SettingName":"ForceMessageReadonSignOut",
            "SettingValue":"0"
         },
         {
            "SettingName":"BypassMessageSignIn",
            "SettingValue":"0"
         }
      ]
   }
}

This object is created using the following scalar function which looks like this:-

CREATE FUNCTION [Business].[BuildLoginResponseObj]
(
    @Username VARCHAR(50),
	@TrustedIP VARCHAR(5),
	@TwoFactorRequired VARCHAR(5)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN

    DECLARE @ReturnJSON NVARCHAR(MAX)
	DECLARE @Trusted VARCHAR(3)

    DECLARE @TempTable TABLE (
	UserID INT,
	UserFirstName VARCHAR(50),
	UserSurname VARCHAR(50),
	CurrentStatus VARCHAR(20),
	StartView VARCHAR(50),
	DefaultLanguage VARCHAR(3),
	TrustedIP VARCHAR(5),
	TwoFactorRequired VARCHAR(5),
	StaffID INT)
INSERT INTO @TempTable
		SELECT
			U.UserID,
			U.FirstName,
			U.Surname,
			US.StatusDescription,
			Business.GetApplicationView(U.UserID),
			U.DefaultLanguage,
			@TrustedIP,
			@TwoFactorRequired,
			U.StaffLink AS StaffID
		FROM	[Business].[Users] U
		LEFT OUTER JOIN [Business].[UserStatus] US ON U.STATUSID = US.STATUSID
		WHERE	ISNULL(U.USERNAME,'') = @UserName;
	
	-- Settings table for the settings array passed down at login.
	DECLARE @SystemSettingsTempTable TABLE (
		SettingName varchar(50),
		SettingValue varchar(50)
	)
	
	-- population of the message settings
	INSERT INTO @SystemSettingsTempTable
	SELECT ConstantName, ConstantValue 
	FROM dbo.SystemConstants
	WHERE ConstantName = 'BypassMessageSignIn' 
		OR ConstantName = 'ForceMessageReadonSignOut'

	SET @ReturnJSON = (
		SELECT JSON_QUERY(
			(SELECT 
				UserID, 
				UserFirstName,
				UserSurname,
				CurrentStatus,
				StartView,
				DefaultLanguage,
				TrustedIP, 
				TwoFactorRequired, 
				StaffID,
				(
					SELECT JSON_QUERY((
						SELECT SettingName, SettingValue
						FROM @SystemSettingsTempTable 
					FOR JSON AUTO))
				) as SystemSettings 
			FROM 
				@TempTable
			FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER)) as LoginResponse
		FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
	)
		
    RETURN @ReturnJSON
END

This object returns a standard login object based on the username that was passed. We return a standard JSON object with a child object array of some system settings.
Note the interesting notation:-

SELECT JSON_QUERY(
			(SELECT 
…..

FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER)) as LoginResponse
		FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

We do this as if we want to create a child object, unless we use the JSON_QUERY function the inner JSON will become escaped. Notice we also want to remove any array wrappers.

The Catch Block

BEGIN CATCH
	set @ERROR = 'ERROR:' + ERROR_MESSAGE()

	IF ISNULL(@UserID,0) > 0
		INSERT INTO Business.Users_AccessLog (OriginIPAddress, UserID, Result, AccessDate)
		SELECT @OriginIPAddress, @UserID, @ERROR, GETDATE()

	SELECT [dbo].[PostAPIResult]('Failed', '', @ERROR)
END CATCH

In our catch block we return any errors using a scalar function called PostAPIResult. We also do some logging in here. Our APIResult function looks like this:-

CREATE FUNCTION [dbo].[PostAPIResult]
(
    @Result NVARCHAR(50),
    @ExtraDetails NVARCHAR(100),
    @ErrorDetails NVARCHAR(100)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @ReturnJSON NVARCHAR(MAX)
    DECLARE @TempTable TABLE (Result NVARCHAR(50), extraDetails NVARCHAR(100), errorDetails NVARCHAR(100))
   
   INSERT INTO @TempTable (Result, extraDetails, errorDetails) 
   VALUES (@Result, @ExtraDetails, @ErrorDetails)

    SET @ReturnJSON = (SELECT * FROM @TempTable FOR JSON AUTO, without_array_wrapper);

    RETURN @ReturnJSON
END

That is how we validate a user with a username and hashed password!

A little more on Table Value Functions
The SQL Server Table Value Function allow you to represent more complex data in a table format. You can use a TVF to “process” a JSON Object and return it as a standard relational table format for further processing. Your TVF can present a schema for JSON Objects.

Simple JSON Object

If we take a simple JSON Object:-

{"ClientRecord": {
    "UniqueID": "1",
    "FirstName": "Matt",
    "LastName": "Luckham",
    "Contacts": [
      {
        "Title": "Mr",
        "FirstName": "Frank",
        "LastName": "Jones",
        "Relationship": "Son"
      },
      {
        "Title": "Mrs",
        "FirstName": "Sarah",
        "LastName": "Jones",
        "Relationship": "Daughter"
      }
    ]
  }
}

We can use a Table Value Function to “process” this object to then present the data back to other database operations.

CREATE FUNCTION [dbo].[tbfClientRecord](@JsonData VARCHAR(MAX))
RETURNS @t TABLE (											
			UniqueID INT,
			FirstName VARCHAR(50),
			Surname VARCHAR(50))
AS
BEGIN
	INSERT INTO @t							
	SELECT 
		ClientRecord.UniqueID,
		ClientRecord.FirstName,
		ClientRecord.LastName
	FROM OPENJSON(@JsonData)		
	WITH
		(ClientRecord NVARCHAR(MAX) AS JSON) as ObjJson
	CROSS APPLY OPENJSON (ObjJson.ClientRecord)
	WITH 
		(UniqueID INT,
		FirstName VARCHAR(50),
		LastName VARCHAR(50)) as ClientRecord
    RETURN
END
GO 

If we run our object code into this TVF we get the following result:-

DECLARE @JSON Varchar(max) = '{
  "ClientRecord": {
    "UniqueID": "1",
    "FirstName": "Matt",
    "LastName": "Luckham",
    "Contacts": [
      {
        "Title": "Mr",
        "FirstName": "Frank",
        "LastName": "Jones",
        "Relationship": "Son"
      },
      {
        "Title": "Mrs",
        "FirstName": "Sarah",
        "LastName": "Jones",
        "Relationship": "Daughter"
      }
    ]
  }
}'
SELECT * FROM [dbo].[tbfClientRecord](@JSON)
If you run this code you get: -

UniqueID	FirstName	Surname
    1	      Matt	    Luckham

We can also add a TVF to extract the client contact array:-

CREATE FUNCTION [dbo].[tbfClientContacts](@JsonData VARCHAR(MAX))
RETURNS @t TABLE (											
			ContactID INT IDENTITY(1,1),
			ClientID INT,
			Title VARCHAR(10),
			FirstName VARCHAR(50),
			LastName VARCHAR(50),
			Relationship VARCHAR(50)
			)
AS
BEGIN
INSERT INTO @t (ClientID, Title, FirstName, LastName, Relationship)					
	SELECT 
		ClientRecord.UniqueID,
		Contacts.Title,
		Contacts.FirstName,
		Contacts.LastName,
		Contacts.Relationship
	FROM OPENJSON(@JsonData)		
	WITH
		(ClientRecord NVARCHAR(MAX) AS JSON) as ObjJson
	CROSS APPLY OPENJSON (ObjJson.ClientRecord)
	WITH 
		(UniqueID INT,
		Contacts NVARCHAR(MAX) as JSON) as ClientRecord
	CROSS APPLY OPENJSON (ClientRecord.Contacts)
	WITH
		(	Title VARCHAR(10),
			FirstName VARCHAR(50),
			LastName VARCHAR(50),
			Relationship VARCHAR(50)) as Contacts
    RETURN
END
GO 

When we use this function like this:-

DECLARE @JSON Varchar(max) = '{
  "ClientRecord": {
    "UniqueID": "1",
    "FirstName": "Matt",
    "LastName": "Luckham",
    "Contacts": [
      {
        "Title": "Mr",
        "FirstName": "Frank",
        "LastName": "Jones",
        "Relationship": "Son"
      },
      {
        "Title": "Mrs",
        "FirstName": "Sarah",
        "LastName": "Jones",
        "Relationship": "Daughter"
      }
    ]
  }
}'
SELECT * FROM [dbo].[tbfClientContacts](@JSON)

And we run this function we get this data set:-

And of course with the two functions we can de-normalise the data and output the entire JSON object as a usable table:-

DECLARE @JSON Varchar(max) = '{
  "ClientRecord": {
    "UniqueID": "1",
    "FirstName": "Matt",
    "LastName": "Luckham",
    "Contacts": [
      {
        "Title": "Mr",
        "FirstName": "Frank",
        "LastName": "Jones",
        "Relationship": "Son"
      },
      {
        "Title": "Mrs",
        "FirstName": "Sarah",
        "LastName": "Jones",
        "Relationship": "Daughter"
      }
    ]
  }
}'
SELECT 
	CR.UniqueID,
	CR.FirstName,
	CR.Surname,
	CC.ContactID,
	CC.Title,
	CC.FirstName,
	CC.LastName,
	CC.Relationship
FROM [dbo].[tbfClientRecord](@JSON) as CR
INNER JOIN [dbo].[tbfClientContacts](@JSON) as CC ON 
CC.ClientID = CR.UniqueID

Hopefully you found this article useful in how you can use SQL Server to consume JSON Objects in a consistent way.

This article first appeared in the SQLServerGeeks Magazine.
Author: Matt Luckham
Subscribe to get your copy.

   

Leave a Reply

Your email address will not be published.