The SQLScript Code Analyzer is a tool that is used to scan the code of functions and procedures to search for patterns indicating potential problems in code quality, security, and performance.
In the Web IDE Database Explorer, choose Analyze SQLScript Code from the context menu of a database, container, schema, procedure, or function.
The tool looks for issues in the code relating to the following:
Consistency
Style
Security
Performance
SQLScript Code Analyzer - 12 Checking Rules
| RULE_NAME | CATEGORY |
|---|---|
COMMIT_OR_ROLLBACK_IN_DYNAMIC_SQL | STYLE |
DML_STATEMENTS_IN_LOOPS | PERFORMANCE |
ROW_COUNT_AFTER_DYNAMIC_SQL | BEHAVIOR |
ROW_COUNT_AFTER_SELECT | BEHAVIOR |
SINGLE_SPACE_LITERAL | CONSISTENCY |
UNCHECKED_SQL_INJECTION_SAFETY | SECURITY |
UNNECESSARY_VARIABLE | CONSISTENCY |
UNUSED_VARIABLE_VALUE | CONSISTENCY |
USE_OF_CE_FUNCTIONS | PERFORMANCE |
USE_OF_DYNAMIC_SQL | PERFORMANCE |
USE_OF_SELECT_IN_SCALAR_UDF | PERFORMANCE |
USE_OF_UNASSIGNED_SCALAR_VARIABLE | CONSISTENCY |
As of SAP HANA 2.0 SPS07, there are twelve rules which are defined in system view SQLSCRIPT_ANALYZER_RULES.
UNNECESSARY_VARIABLE: Each variable is tested to identify if it is used by any output parameter of the procedure or if it influences the outcome of the procedure. Relevant statements for the outcome could be DML statements, implicit result sets, or conditions of control statements.
UNUSED_VARIABLE_VALUE: If a value assigned to a variable is not used in any other statement, the assignment can be removed. In case of default assignments in DECLARE statements, the default is never used.
SINGLE_SPACE_LITERAL: This rule searches for string laterals consisting of only one space. If ABAP VARCHAR MODE is used, such string literals are treated as empty strings. In this case, CHAR(32) can be used instead of ' '.
USE_OF_UNASSIGNED_SCALAR_VARIABLE: Detects variables which are used but were never assigned explicitly. Those variables will still have their default value when used, which might be undefined. We recommend that you assign a default value (can be NULL) to be sure that you get the intended value when you read from the variable.
USE_OF_CE_FUNCTIONS : Checks whether Calculation Engine Plan Operators (CE functions) are used. Since they make optimization more difficult and lead to performance penalties, they should be avoided.
DML_STATEMENTS_IN_LOOPS: Detects the following DML statements inside of loops: INSERT, UPDATE, DELETE, UPSERT. Sometimes it is possible to rewrite the loop and use a single DML statement to improve performance instead.
USE_OF_SELECT_IN SCALAR_UDF: Detects if SELECT is used within a scalar UDF which can lower the performance. If table operations are really needed, procedures or Table UDFs should be used instead.
UNCHECKED_SQL_INJECTION_SAFETY: Parameters of string type should always be checked for SQL injection safety if they are used in dynamic SQL. This rule checks if, for any such parameter, the function is_sql_injection_safe was called. For a simple conditional statement like IF is_sql_injection_safe(:var) = 0 THEN..., the control flow in the true branch is checked. The procedure should either end (by returning or by throwing an error) or the unsafe parameter value should be escaped with the functions escape_single_quotes or escape_double_quotes, depending on where the value is used. If the condition is more complex (for example, more than one variable is checked in one condition), a warning will be displayed as it could only be checked if any execution of the dynamic SQL has passed the SQL injection check.
COMMIT_OR_ROLLBACK_IN_DYNAMIC_SQL: Detects dynamic SQL which uses the COMMIT or ROLLBACK statement. Since COMMIT and ROLLBACK can be used directly in SQLScript without the need of dynamic SQL, it is recommended to use them directly.
The SQLScript Code Analyzer is launched in the SAP Web IDE Database Explorer from the context menu of:
Database: To scan all procedures and function in the database (launched from the catalog DB connection type)
Schema: To scan all procedures and function in a specific schema (launched from the catalog DB connection type)
Container: To scan all procedures and function in a specific HDI container (launched from the HDI container connection type)
Procedure: To scan a specific procedure
Function: To scan a specific function
You can download the results of a code analysis to a .CSV file. Use the download button in the results screen.
