Are you aware of WHERE ""="" is Always True

No surprises, we still see many dynamic SQL codes that are wrongly implemented which allow smart hackers to go into unauthorized databases and manipulate data as they want. There are many SQL Injections that are famous and widely discussed but some are tiny, unnoticed hence ignored. Here is something like that.

WHERE ""="" is Always True. This is something you have to remember. If you have not parameterized your code or have not used relevant technique for executing your dynamically constructed TSQL code, you open your database for unauthorized people. Have a look on below stored procedure.

IF OBJECT_ID('dbo.GetMyOrder', 'P') IS NOT NULL
 DROP PROC dbo.GetMyOrder;
GO

CREATE PROCEDURE dbo.GetMyOrder @PurchaseOrderNumber nvarchar(50)
AS
BEGIN

 DECLARE @Sql nvarchar(1000)
 
 SET @Sql = N'SELECT * FROM Sales.SalesOrderHeader'
 SET @Sql += N'  WHERE PurchaseOrderNumber = ''' + @PurchaseOrderNumber + ''''

 PRINT @Sql
 EXEC (@Sql);
END
GO

This stored procedure returns details of the order based on the supplied purchased order number. If you execute the code (directly using SSMS or via an interface developed), he gets the desired result;

Now what if someone has no purchased order number but still wants to see orders. This is where we can use WHERE ""="" injection. See the code below;

See what has happened. This forms the query like below;

SELECT * FROM Sales.SalesOrderHeader  WHERE PurchaseOrderNumber = '' OR ''='' --'

If someone pass a value like this via an interface, he might be able to see records he is not supposed to see. This can be controlled by parameterizing the query and execute the dynamic code using sp_executesql instead SQL.

IF OBJECT_ID('dbo.GetMyOrder', 'P') IS NOT NULL
 DROP PROC dbo.GetMyOrder;
GO

CREATE PROCEDURE dbo.GetMyOrder @PurchaseOrderNumber nvarchar(50)
AS
BEGIN

 DECLARE @Sql nvarchar(1000)
 
 SET @Sql = N'SELECT * FROM Sales.SalesOrderHeader'
 SET @Sql += N'  WHERE PurchaseOrderNumber = @PurchaseOrderNumber'

 PRINT @Sql
 EXEC sp_executesql @Sql, N'@PurchaseOrderNumber nvarchar(50)', @PurchaseOrderNumber;
END
GO

Done, now it cannot be hacked easily.