SQL Scan rules
Contrast Scan supports these rules for SQL.
Severity | Contrast rule | Engine rule ID | Description |
|---|---|---|---|
Critical | Detect Unaware Cross Joins | OPT.SQL.SQL_PB.DetectUnawareCrossJoins | DetectUnawareCrossJoins: Do not make "unnoticed" cartesian products in queries |
Critical | Fetch And Declare Same Fields | OPT.SQL.SQL_PB.FetchAndDeclareSameFields | FetchAndDeclareSameFields: The number of fields to retrieve specified in the DECLARE CURSOR statement must be the same as the number of fields specified in the FETCH statement |
Critical | Avoid Correlated Sub Selects | OPT.SQL.SQL_PERFORMANCE.AvoidCorrelatedSubSelects | AvoidCorrelatedSubSelects: Avoid nested SELECTs that use columns defined in outer SELECTs |
Critical | Cursor For Update Where Current | OPT.SQL.SQL_PERFORMANCE.CursorForUpdateWhereCurrent | CursorForUpdateWhereCurrent: If a CURSOR is declared FOR UPDATE, DELETE and UPDATE must be used with the WHERE CURRENT specification |
Critical | Dont Select Known Fields | OPT.SQL.SQL_PERFORMANCE.DontSelectKnownFields | DontSelectKnownFields: SELECT queries never should get fields used in the WHERE specification with {} |
High | Avoid Scroll Cursor | OPT.SQL.AvoidScrollCursor | AvoidScrollCursor: Do not use SCROLL CURSOR clause |
High | Check Updt Dlt Rowset | OPT.SQL.CheckUpdtDltRowset | CheckUpdtDltRowset: Do not use the FOR ROW n OF ROWSET clause in UPDATE or DELETE statements with MULTIROW option. |
High | Check Insert Not Atomic | OPT.SQL.CheckInsertNotAtomic | CheckInsertNotAtomic: Do not use the NOT ATOMIC CONTINUE ON SQLEXCEPTION in SELECT statements with MULTIROW option |
High | Avoid Dynamic SQL | OPT.SQL.AvoidDynamicSql | AvoidDynamicSql: Do not use dynamic SQL |
High | Avoid Lock Table | OPT.SQL.AvoidLockTable | AvoidLockTable: Do not use LOCK TABLE statement |
High | Avoid Numeric References In By Clauses | OPT.SQL.SQL_MAINTAINABILITY.AvoidNumericReferencesInByClauses | AvoidNumericReferencesInByClauses: Do not refer to column names with number indexes in * BY clauses |
High | Use The As Keyword | OPT.SQL.SQL_MAINTAINABILITY.UseTheAsKeyword | UseTheAsKeyword: Use AS keyword when stablishing an alias to tables |
High | Avoid Declared Unopened Cursors | OPT.SQL.SQL_PERFORMANCE.AvoidDeclaredUnopenedCursors | AvoidDeclaredUnopenedCursors: If a CURSOR is declared, it must be opened |
High | Avoid For Update Wait Forever | OPT.SQL.SQL_PERFORMANCE.AvoidForUpdateWaitForever | AvoidForUpdateWaitForever: No use FOR UPDATE without specifying NOWAIT or WAIT n |
High | Avoid Opened Unclosed Cursors | OPT.SQL.SQL_PERFORMANCE.AvoidOpenedUnclosedCursors | AvoidOpenedUnclosedCursors: If a CURSOR is opened, it must be closed |
High | Avoid Opened Unused Cursors | OPT.SQL.SQL_PERFORMANCE.AvoidOpenedUnusedCursors | AvoidOpenedUnusedCursors: If a CURSOR is opened, it must be used |
High | Avoid Union | OPT.SQL.SQL_PERFORMANCE.AvoidUnion | AvoidUnion: Avoid selects with UNION |
High | No Current Clause | OPT.SQL.SQL_PERFORMANCE.NoCurrentClause | NoCurrentClause: SQL queries with CURRENT clause are heavy-weighted and must be used only when necessary |
Info | Avoid Concat Operator | OPT.SQL.AvoidConcatOperator | AvoidConcatOperator: Do not use concatenation operator |
Info | Avoid Current Server | OPT.SQL.AvoidCurrentServer | AvoidCurrentServer: Do not use CURRENT SERVER |
Info | Avoid Numeric Cursor | OPT.SQL.AvoidNumericCursor | AvoidNumericCursor: Incorrect nomenclature for cursor name |
Info | Avoid Current Time | OPT.SQL.AvoidCurrentTime | AvoidCurrentTime: Do not use CURRENT TIME |
Info | Avoid Current Date | OPT.SQL.AvoidCurrentDate | AvoidCurrentDate: Do not use CURRENT DATE |
Info | Avoid Non Declared Cursor | OPT.SQL.AvoidNonDeclaredCursor | AvoidNonDeclaredCursor: Use of cursor not previously declared |
Info | Avoid Current Timestamp | OPT.SQL.AvoidCurrentTimestamp | AvoidCurrentTimestamp: Do not use CURRENT TIMESTAMP, CURRENT DATE or CURRENT TIME |
Info | Check Nulls In Select Count | OPT.SQL.CheckNullsInSelectCount | CheckNullsInSelectCount: Do not use null indicator in queries with COUNT function |
Info | Use Null Indicator | OPT.SQL.UseNullIndicator | UseNullIndicator: Use null indicator in queries with MAX, MIN, AVG and SUM functions |
Info | Avoid Non Qualified Joins | OPT.SQL.SQL_PORTABILITY.AvoidNonQualifiedJoins | AvoidNonQualifiedJoins: Make the type of join explicit |
Low | Do Not Use Negation In Where | OPT.SQL.DoNotUseNegationInWhere | DoNotUseNegationInWhere: Do not use negation operator |
Low | Avoid Whenever | OPT.SQL.AvoidWhenever | AvoidWhenever: Do not use WHENEVER clause |
Low | Avoid Host Operations | OPT.SQL.AvoidHostOperations | AvoidHostOperations: Do not perform arithmetic operations in the WHERE clause |
Low | Avoid Having | OPT.SQL.AvoidHaving | AvoidHaving: Do not use HAVING clause |
Low | Check Simple Condition | OPT.SQL.CheckSimpleCondition | CheckSimpleCondition: Simplify WHERE clause conditions |
Low | Avoid Qualified Tables In Queries | OPT.SQL.SQL_MAINTAINABILITY.AvoidQualifiedTablesInQueries | AvoidQualifiedTablesInQueries: Table names should not be qualified in queries |
Low | One SQL Statement Per Line | OPT.SQL.SQL_MAINTAINABILITY.OneSQLStatementPerLine | OneSQLStatementPerLine: Only write a SQL statement per line |
Low | Qualified Tables In Queries | OPT.SQL.SQL_MAINTAINABILITY.QualifiedTablesInQueries | QualifiedTablesInQueries: Every table referenced in the query must be qualified |
Low | Avoid Insert Without Fields Specification | OPT.SQL.SQL_PB.AvoidInsertWithoutFieldsSpecification | AvoidInsertWithoutFieldsSpecification: Every INSERT statement must include the field specification (i.e : INSERT INTO table(column1,column2) VALUES (value1,value2)) |
Low | Avoid Group By | OPT.SQL.SQL_PERFORMANCE.AvoidGroupBy | AvoidGroupBy: Avoid to use GROUP BY clause |
Medium | Avoid Case In Select | OPT.SQL.AvoidCaseInSelect | AvoidCaseInSelect: Do not use CASE clause |
Medium | Check Number Tables | OPT.SQL.CheckNumberTables | CheckNumberTables: Do not code queries with more than one table |
Medium | Fully Qualified Names In Columns | OPT.SQL.SQL_MAINTAINABILITY.FullyQualifiedNamesInColumns | FullyQualifiedNamesInColumns: Use qualified names when referring to column names |
Medium | Avoid Natural Joins | OPT.SQL.SQL_PB.AvoidNaturalJoins | AvoidNaturalJoins: NATURAL JOINs are buggy and unmaintenable |
Medium | Avoid Nested Selects | OPT.SQL.SQL_PERFORMANCE.AvoidNestedSelects | AvoidNestedSelects: Avoid nested selects |
Medium | Avoid Queries On Many Tables | OPT.SQL.SQL_PERFORMANCE.AvoidQueriesOnManyTables | AvoidQueriesOnManyTables: Avoid JOIN queries referencing too many tables |
Medium | Avoid Select Asterisk | OPT.SQL.SQL_PERFORMANCE.AvoidSelectAsterisk | AvoidSelectAsterisk: Do not use SELECT * |
Medium | Avoid Too Many Joins | OPT.SQL.SQL_PERFORMANCE.AvoidTooManyJoins | AvoidTooManyJoins: Avoid queries with too many JOINs |
Medium | Detect Implicit Joins | OPT.SQL.SQL_PERFORMANCE.DetectImplicitJoins | DetectImplicitJoins: Never use implicit JOINS |
Medium | Prefer On Over Using | OPT.SQL.SQL_PORTABILITY.PreferOnOverUsing | PreferOnOverUsing: Replace Using clause for its equivalent On counterpart |