DataGrip 2020.1 Help

User Parameters

Generally, only the question mark ? is treated as a parameter in SQL statements. On this page, you can specify which other characters and their sequences should be treated as parameters, and in which places.

The patterns for SQL parameters are specified by means of regular expressions.

Executing parameterized statements

If you have parameters in your statement, you must specify the values of the parameters before you execute the statement.

  • To execute a parameterized statement, click the Execute button the Execute button on the toolbar and enter values in the second column. Alternatively, to open the Parameters dialog, click the View Parameters button the View Parameters button.

    Execute a parametrized statement

Configure settings for user parameters

  • Open settings by pressing Ctrl+Alt+S and navigate to Database | User Parameters. See description of user parameters in the following table.

    Configure settings for user parameters

User parameters

ItemDescription
Enable in console and SQL files

If the checkbox is selected, the parameter patterns are applied to SQL (in SQL files and database consoles). The usage scope, if necessary, may be limited at the level of individual patterns.

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

Enable in string literals with SQL injection

If the checkbox is selected, the parameter patterns are applied 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 which usage scope is specified for individual patterns.

Parameter patterns

The table shows the parameter patterns and their usage scopes.

The patterns are specified using regular expressions. Values 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+) - a colon followed by one or more word characters, for example, :x, :value, :parameter_1.

  • %\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+Up and the Next Occurrence button Alt+Down 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: 23 June 2020