YDB supports and recommends the use of so-called parameterized queries. In such queries, the data is transmitted separately from the request body itself, and in the SQL query, special parameters are used to indicate the location of the data.
Request with data in the request body:
SELECT sa.title AS season_title, sr.title AS series_title FROM seasons AS sa INNER JOIN series AS sr ON sa.series_id = sr.series_id WHERE sa.series_id = 15 AND sa.season_id = 3
The corresponding parameterized query:
DECLARE $seriesId AS Uint64; DECLARE $seasonId AS Uint64; SELECT sa.title AS season_title, sr.title AS series_title FROM seasons AS sa INNER JOIN series AS sr ON sa.series_id = sr.series_id WHERE sa.series_id = $seriesId AND sa.season_id = $seasonId
Parameterized queries are written in the form of a template in which certain types of names are replaced with specific parameters each time the query is executed. Tokens starting with the sign
$ such as
$seasonId in the query above are used to denote parameters.
Parameterized queries provide the following advantages:
- For repeated requests, the database server has the ability to cache the query plan for parameterized requests. This radically reduces CPU consumption and increases system throughput.
- The use of parameterized queries saves from vulnerabilities like SQL Injection.
YDB SDK automatically caches parameterized query plans by default, the setting
KeepInCache = true is usually used for this.