Transact-SQL Scan rules
Contrast Scan supports these rules for Transact-SQL.
Severity | Contrast rule | Engine rule ID | Description |
|---|---|---|---|
Critical | Command Injection | OPT.TRANSACTSQL.SEC.CommandInjection | CommandInjection: Improper Neutralization of Special Elements used in an OS Command ('OS Command Injection') |
Critical | Sleep Injection | OPT.TRANSACTSQL.SEC.SleepInjection | SleepInjection: Denial of Service by externally controlled sleep time |
Critical | SQL Injection | OPT.TRANSACTSQL.SEC.SqlInjection | SqlInjection: Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection') |
Critical | Avoid No Lock | OPT.TRANSACTSQL.AvoidNoLock | AvoidNoLock: Avoid NOLOCK table hint |
Critical | Use Proper Transaction Isolation Level | OPT.TRANSACTSQL.UseProperTransactionIsolationLevel | UseProperTransactionIsolationLevel: Use proper Transaction Isolation Level |
Critical | Too Broad Grant | OPT.TRANSACTSQL.SEC.TooBroadGrant | TooBroadGrant: Too broad privileges granted. |
Critical | Weak Cryptographic Hash | OPT.TRANSACTSQL.SEC.WeakCryptographicHash | WeakCryptographicHash: Weak cryptographic hashes cannot guarantee data integrity |
Critical | Weak Symmetric Encryption Algorithm | OPT.TRANSACTSQL.SEC.WeakSymmetricEncryptionAlgorithm | WeakSymmetricEncryptionAlgorithm: Weak symmetric encryption algorithm. |
High | Avoid Email Hardcoded | OPT.TRANSACTSQL.AvoidEmailHardcoded | AvoidEmailHardcoded: Avoid hardcoded or in-comment emails in source code |
High | Avoid IP Hardcoded | OPT.TRANSACTSQL.AvoidIPHardcoded | AvoidIPHardcoded: Do not write IP address in source code |
High | Avoid Cross Joins | OPT.TRANSACTSQL.AvoidCrossJoins | AvoidCrossJoins: Avoid explicit or unwanted CROSS JOINS (cartesian products) |
High | Avoid Delete Update Without Search Condition | OPT.TRANSACTSQL.AvoidDeleteUpdateWithoutSearchCondition | AvoidDeleteUpdateWithoutSearchCondition: Do not Use UPDATE / DELETE without search condition |
High | Close Deallocate Cursors | OPT.TRANSACTSQL.CloseDeallocateCursors | CloseDeallocateCursors: Close/deallocate cursors and deallocate cursor variables in the same T-SQL scope where they are declared |
High | Dead Variable Or Parameter | OPT.TRANSACTSQL.DeadVariableOrParameter | DeadVariableOrParameter: Looks for unused local variables and procedure/function parameter.s |
High | Use Exists Instead Of In | OPT.TRANSACTSQL.UseExistsInsteadOfIn | UseExistsInsteadOfIn: Avoid IN(subquery) if not necessary |
High | Use Order By With Top | OPT.TRANSACTSQL.UseOrderByWithTop | UseOrderByWithTop: Use ORDER BY or GROUP BY clause in queries with TOP clause |
High | Encrypt Information | OPT.TRANSACTSQL.EncryptInformation | EncryptInformation: Avoid including sensitive information in column names |
High | Insecure Randomness | OPT.TRANSACTSQL.SEC.InsecureRandomness | InsecureRandomness: Standard pseudo-random number generators cannot withstand cryptographic attacks. |
Info | Avoid Comparing With Null Constant | OPT.TRANSACTSQL.AvoidComparingWithNullConstant | AvoidComparingWithNullConstant: Use IS/IS NOT NULL instead of comparing against NULL constant |
Info | Avoid Distinct | OPT.TRANSACTSQL.AvoidDistinct | AvoidDistinct: Avoid DISTINCT |
Info | Avoid Large Composite Primary Keys | OPT.TRANSACTSQL.AvoidLargeCompositePrimaryKeys | AvoidLargeCompositePrimaryKeys: Limit columns in composite PRIMARY KEY |
Info | Avoid Large Text Binary Objects | OPT.TRANSACTSQL.AvoidLargeTextBinaryObjects | AvoidLargeTextBinaryObjects: Do not store binary or image files (Binary Large Objects or BLOBs) inside the database |
Info | Avoid Non Ansi Outer Join Syntax | OPT.TRANSACTSQL.AvoidNonAnsiOuterJoinSyntax | AvoidNonAnsiOuterJoinSyntax: Avoid non-ANSI outer join syntax * [] {} |
Info | Avoid Nullable Char | OPT.TRANSACTSQL.AvoidNullableChar | AvoidNullableChar: Use the CHAR/NCHAR data type for a column only when the column is non-nullable |
Info | Avoid Text Datatypes | OPT.TRANSACTSQL.AvoidTextDatatypes | AvoidTextDatatypes: Avoid TEXT, NTEXT or IMAGE datatypes for storing large textual/binary data |
Info | Comment Tsql Code | OPT.TRANSACTSQL.CommentTsqlCode | CommentTsqlCode: Comment specific T-SQL elements |
Info | Prefix Column Names With Table Name | OPT.TRANSACTSQL.PrefixColumnNamesWithTableName | PrefixColumnNamesWithTableName: Prefix column names with table name/alias in SQL statements referencing multiple tables |
Info | Single Exit Point In Procedures | OPT.TRANSACTSQL.SingleExitPointInProcedures | SingleExitPointInProcedures: Code stored procedures, functions and triggers with a single exit point (RETURN) |
Info | Use Ansi Join | OPT.TRANSACTSQL.UseAnsiJoin | UseAnsiJoin: Use ANSI-Standard Join clauses instead of the old style joins |
Info | Use Set No Count On In Procedures | OPT.TRANSACTSQL.UseSetNoCountOnInProcedures | UseSetNoCountOnInProcedures: Use SET NOCOUNT ON at the beginning of SQL batches, stored procedures and triggers |
Info | Use Standard Names | OPT.TRANSACTSQL.UseStandardNames | UseStandardNames: Follow naming conventions for names of database objects and T-SQL entities |
Low | Avoid Cursors | OPT.TRANSACTSQL.AvoidCursors | AvoidCursors: Try to avoid server side cursors as much as possible |
Low | Avoid Expressions That Prevent Index | OPT.TRANSACTSQL.AvoidExpressionsThatPreventIndex | AvoidExpressionsThatPreventIndex: Avoid expressions with columns in a query predicate that disable index search |
Low | Avoid Goto | OPT.TRANSACTSQL.AvoidGoto | AvoidGoto: Do not use GOTO |
Low | Avoid Group By Without Aggregation Functions | OPT.TRANSACTSQL.AvoidGroupByWithoutAggregationFunctions | AvoidGroupByWithoutAggregationFunctions: Do not use the GROUP BY clause without an aggregate function |
Low | Avoid Recompile | OPT.TRANSACTSQL.AvoidRecompile | AvoidRecompile: Avoid operations that force a recompilation of an SQL batch or stored procedure / trigger |
Low | Avoid Reserved Words In Identifiers | OPT.TRANSACTSQL.AvoidReservedWordsInIdentifiers | AvoidReservedWordsInIdentifiers: Do not use reserved words in identifiers |
Low | Avoid Select Count From Table | OPT.TRANSACTSQL.AvoidSelectCountFromTable | AvoidSelectCountFromTable: Avoid SELECT COUNT to fetch number of rows in table |
Low | Avoid System Prefixes | OPT.TRANSACTSQL.AvoidSystemPrefixes | AvoidSystemPrefixes: Do not use sp_ or fn_ as prefix for stored procedures and functions |
Low | Explicit Column Names In Insert | OPT.TRANSACTSQL.ExplicitColumnNamesInInsert | ExplicitColumnNamesInInsert: Identify Column Names in INSERT statements |
Low | Length With Varchar Types | OPT.TRANSACTSQL.LengthWithVarcharTypes | LengthWithVarcharTypes: Specify explicit length with character and binary data types |
Low | Precision Scale With Decimal Numeric | OPT.TRANSACTSQL.PrecisionScaleWithDecimalNumeric | PrecisionScaleWithDecimalNumeric: Specify explicit precision and scale with DECIMAL and NUMERIC data types |
Medium | Avoid Dynamic SQL | OPT.TRANSACTSQL.AvoidDynamicSql | AvoidDynamicSql: Avoid dynamic SQL statements as much as possible |
Medium | Avoid Deprecated Features | OPT.TRANSACTSQL.AvoidDeprecatedFeatures | AvoidDeprecatedFeatures: Avoid deprecated features |
Medium | Avoid Exact Or Overlapping Indexes | OPT.TRANSACTSQL.AvoidExactOrOverlappingIndexes | AvoidExactOrOverlappingIndexes: Avoid indexes with same or overlapping columns |
Medium | Avoid Like Patterns Table Scan | OPT.TRANSACTSQL.AvoidLikePatternsTableScan | AvoidLikePatternsTableScan: Avoid patterns in LIKE that disable index usage and forces a table/index scan |
Medium | Avoid Negative Operator | OPT.TRANSACTSQL.AvoidNegativeOperator | AvoidNegativeOperator: Avoid using <> or ! {} |
Medium | Avoid Select Asterisk | OPT.TRANSACTSQL.AvoidSelectAsterisk | AvoidSelectAsterisk: Avoid * in SELECT |
Medium | Avoid Too Many Joins | OPT.TRANSACTSQL.AvoidTooManyJoins | AvoidTooManyJoins: Avoid queries with too many joined tables |
Medium | Avoid Trigger Return Data | OPT.TRANSACTSQL.AvoidTriggerReturnData | AvoidTriggerReturnData: Avoid returning results in triggers |
Medium | Check Error After Data Manipulation | OPT.TRANSACTSQL.CheckErrorAfterDataManipulation | CheckErrorAfterDataManipulation: Check result from INSERT / UPDATE / DELETE using @@ERROR or @@ROWCOUNT or in TRY ... CATCH |
Medium | Prefer Union All Over Union | OPT.TRANSACTSQL.PreferUnionAllOverUnion | PreferUnionAllOverUnion: Prefer UNION ALL over UNION |
Medium | Forbidden Call | OPT.TRANSACTSQL.SEC.ForbiddenCall | ForbiddenCall: Dangerous procedure / function called. |
Medium | User Controlled SQL Primary Key | OPT.TRANSACTSQL.SEC.UserControlledSQLPrimaryKey | UserControlledSQLPrimaryKey: Avoid using an user controlled Primary Key into a query |