To what extent do prepare and execute ensure the safety of a sql query from injection?

Question

PHP

We use PDO. How safe is the use of data received from the user for a sql query using prepare and execute in terms of a possible sql injection?
Conditional code example:

$query = $db->prepare('SELECT * FROM posts WHERE id = ?');
$query->execute([$_GET['id']);

Answer 1

In general, the question sounds rather strange. "Is it possible to use a car for driving?" "Is money good for paying for goods?", "Are prepared expressions safe from the point of view of injection protection?".
Actually, if this is their main purpose, then probably, if they did not provide protection, then they would be useless a little more than completely?
The meaning of prepared expressions is to solve 3 tasks when forming a dynamic query:
1. so that the literal inserted into the query is formatted correctly
2. so that the formatting is mandatory
3. so that it is done as close as possible to the actual execution of the query.
Thanks to these three points, guaranteed protection is provided, even taking into account the human factor.
ANY variable gets into the request only through a placeholder - this request is safe. Another thing is that in real life it doesn’t work out that way, and you have to add variables to the request. How to act in such cases, I described in a special article
And finally, it is worth mentioning that PDO has two modes of operation: "real" prepared expressions and emulation. In the first case, the request is executed in two stages - first, a request with placeholders (question marks) is sent to the database server, and then, in the next package, the data for it. This mode is also convenient if we need to execute a lot of the same type of requests, allowing us to save a couple of nanoseconds. By default, PDO only emulates this mode by sending a request in one run, substituting values ​​directly into the request. But since it formats them correctly, then again there is no danger
Well, in the end, we can mention the rarest case, inflated by hysterics like Shiflett or Ferrara - if you are Chinese and use the GBK encoding (or one of a couple of others, as often used), then you must not forget to set the connection encoding in DSN and only in DSN. Because if you use SET NAMES out of habit, then injection will be possible in emulation mode. That is, the situation when the injection through? placeholder is possible - it exists. But for this, three factors must match:
- you must be Chinese and use the GBK encoding
- the encoding must be set not in the DSN
- the emulation mode must be enabled

Answer 2

I also advise you to explicitly cast to numeric types in intval(), floatval() requests and cut off long strings (so that megabytes / gigabytes cannot be transferred).
Further, there are only vulnerabilities due to poor design of the base / service (obtaining privileges, substituting parameters).

Answer 3

Yes, it's so safe.
As an option :

$query = $db->prepare('SELECT * FROM posts WHERE id = :id');
$query->bindValue(':id', $_GET['id'], PDO::PARAM_INT);

Answer 4

With prepared expressions, they save from vulnerabilities of the first and second order. This is if the sql is not formed dynamically, and in this case only the first one.
In simple terms, it protects against all possible sql injections, ddos, and other load requests.
In our large projects (php), cleaning goes exactly like this, so far there have been no security problems.

Answer 5

In your example, security is at the level of escaping quotes and special characters. In practice, this gives good protection, but not complete, because. a hacker can use query features and tricky combinations of special characters.
The example provided by Miraage provides protection against combinations of special characters, but does not protect against incorrect database structure.

Answer 6

Also, do not forget that prepare requests do not escape tags (possibly javascript, but did not check). Just for some reason I was sure that html tags are also automatically escaped, until I checked it))