bertwagner

USE Sandbox
GO
-- 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
CREATE TABLE dbo.CoffeeInventory
(
	ID int IDENTITY(1,1),
	Name VARCHAR(100),
	Price DECIMAL(4,2),
	Description VARCHAR(500),

bertwagner

-- From https://github.com/bertwagner/SQLServer/blob/master/Non-SARGable%20Execution%20Plans.sql
-- This script will check the execution plan cache for any queries that are non-SARGable.
-- It does this by finding table and index scans that contain a scalar operators

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 
DECLARE @dbname SYSNAME
SET @dbname = QUOTENAME(DB_NAME());
 
WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

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),

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_GetFullName
	@ParmUserName varchar(100)
AS
BEGIN
	DECLARE @FullQuery varchar(1000)
	SET @FullQuery = 'SELECT FullName FROM dbo.RegisteredUser WHERE UserName = ''' + @ParmUserName + ''''

	EXEC(@FullQuery);
END

bertwagner

EXEC dbo.sp_GetFullName 'TFly37' 

bertwagner

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

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

-- 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_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)';