When you execute a batch of Transact-SQL statements, conditional and branching statements such as IF, WHILE, and GOTO statements are ignored. The rest of the statements are executed only as necessary to obtain the metadata of the output and to send the metadata back to the client application. If the batch of statements contains the SET FMTONLY OFF option statement, the statement is executed, and the
FMTONLY option is set to OFF. Therefore, the following pattern of Transact-SQL statements contains a security threat:
IF <trusted user>
BEGIN
-- Transact-SQL code that is inserted here is not validated for injection purposes, because the code is executed by
-- only a trusted user.
END
ELSE
BEGIN
-- Transact-SQL code that is inserted here is validated for injection purposes.
END
In the following Transact-SQL statements, client drivers such as the OLEDB, ODBC, and ADO.NET drivers frequently execute a batch of statements when the
FMTONLY option is set to ON. The drivers do this to discover metadata before the driver continues regular statement execution. Therefore, a non-trusted user can insert a SET FMTONLY OFF statement into the first part of the IF/ELSE construct. Then, the user can execute an inserted statement in that block. The following code example illustrates this scenario.
IF <trusted user>
BEGIN
....
SET FMTONLY OFF
-- Any statement here may potentially do harm.
....
END
ELSE
BEGIN
-- Transact-SQL code that is inserted here is validated for injection purposes.
END
When the driver executes these statements together with the
FMTONLY option set to ON, the trusted user check is ignored, and the flow control reaches the SET FMTONLY OFF statement. This action switches the Transact-SQL code execution into regular mode, and this action enables the rest of the inserted code to execute.
Because of this threat, make sure that you always use SQL injection mitigation, even in code paths that usually can be executed only by trusted users.
