bertwagner

-- See https://gist.github.com/bertwagner/356bf47732b9e35d2156daa943e049e9 for a formatted version of this JSON
DECLARE @garage nvarchar(1000) = N'{ "Cars": [{ "Make": "Volkswagen", "Model": { "Base": "Golf", "Trim": "GL" }, "Year": 2003, "PurchaseDate": "2006-10-05T00:00:00.000Z" }, { "Make": "Subaru", "Model": { "Base": "Impreza", "Trim": "Premium" }, "Year": 2016, "PurchaseDate": "2015-08-18T00:00:00.000Z" }] }'

SELECT JSON_VALUE(@garage, '$.Cars[0].Make') -- Return the make of the first car in our array
-- Output: Volkswagen

SELECT CAST(JSON_VALUE(@garage, '$.Cars[0].PurchaseDate') as datetime2) -- Return the Purchase Date of the first car in our array and convert it into a DateTime2 datatype
-- Output: 2006-10-05 00:00:00.0000000

SELECT JSON_VALUE(@garage, '$.Cars') -- This returns NULL because the values of Cars is an array instead of a simple object

bertwagner

USE Sandbox;
GO
DROP TABLE IF EXISTS dbo.RegisteredUser
CREATE TABLE dbo.RegisteredUser
(
	Id INT IDENTITY(1,1),
	FullName varchar(100),
	UserName varchar(40),
	HashedPassword varchar(66),
	JoinDate datetime2

bertwagner

CREATE PROCEDURE dbo.sp_GetFullNameFromTableSanitized
	@ParmTableName varchar(100),
	@ParmUserName varchar(100)
AS
BEGIN
	DECLARE @FullQuery nvarchar(1000)
	SET @FullQuery = N'SELECT FullName FROM dbo.' + QUOTENAME(@ParmTableName) + ' WHERE UserName = @UserName'

	DECLARE @ParmDefinition nvarchar(100) = N'@UserName varchar(100)';  

bertwagner

CREATE PROCEDURE dbo.sp_GetFullNameFromTable
		@ParmTableName varchar(100),
		@ParmUserName varchar(100)
AS
BEGIN
	DECLARE @FullQuery nvarchar(1000)
	SET @FullQuery = N'SELECT FullName FROM dbo.@TableName WHERE UserName = @UserName'

	DECLARE @ParmDefinition nvarchar(100) = N'@TableName varchar(100), @UserName varchar(100)';  

bertwagner

-- This file tries to find stored procedures and functions that *may* be vulnerable to SQL injection attacks.

-- It works by searching your database for occurences of "+" signs followed by "@", indicating that SQL parameters
-- might be getting concatenated to a dynamic SQL string.  It also checks for the existence of 'EXEC' to see if any
-- strings are being executed.

-- Not every result returned will be susceptible to SQL injection, however they should all be examined to see if they are vulnerable.

-- Originally fromn: https://github.com/bertwagner/SQLServer/blob/master/SQL%20Injection%20Vulnerabilities.sql

bertwagner

CREATE PROCEDURE dbo.sp_GetFullNameSafe2
	@ParmUserName varchar(100)
AS
BEGIN
	DECLARE @FullQuery nvarchar(1000)
	SET @FullQuery = N'SELECT FullName FROM dbo.RegisteredUser WHERE UserName = @UserName'

	DECLARE @ParmDefinition nvarchar(100) = N'@UserName varchar(100)';  
  
	EXEC sp_executesql @FullQuery, @ParmDefinition,  

bertwagner

CREATE PROCEDURE dbo.sp_GetFullNameSafe
	@ParmUserName varchar(100)
AS
BEGIN
	SELECT FullName FROM dbo.RegisteredUser WHERE UserName =  @ParmUserName
END

bertwagner

EXEC dbo.sp_GetFullName 'TFly37' 

bertwagner

CREATE PROCEDURE dbo.sp_GetFullName
	@ParmUserName varchar(100)
AS
BEGIN
	DECLARE @FullQuery varchar(1000)
	SET @FullQuery = 'SELECT FullName FROM dbo.RegisteredUser WHERE UserName = ''' + @ParmUserName + ''''

	EXEC(@FullQuery);
END

bertwagner

-- Test data taken from my favorite green coffee seller's website, Sweet Maria's https://www.sweetmarias.com/category/green-coffee
--DROP TABLE IF EXISTS dbo.CoffeeInventory

DROP TABLE dbo.CoffeeInventory
CREATE TABLE dbo.CoffeeInventory
(
    ID int IDENTITY(1,1),
    Name VARCHAR(100),
    Price DECIMAL(4,2),
    Description VARCHAR(500),