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.

view plainprint?

1. IF OBJECT_ID('dbo.GetMyOrder', 'P') IS NOT NULL  
2.  DROP PROC dbo.GetMyOrder;  
3. GO  
4.   
5. CREATE PROCEDURE dbo.GetMyOrder @PurchaseOrderNumber nvarchar(50)  
6. AS  
7. BEGIN  
8.   
9.  DECLARE @Sql nvarchar(1000)  
10.    
11.  SET @Sql = N'SELECT * FROM Sales.SalesOrderHeader'  
12.  SET @Sql += N'  WHERE PurchaseOrderNumber = ''' + @PurchaseOrderNumber + ''''  
13.   
14.  PRINT @Sql  
15.  EXEC (@Sql);  
16. END  
17. GO  

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;

view plainprint?

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

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.

view plainprint?

1. IF OBJECT_ID('dbo.GetMyOrder', 'P') IS NOT NULL  
2.  DROP PROC dbo.GetMyOrder;  
3. GO  
4.   
5. CREATE PROCEDURE dbo.GetMyOrder @PurchaseOrderNumber nvarchar(50)  
6. AS  
7. BEGIN  
8.   
9.  DECLARE @Sql nvarchar(1000)  
10.    
11.  SET @Sql = N'SELECT * FROM Sales.SalesOrderHeader'  
12.  SET @Sql += N'  WHERE PurchaseOrderNumber = @PurchaseOrderNumber'  
13.   
14.  PRINT @Sql  
15.  EXEC sp_executesql @Sql, N'@PurchaseOrderNumber nvarchar(50)', @PurchaseOrderNumber;  
16. END  
17. GO  

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.