3
I recently did some research on the internet and noticed that several people say that PDO does not protect 100% against Injection, specifically with Emulated prepares. If possible someone give me an example of SQL that would go through the PDO with Prepared statement to better understand the issue and the difference of using Emulated prepare.
8
First of all, let’s understand what the Prepared statements. When you send a query for the database engine, its SQL commands are transformed into a series of internal "commands". Simply put, they are compiled.
Usually SQL goes through a query Planner, who abstracts from the programmer what is the best way to do that task (when to use indexes, how to do the joins, etc.)
In many situations, it was realized that the cost of this compilation could be avoided in cases of repetition of commands, as several Inserts then in cases where the number of inserts can vary dynamically and cannot be sent in a single query, or in several queries with equal structure, but varied values.
The solution was to separate the values of the query itself, through the Prepared statements.
In short: you send the query only once, it is "compiled" only once, and reused with the values you send next.
Let’s just forget for a brief moment Prepared statements, and think about SQL injections. Usually, the darlings "traditional" dynamics are mounted as a concatenation of string:
$sql = 'SELECT tipo FROM batatas WHERE id='.$_POST['idDaBatata'];
In a normal situation, the application sends a "7", and the query gets like this:
SELECT tipo FROM batatas WHERE id=7
So far all legal, but an attacker could send a "0; DROP DATABASE;":
SELECT tipo FROM batatas WHERE id=0; DROP DATABASE;
Here you have a problem! (Let’s disregard the fact that it is more than one query, syntax and other protections, just wanted to simplify the example).
When we use Prepared statements, the format changes:
$sql = 'SELECT tipo FROM batatas WHERE id=?';
If we’re talking about Prepared statements natives, has no concatenation of string here. We simply reserve a placeholder with the ?, that will receive the value.
When we call the prepare, the engine DB will compile the query, she will no longer be a string and become a kind of internal "program".
To use this program, we will send the user value:
funcao_bind_da_lib( $preparado, INTEGER, $_POST['idDaBatata']; );
At this point, the data is converted into an integer (could be a string), and passed as value, directly to the compiled program, and will not be reinterpreted. At this time, even if it was a string, one DROP DATABASE would have no meaning for DB, as we have already passed the compilation phase in the previous step.
In short, when we use Prepared statements Native isolation is a "natural" consequence, and safety a side effect (desirable).
When we are simulating, is happening the first example, with strings, and on the client’s side. Our query goes through a replace exchanging the questions and placeholders for strings. The data coming from the user is sent to the compilation step, not execution.
Calm down! It can be safe yes. :)
As long as the framework/lib used "cleans" the strings, avoiding the misuse of a ;, unwanted functions, and special characters like quotes, etc.
What is necessary to understand is that "natural" protection has been lost, and that it has to be compensated in some way. And that then you depend on the quality of the lib used, or if you do "manually", the quality of your code.
In the specific case of PDO, the simulation of the prepared queries and the binding of parameters, as expected, already includes the sanitization of client-side strings (if not, it would be a source of headaches that would make its use unfeasible with values with special characters, even in its regular use).
Without native Binding, it becomes a function of lib to take the user’s data, and clear any strange things, because DB will "blindly believe" in what you send to it, as if they were your commands.
And also remembering that in this case, if 40 darlings then they will have to be transmitted in full to DB the 40 times and compiled 40 times, which is not a security problem, only performance and architecture.
This, fortunately, I don’t have, after all, if anyone finds any, you urgently need to send them as bug report, because it would be a vulnerability affecting a huge number of users :)
Browser other questions tagged php pdo security-guard
You are not signed in. Login or sign up in order to post.
When you say "misuse of a ;" does that mean that the example below would pass? $sql = 'SELECT type FROM potatoes where id=? '; function_bind_da_lib( $prepared, INTEGER, '; DROP...'; );
– Math
It does not pass pq the PDO internally goes through an "escape" in all the strange characters, nullifying them. The important thing is to understand that it is not Prepared statement who is protecting you in this case, and yes the PDO code doing a "manual" cleaning internally. The big risk of not using the native is that you stay depending on the lib programmer be careful. With Prepared native There’s not much left to go wrong. In the case of PDO, of course, there are many people testing to pass nothing. But being aware of the difference is good at the time you can choose between something native or not.
– Bacco
I think it is now clear, it is because when you said "Since the framework/lib used, do the "cleaning" of the strings", I thought it would be necessary to use PDO + to do this cleaning, but your phrase referred to the concept itself, that PDO itself already implements.
– Math