PDO with Emulated prepares is insecure? What's the difference?

Understanding Prepared Statements

First of all let's understand what the prepared statements are. When you send a query to the database engine, your SQL commands are transformed into a series of internal "commands". Simply put, they are compiled.

Normally, SQL goes through a query planner , which abstracts the programmer from the best way to do that task (when using indexes, such as joins, etc.) )

In many situations it has been realized that the cost of this compilation could be avoided in instances of repetition of commands such as several inserts then in cases where the number of inserts may vary dynamically and can not be sent in a single query, or in multiple queries with equal structure, but varying values.

The solution was to separate the values of the query itself, through the prepared statements .

In summary: you send the query only once, it is "compiled" once, and reused with the values you send next.

Security x Prepared Statements

Let's just forget about the prepared statements , and think about SQL injections. Usually, traditional "dynamic" queries are assembled 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 looks like this:

SELECT tipo FROM batatas WHERE id=7

So far so good, 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 of a 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 native prepared statements , it does not have any string concatenation here. We simply reserve a placeholder with the ? , which will receive the value.

When we call prepare , the engine of the DB will compile the query , it will no longer be a string in> 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 point, even if it were a string , a DROP DATABASE would not have any meaning for the DB, since we have already passed the build phase in the previous step.

In summary, when we use native prepared statements, isolation is a "natural" consequence, and security is a side effect (desirable).

When we are simulating , the first example, strings , and the client side is happening. Our query goes through a replace that exchanges queries and placeholders for strings . The data that comes from the user is sent to the compilation stage, not execution.

So simulating Prepared Statements is not secure?

Calm down! It can be safe, yes. :)

Since the / lib framework used, clean strings , avoiding undue use of ; , unwanted functions, and special characters such as quotation marks, etc.

>

What needs to be understood is that "natural" protection has been lost, and that it has to be compensated in some way. And that depends on the quality of the lib used, or if you do "manually", the quality of your code.

In the specific case of the PDO, the simulation of prepared queries and parameter binding, as expected, already includes the sanitization of client-side strings (otherwise, it would be a source of headaches that would make its non-usable use with values with special characters, even in its regular usage).

Without native binding, it becomes the function of lib to grab the user's data, and clean up any strange things, as the DB will "blindly believe" what you send to it, as if they were your commands.

And remembering that in this case, if you send 40 queries then they will have to be passed in full to DB 40 times and compiled 40 times, which is not a security problem , just performance and architecture.

And the example that passes the PDO?

This, fortunately, I do not have, after all, if anyone finds any, they need to urgently send them as bug report , as it would be a vulnerability affecting a huge number of users: p>