Why is ':' used in queries?

Question

I was looking at a php script and noticed that every time SQL query was written, this "operator".

A part of the code

$sql = "INSERT INTO categorias (nome) VALUES(:nome)";
$stmt = DB::prepare($sql);
$stmt->bindParam("nome", $data->nome);

So, what’s his reason for using this "operator"?

Answer 1

This syntax is used by PDO to attach parameters to prepared statements through function bindParam().

It is very useful to implement security when using information coming from forms, or even by the dynamicity of a query.

Example:

<?php
/* Execute a prepared statement by binding PHP variables */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');
$sth->bindParam(':calories', $calories, PDO::PARAM_INT);
$sth->bindParam(':colour', $colour, PDO::PARAM_STR, 12);
$sth->execute();
?>

Source: http://php.net/manual/en/pdostatement.bindparam.php

Answer 2

Friend, you didn’t show all your code but I believe this =: is not an "operator".

The correct interpretation would be = and :id, where the :id is related to the name parameter id which must be passed at the time of execution.

More information here.

Answer 3

Although they represent the same thing, parameters and placeholders are distinguished in the way they are presented in a statement.

Parameters are nameable, allowing them to be easily identified in the middle of all terms of the SQL pseudo-language. Because it is a simple string, it requires something that allows them to be identified during interpretation by the DBMS. This form of identification is usually the two-point character.

Placeholders, however, they are not nameable. They only reserve a place for the real values. These are usually used a question (?) in the middle of the statement.

Parameters, or named placeholders, has as an advantage the ease of interaction with dynamic arguments once a key/value association is made, that is, the parameters found in statement are searched in the list of linked parameters and a simple substitution is made.

Placeholders, however, they are numerically indexed and are placed in the statement by their positions and so require a little more attention, otherwise you may end up accidentally linking (Binding) an integer to a column on the bench representing a float and at best receive an incorrect result.

---

Before you know why two-points are used in a query, you have to understand what they are Prepared statements and how they differ from a direct execution.

Let’s look at the typical workflow when using a Prepared statement:

• Prepare

A statement (statement) template is created by the Application and sent to the DBMS. Of the values assigned in the query, some may be omitted. These are called parameters, placeholders or linked variables (bind variables).

INSERT INTO PRODUCT (name, price) VALUES (:name, :price)

Or

INSERT INTO PRODUCT (name, price) VALUES (?, ?)

DBMS analyzes, compiles and executes a series of optimizations in the declaration template and stores the result without executing it.

• Execute

Later, the Application provides (or links - bind) values for the parameters and the DBMS executes the declaration possibly returning some result. The App 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 the declarations, 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 since the best optimization depends on the type of argument received as well as the best strategy today may not serve in the future, near or not, given the possible changes in the structure, be in the indexes of a table.

• Prepared statement are resilient to SQL injection because the values used later are transmitted by a different protocol, which does not require your data to be properly escaped.

Some say Prepared statements are not so advantageous for queries executed once given all the extra procedures and that this can be a performance penalty for the Application. I disagree because it depends on N factors, from hardware scalability to how well programmed the Application was.