Return to Answer

Regarding many useful answers, I hope to add some values to this thread. SQL injection is an 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 a bad person that try to get secret information (bypassing access control) that affect the three principles of security (Confidentiality, Integrity, Availability).

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?

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.

1. SQL user (limiting user privilege): most common SQL operations are (SELECT, UPDATE, INSERT), then, why giving UPDATE privilege to a user that not require it? For example login, and search pages are only using SELECT, then, why using DB users in these pages with high privileges? 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.

see Principle of least privilege

2. Data filtering: before building any query user input should be validated and filtered, for programmers, it's important to define some properties for each user-input variables: data type, data pattern, and data length. a field that is a number between (x and y) must be exactly validated using exact rule, for a field that is a string (text): pattern is the case, for example, username must contain only some characters lets say [a-zA-Z0-9_-.] the length varies between (x and n) where x and n (integers, x <=n  ). Rule: creating exact filters and validation rules are best practice for me.

3. Use other tools: Here, I will also agree with you that prepared statement (parametrized query) and Stored procedures, the disadvantages here is these ways requires advanced skills which do not exist for most users, the basic idea here is to distinguish between the SQL query and the data that is used inside, both approaches can be used even with unsafe data, because the user-input data here not add anything to the original query such as (any or x=x). For more information, please read OWASP SQL Injection Prevention Cheat Sheet.

Now, if you are an advanced user, start using this defense as you like, but, for beginners, if they can't quickly implement stored procedure and prepared the statement, it's better to filter input data as much they can.

Finally, let's consider that user sends this text below instead of entering his username:

The 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.

##UPDATE1: A user commented that this post is useless, OK! Here is what OWASP.ORG provided:

As you may know, claiming an article should be supported by valid argument, at least one reference! Otherwise, it's considered as an attack and bad claim!

##Update2:

##Update3:

I created test cases for knowing how PDO and MySQLi send the query to the MySQL server when using prepared statement:

$user = "''1''"; //Malicious keyword
$sql = 'SELECT * FROM awa_user WHERE userame =:username';
$sth = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$sth->execute(array(':username' => $user));

As also mentioned in the above statement 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, therefore, this proves that data validation such as intval() is a good idea for integer values before sending any query, in addition, preventing malicious user data before sending the query is correct and valid approach.

Please see this question for more detail: PDO sends raw query to MySQL while Mysqli sends prepared query, both produce the same result

Regarding many useful answers, I hope to add some value to this thread.

SQL injection is an attack that can be done through user inputs (inputs that filled by a user and then used inside queries). The SQL injection patterns are correct query syntax while we can call it: bad queries for bad reasons, and we assume that there might be a bad person that try to get secret information (bypassing access control) that affect the three principles of security (confidentiality, integrity, and availability).

Now, our point is to prevent security threats such as SQL injection attacks, the question asking (how to prevent an 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 do you secure your application?

Data filtering for (converting unsafe data to safe data)

Consider that PDO and MySQLi are 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 a specific language.

1. SQL user (limiting user privilege): most common SQL operations are (SELECT, UPDATE, INSERT), then, why give the UPDATE privilege to a user that does not require it? For example, login, and search pages are only using SELECT, then, why use DB users in these pages with high privileges?

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.

See principle of least privilege.

2. Data filtering: before building any query user input, it should be validated and filtered. For programmers, it's important to define some properties for each user-input variables: data type, data pattern, and data length. A field that is a number between (x and y) must be exactly validated using the exact rule, and for a field that is a string (text): pattern is the case, for example, a username must contain only some characters, let’s say [a-zA-Z0-9_-.]. The length varies between (x and n) where x and n (integers, x <=n). Rule: creating exact filters and validation rules are best practices for me.

3. Use other tools: Here, I will also agree with you that a prepared statement (parametrized query) and stored procedures. The disadvantages here is these ways require advanced skills which do not exist for most users. The basic idea here is to distinguish between the SQL query and the data that is used inside. Both approaches can be used even with unsafe data, because the user-input data here does not add anything to the original query, such as (any or x=x).

For more information, please read OWASP SQL Injection Prevention Cheat Sheet.

Now, if you are an advanced user, start using this defense as you like, but, for beginners, if they can't quickly implement a stored procedure and prepared the statement, it's better to filter input data as much they can.

Finally, let's consider that a user sends this text below instead of entering his/her username:

The last point is detecting unexpected behavior which requires more effort and complexity; it's not recommended for normal web applications.

Unexpected behavior in the above user input is SELECT, UNION, IF, SUBSTRING, BENCHMARK, SHA, and root. Once these words detected, you can avoid the input.

##UPDATE 1:

A user commented that this post is useless, OK! Here is what OWASP.ORG provided:

As you may know, claiming an article should be supported by a valid argument, at least by one reference! Otherwise, it's considered as an attack and a bad claim!

##Update 2:

##Update 3:

I created test cases for knowing how PDO and MySQLi send the query to the MySQL server when using a prepared statement:

$user = "''1''"; // Malicious keyword
$sql = 'SELECT * FROM awa_user WHERE userame =:username';
$sth = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$sth->execute(array(':username' => $user));

As also mentioned in the above statement,

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

Therefore, this proves that data validation such as intval() is a good idea for integer values before sending any query. In addition, preventing malicious user data before sending the query is a correct and valid approach.

Please see this question for more detail: PDO sends raw query to MySQL while Mysqli sends prepared query, both produce the same result