RULE: do not create one database user for all privileges, for all SQL operations, you can create your scheme like (deluser, selectuser, updateuser) as usernames for easy usage.
"It's clear that prepared statement also escaping the data, nothing else" - not really. What about formatting for different types? How do you write a date, or a boolean? Even if SQL injection wasn't an issue, I'd still use parameterized queries.
A user commented that this post is useless, OK! Here is what OWASP.ORG provided:
Bound variables will be escaped automatically by the server. The
server inserts their escaped values at the appropriate places into the
statement template before execution. A hint must be provided to the
server for the type of bound variable, to create an appropriate
conversion. See the mysqli_stmt_bind_param() function for more
Data filtering for (Converting unsafe data to safe data)
Consider that PDO and MySQLi not available, how can you secure your application? do you force me to use them? what about other languages other than PHP? I prefer to provide general ideas as it can be used for wider border not just for specific language.
Finally, let's consider that user sends this text below instead of entering his username:
I created test cases for knowing how PDO and MySQLi sends the query to MySQL server when using prepared statement:
I got it. But: you said "beginners, if they can't quickly implement [...] prepared statement". I'm arguing that Brad is right in his (little rude) comment - SQL parameters are much easier than building an SQL string - even ignoring security. Another benefit is that the query can easily be configured, or a const somewhere.
Last point is detecting unexpected behavior which requires more effort and complexity, it's not recommended for normal web applications.
Unexpected behavior in above user input is: SELECT, UNION, IF, SUBSTRING, BENCHMARK, SHA, root once these words detected, you can avoid the input.
My approach against SQL injection is: clearing user-input data before sending it to database (before using it inside any query).
Now, if you are an advanced user, start using these defense as you like, but, for beginners, if they can't quickly implement stored procedure and prepared statement, it's better to filter input data as much they can.
Now, our point is to prevent security threats such as SQL injection attacks, the question asking (How to prevent SQL injection attack using PHP), be more realistic, data filtering or clearing input data is the case when using user-input data inside such query, using PHP or any other programming language is not the case, or as recommended by more people to use modern technology such as prepared statement or any other tools that currently supporting SQL injection prevention, consider that these tools not available anymore? how you secure your application?
Option #1: Use of Prepared Statements (Parameterized Queries)
Option #2: Use of Stored Procedures
Option #3: Escaping all User Supplied Input
Also Enforce: Least Privilege
Also Perform: White List Input Validation
Regarding to many useful answers, I hope to add some values to this thread.
SQL injection is type of attack that can be done through user inputs (Inputs that filled by user and then used inside queries), The SQL injection patterns are correct query syntax while we can call it: bad queries for bad reasons, we assume that there might be bad person that try to get secret information (by passing access control) that affect the three principles of security (Confidentiality, Integrity, Availability).
The automatic escaping of values within the server is sometimes
considered a security feature to prevent SQL injection. The same
degree of security can be achieved with non-prepared statements, if
input values are escaped correctly.
This answer is bad! YourCommonSense outlined most of the reasons, but I'm throwing a downvote in for the thought that beginners can't use a prepared statement. Ridiculous. I would argue that it's probably easier to use prepared statements than concatenate garbage into a query in the first place. You should delete your answer.
This input can be checked early without any prepared statement and stored procedures, but to be on safe side, using them starts after user-data filtering and validation.
This is absolutely pointless answer. 1. SELECT-based injection is a disaster alone. So, #1 is quite useless. 2. Data filtering won't help for the most of real life usage. Imagine one were used on Stack Overflow - this answer just were unable to happen, as it's full of "evil" words like SELECT, UNION and even complete "malicious" BENCHMARK query. Thus, #2 is inapplicable too. 3. "Use other tools" is not a protection measure at all. So, #3 is as pointless as other two.