DataGrip 2023.3 Help

User Parameters

For more information about working with user parameters, refer to the corresponding chapter of Run queries topic.

Execute a parametrized statement

User parameters

Item

Description

Enable in query consoles and SQL files

Apply parameter patterns to SQL in SQL files and database consoles. You can limit the usage scope at the level of individual patterns.

If this checkbox is cleared, the patterns are not used in SQL files and consoles irrespective of the usage scope that is specified for individual patterns.

Enable in string literals with SQL injection

Apply parameter patterns to string literals injected with SQL. If necessary, you can limit the usage scope at the level of individual patterns.

If this checkbox is cleared, the patterns are not used in string literals irrespective of the usage scope that is specified for individual patterns.

Substitute inside SQL strings

Apply parameter patterns to string literals in the SQL code.

For example, consider the following code.

SELECT ${column_name} FROM actor WHERE actor_id='${actor_id}'

If the checkbox is cleared, DataGrip will find only the column_name parameter in it. The actor_id parameter is treated as a string.

But if you select the Substitute inside SQL strings option, the actor_id parameter is treated as a user parameter.

The Substitute inside SQL strings setting disabled
The Substitute inside SQL strings setting enabled

Parameter patterns

List of parameter patterns and their usage scopes.

The patterns are specified using regular expressions. Values that start with a colon (:) or located in parentheses are treated as parameter names. The patterns available initially have the following meanings:

  • \?(\d+) - a question mark followed by one or more digits, for example, ?69 in which case 69 would be the parameter name.

  • %\w+ - % followed by one or more word characters, for example, %xyz.

  • \$\{([^$\{\}]*)\} - $, then {, then any character except $, { or } zero or more times, then }, for example, ${}, ${value}.

  • \$\(([^\)]+)\) - $, then (, then any character except ) one or more times, then ), for example, $(x).

  • \$(\w+)\$ - $, then one or more word characters, then $ again, for example, $x1$.

  • \#(\w+)\# - #, then one or more word characters, then # again, for example, #field_3#.

  • (?<=\W|\A)\$(\d+|[a-zA-Z_](?:\w|\.)*)\$?(?=\W|\z) - various sequences that start with $ with additional conditions for symbols that precede and follow those sequences. For example, $ACCESS if preceded by a space matches the pattern but V$ACCESS does not. In Oracle, there are system views whose names start with V$ that should be filtered out.

  • (?<=\W|\A)#(\d+|[a-zA-Z_](?:\w|\.)*)#?(?=\W|\z) - similar to the previous pattern but for the sequences that start with #.

Use the Add button Alt+Insert, the Remove button Alt+Delete, the Previous Occurrence button Alt+ArrowUp and the Next Occurrence button Alt+ArrowDown to add, delete and reorder the patterns.

To edit a pattern or its usage scope, click the pattern and use the following controls:

  • In scripts: clear this checkbox if the pattern must not be used in SQL files and database consoles.

  • In literals: clear this checkbox if the pattern must not be used in string literals injected with SQL.

  • All languages: click the link and clear language checkboxes where you do not want to use the pattern.

Last modified: 11 December 2023