How exactly is an sql injection with pg_prepare avoided?

For many years it was believed in the myth that mysql_real_escape_string protected from SQL Injection. Time has shown that no, and that in certain scenarios you can perfectly perpetrate an injection even using mysql_real_escape_string . You can say that this function does not really help to escape from an injection as you might think.

Practice has also shown that the most robust measure to protect against SQL injection are prepared queries.

The strength of this technique is very simple: the query and the data (element that a malicious user can manipulate to inject code) travel separately .

For this reason, prepared queries have bookmarks , which replace the data themselves, as in your query:

SELECT * FROM shops WHERE name = $1

here $1 is a marker that tells the system there is a data, then I tell you the data that is .

Next, with the pg_execute method, we tell the handler what data it is. If there is something harmful it will be analyzed and rejected, so that the injection is neutralized.

Here:

 $result = pg_execute($dbconn, "my_query", array("Joe's Widgets"));

What we say to the manager is something like: what is promised is debt, this is the data that I told you about: Joe's Widgets ... check if everything is ok and execute the query .

If the handler discovers malicious code, it will not execute the query.

This is the core of what are known as prepared queries.

In addition, prepared queries have another advantage, and that is that you can re-use them. The same query that was prepared earlier can be used to select the records that have a name equal to Clothes Clothes Clothes .

For more details you can read the question: How to avoid SQL injection in PHP? . In the answer I have given, it is explained why it is convenient to use prepared queries. And we also talk about the necessary configurations to make the code more robust.

Recommended reading: The Unexpected SQL Injection

Exhaust functions like pg_escape_string have no way to guess what kind of data you are waiting.

If you put:

$id = $_GET['id'];
$id_escapado=pg_escape_string($id);
$query = "SELECT * FROM tabla WHERE id=$id_escapado";

Perfectly the GET parameter can be passed to you by id=4 UNION ALL SELECT * FROM tabla . And this is because in that chain of text there is nothing wrong in itself. What the function does not know is that you expected a number and not a string.

Another example: you use a search engine to display all the records that start with a string:

$cadena = $_GET['cadena'];
$cadena_escapada=pg_escape_string($cadena);
$query = "SELECT * FROM tabla where nombre LIKE '$cadena_escapada%'";

Perfectly you can pass%% of the GET parameter and modify your query to show all the records that contain the string. This is because cadena=%hola has nothing malicious in the general context, but managed to alter the behavior of your LIKE by adding a wildcard because particularly for the LIKE, the% does have a special meaning.

Conclusion: prepared statements!