How can prepared statements protect from SQL injection attacks

Question

In what ways do prepared statements aid in the defense against SQL injection attacks?

Wikipedia says:

Prepared statements are resilient against SQL injection, because parameter values, which are transmitted later using a different protocol, need not be correctly escaped. If the original statement template is not derived from external input, SQL injection cannot occur.

I am having trouble understanding the cause. What are some examples and a straightforward explanation in basic English?

Answer 1

The concept is pretty straightforward: the query and the data are delivered separately to the database server.
Just that. The mingling of code and data is the primary cause of the SQL injection issue.

In actuality, our SQL query is a trustworthy application. A program of this kind is being created dynamically, with data being added as we go. Therefore, as every SQL injection example (all examples in PHP/MySQL) demonstrates, the data may affect the program code and potentially change it.

It works because the data is immediately added to the program body and integrated into it. As a result, the data may change the program, and based on the data supplied, we may have a regular output or a table with users eliminated.

While we don't change our program using prepared statements, it does so.
That is the key idea. First, a program is sent to the server. Where a variable known as a parameter or a placeholder replaces the data.

Keep in mind that the server receives the exact same query but without any data! The information is then transmitted with the second request, effectively apart from the actual query.

I hope this helps you.