Using EDB SPL Check
Suggest editsEDB SPL Check helps you to find errors in an EDB stored procedure that a CREATE PROCEDURE/FUNCTION
can miss. The following are some methods to check your code with EDB SPL Check.
Checking triggers
To check any trigger, enter a relation that's used with the trigger function. For example:
When there's no specified relation for the trigger, the following is returned:
When the trigger is checked successfully with a specified relation, the following is returned:
For triggers with transitive tables, set the oldtable
and newtable
parameters:
Validating compound triggers
Another way to verify a trigger function is to use spl_check_trigger()
by providing the trigger name and, optionally, the relation name. This method is useful for redwood-style triggers and compound triggers where trigger functions are created internally. For example:
Validating packages
A package can have multiple functions/procedures. To validate all of your functions/procedures at once, you can use spl_check_package()
. Similarly, use spl_check_package_tb()
to view the output in tabular format.
You can also validate individual package functions/procedures using spl_check_function()
. For example:
Validating object types
Object types can have one or more member functions. To validate all the functions at once, use spl_check_objecttype()
. Similarly, use spl_check_objecttype_tb()
to view the output in tabular format.
You can also validate individual object type member functions using spl_check_function()
. For example:
Setting in-comment options
EDB SPL Check allows persistent-setting, written in-comments. These options are taken from a function's source code before checking. The syntax is:
The settings from comment options have top priority, but generally they can be disabled by setting use_incomment_options
to false
. For example:
Checking all your code
Use the spl_check_function()
to check all of your functions/procedures and to check all your triggers.
To check all nontrigger EDB SPL Check functions:
To check all trigger EDB SPL Check functions:
To check all EDB SPL Check functions, including functions or trigger functions with defined triggers:
Limitations
EDB SPL Check finds almost all errors on static code. However, when using PL/pgSQL's dynamic features like dynamic SQL or the record data type, false positives are possible. In these cases, we recommend that you rewrite the affected function or disable EDB SPL Check for the function. For example:
Note
When passive mode is enabled, using EDB SPL Check adds a small overhead. We recommend that you use the setting only in development or preproduction environments.
Dynamic SQL
This module doesn't check queries that are assembled in runtime because you can't identify the results of dynamic queries. Therefore, EDB SPL Check can't set the correct type to record variables and can't check a dependent SQL or
expressions.
When the type of a record's variable is unknown, you can explicitly assign it with pragma type
. For example:
Warning
You can't use the SQL injection check for a security audit. This tool detects only some SQL injection vulnerabilities, so it might not detect some issues. It can also raise false alarms.
Refcursors
You can't use EDB SPL Check to detect the structure of referenced cursors. A reference on a cursor in EDB SPL Check is implemented as the name of a global cursor. During the check, the name is usually unknown, and the global cursor doesn't exist. This issue is significant when performing any static analysis. EDB SPL Check can't set the correct type for the record variable and can't check the dependent SQL statements or expressions. The solution is the same for dynamic SQL.
You can use either of these solutions:
- Don't use the the record variable as a target when you use a
refcursor
type. - Disable
spl_check
for the affected functions.
For example:
In this example, don't use a record type. Instead use a known rowtype
:
Temporary tables
EDB SPL Check can't verify queries over temporary tables that are created in the edbspl
function runtime.
As a solution, you can either:
- Create a fake temporary table.
- Disable EDB SQL Check for this function.
Temporary tables are stored in your own schema with higher priority than persistent tables. So, you can perform this workaround safely:
Could this page be better? Report a problem or suggest an addition!