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.
