What is a parameterized query in SQL

Parameterized queries

This section covers a topic that is missing from most books on SQL: parameterized queries and bind parameters.

Bind parameters - also called dynamic parameters or bind variables - are an alternative way of transferring data to databases. Instead of writing the data directly into the SQL statement, placeholders such as, or are used. The actual values ​​are made available to the database via a separate API call.

In the case of manual queries, there is nothing wrong with writing the values ​​directly into the SQL statement. With programs, however, there are two good reasons for using bind parameters:

security

Bind variables are the best way to prevent SQL injections.

performance

Databases with an execution plan cache such as SQL Server or the Oracle database can reuse execution plans if an SQL statement is executed multiple times. This avoids the hassle of creating an execution plan. However, this mechanism only works if exactly the same SQL statement is executed repeatedly. If the SQL statement contains other values, the database creates the execution plan again.

When using bind parameters, the actual values ​​are not included, only placeholders in the SQL statement. The values ​​do not change the SQL string. This makes it the same statement for the database, even if it is executed with different values.

But of course there are also exceptions if the amount of data involved, as in the following example, is heavily dependent on the actual values.

In the case of a small branch office, index access provides the best performance. However, when looking at a larger branch, it may be better to read the whole table:

In this case, the histogram for the column serves its purpose. The optimizer uses it to determine the frequency of the values ​​that are specified directly in the SQL statement. This gives it different row estimates for the two queries. As a result, the cost values ​​of the two plan variants are different. In the end, the optimizer selects the execution plan with the lowest costs. For the smaller branch, this is the execution plan with index access.

The cost value of the operation is very much dependent on the number of lines. Since the larger branch is around ten times as large, the cost value increases by this factor. This increases the total costs even beyond the costs of a full table scan. If the optimizer finally selects the execution plan with the lowest costs, this is the execution plan without index access for the large branch.

When using bind parameters, however, the optimizer does not have any specific values ​​whose frequency it could determine with the histogram. Therefore, the optimizer always arrives at the same line estimate and the same cost value. In the end, he always selects the same execution plan.

tip

Histograms are particularly useful when the distribution is uneven.

If the distribution is even, it is often sufficient to divide the number of different values ​​by the total number of lines. This method is also used with bind parameters.

If you compare the optimizer with a compiler, bind parameters behave like program variables, while values ​​that are directly in the SQL statement correspond to constants. Just as a compiler can resolve constant expressions during translation, the optimizer can take concrete values ​​into account when creating the execution plan. In contrast to this, the values ​​of the bind parameters for the optimizer are - in simple terms - not visible.

From this point of view, it seems a bit contradictory that bind parameters can improve performance. Because with concrete values, the optimizer can always select the best execution plan. The only question is: at what price? Because the creation of all execution plan variants can also be a considerable effort. An effort that only pays off if the end result is not always the same.

tip

Not using bind parameters is like recompiling a program every time.

The database faces a dilemma when it comes to whether the optimizer should create a general or a customized execution plan. Because in order to know whether the optimization effort is paying off, the database must first create several execution plans. But that's exactly what you want to avoid with an execution plan cache. The database manufacturers try to solve this dilemma with heuristic methods, but have only limited success.

As a developer, however, you can help the database with this dilemma by using bind parameters in a targeted manner. This means that you should always use bind parameters, except for values ​​that affect the execution plan should.

Unevenly distributed status codes such as “done” and “not yet done” are a good example. The number of entries that have not yet been completed is usually low compared to the number that have been completed. In this case, index access only makes sense if you are looking for entries that have not yet been completed. Another example is partitions - that is when tables and / or indexes are divided into several storage locations. The values ​​can then influence which partitions have to be searched for a query. Even with -conditions, bind parameters can lead to suboptimal execution plans, as we will see in the next section.

tip

In practice, there are few cases in which the values ​​affect the execution plan.

In case of doubt, you should therefore always use bind parameters - if only to avoid SQL injections.

The following examples show the use of bind parameters in some popular programming languages.

C #

Without bind parameters:

With bind parameter:

See: Documentation for the class.

Java

Without bind parameters:

With bind parameter:

See: Documentation for the class.

Pearl

Without bind parameters:

With bind parameters:

Please refer: "Placeholders and Bind Values“In the DBI documentation.

PHP

The following example shows a MySQL access without bind parameters:

With bind parameter:

See: Documentation on and "Prepared statements and stored procedures" in the PDO documentation.

Ruby

Without bind parameters:

With bind parameter:

See: "Quoting, Placeholders, and Parameter Binding" in the Ruby DBI Tutorial

In the SQL standard, only the question mark () is provided as a placeholder. If you use several placeholders, they only differ in their position in the SQL statement - first, second, third, ... and so on. This can be very inconvenient when working with code, as the numbering changes when you add or remove placeholders. Therefore, some databases offer proprietary solutions for named placeholders at. For example with a spider monkey () or a colon ().

Notice

Bind parameters can only be used for values use.

This means that you cannot replace tables or column names with bind parameters. Therefore, neither of the following two bind parameters will work:

If you need to change the structure of an SQL statement at runtime, you have to use dynamic SQL.