Although they represent the same thing, parameters and placeholders differ in the way they are presented in a statement .
Parameters are nameable, allowing them to be easily identified across all the terms of the SQL pseudo-language. Because it is a simple string, it requires something that allows them to be identified during the DBMS interpretation. This form of identification is usually the colon character.
Placeholders , however, are not nameable. They just reserve a place for real values. These are usually used in the middle of the statement .
Parameters, or named placeholders , has the advantage of interacting with dynamic arguments once a key / value association is made, ie the parameters found in the statement in> are searched in the list of linked parameters and a simple replacement is done.
Placeholders , however, are numerically indexed and are arranged in the statement by their positions and therefore require a little more attention, otherwise you may end up accidentally linking ( binding ) an integer to a column in the database that represents a float and, at best, receive an incorrect result.
Before you know why the colon is being used in a query, you have to understand what prepared statements are and how they differ from a direct execution.
Let's look at the typical workflow when using an prepared statement :
A statement template is created by the Application and sent to the DBMS. Of the values assigned in the query, some can be omitted. These are called the parameters, , or bind variables .
INSERT INTO PRODUCT (name, price) VALUES (:name, :price)
Or
INSERT INTO PRODUCT (name, price) VALUES (?, ?)
The DBMS analyzes, compiles, and executes a series of optimizations in the declaration model and stores the result without executing it .
Later, the Application provides (or binds ) values to the parameters and the DBMS executes the declaration possibly returning some result. The Application can execute the same statement as many times as it needs, with as many different arguments as it can or needs to provide.
Why use?
If we compare with the direct execution of statements, prepared statements have two main advantages:
-
The cost in terms of processing to compile and optimize the declaration is incurred only once, even if it is executed multiple times. However, it is worth noting that not all optimization techniques have the best optimization depending on the type of argument received, as the best strategy today may not serve in the future, near or not, given the possible changes in structure and index of a table.
- Prepared statement are resilient to SQL Injection because the values used are later transmitted by a different protocol, which does not require your data to be properly escaped.
>
There are those who say that prepared statements are not as advantageous for once-performed queries given all the extra procedures and that this may be a performance penalty for the Application. I disagree as this depends on N factors, from hardware scalability to how well the application was programmed.