Dynamic SQL constructs query text at runtime. Concatenating login fields into that text may seem convenient, but it lets input alter the query's meaning and turns an authentication check into an attack surface. section 14.7
Mistake: concatenating login fields
A login query built from a username and password can be changed with input such as ' OR 1=1--, producing a query that returns a user without a valid password. More destructive payloads can create accounts, delete data, drop tables, or support lateral movement. section 14.7
Use these safer practices:
- Prefer a stored procedure that accepts username and password variables instead of dynamically building the query. section 14.7
- Enforce the stored-procedure standard at the application boundary; it protects against poor validation without rejecting legitimate names. section 14.7
Listing 14.19 Creating a login stored procedure
CREATE PROCEDURE dbo.Login
@UserName NVARCHAR(128)
, @Password NVARCHAR(128)
AS
BEGIN
SELECT *
FROM dbo.Users
WHERE UserName = @UserName
AND UserPassword = @Password ;
END
Inspect the parameterized login procedure and compare it with dynamically concatenated login SQL. section 14.7
