Rectangle 27 1

mysql How can I prevent SQL injection in PHP?


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

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?

Primary defenses: Option #1: Use of Prepared Statements (Parameterized Queries) Option #2: Use of Stored Procedures Option #3: Escaping all User Supplied Input Additional defenses: 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.

Note
Rectangle 27 1

mysql How can I prevent SQL injection in PHP?


In my opinion, the best way to generally prevent SQL injection in your PHP application (or any web application, for that matter) is to think about your application's architecture. If the only way to protect against SQL injection is to remember to use a special method or function that does The Right Thing every time you talk to the database, you are doing it wrong. That way, it's just a matter of time until you forget to correctly format your query at some point in your code.

Adopting the MVC pattern and a framework like CakePHP or CodeIgniter is probably the right way to go: Common tasks like creating secure database queries have been solved and centrally implemented in such frameworks. They help you to organize your web application in a sensible way and make you think more about loading and saving objects than about securely constructing single SQL queries.

@AnthonyRutledge I agree! I think the use-case makes a difference too: Am I building a photo gallery for my personal homepage or am I building an online banking web application? In the latter case it's very important to understand the details of security and how a framework that I am using is addressing those.

@AnthonyRutledge You are absolutely correct. It is very important to understand what is going on and why. However, the chance that a true-and-tried and actively used and developed framework has run into and solved a lot of issues and patched a lot of security holes already is pretty high. It's a good idea to look at the source to get a feel for the code quality. If it's an untested mess it's probably not secure.

Adopting the MVC pattern and a framework like CakePHP or CodeIgniter is probably the right way to go: Common tasks like creating secure database queries have been solved and centrally implemented in such frameworks. They help you to organize your web application in a sensible way and make you think more about loading and saving objects than about securely constructing single SQL queries.

Ah, the security exception to the do it yourself corollary. See, I tend to be willing to risk it all and go for broke. :-) Kidding. With enough time, people can learn to make a pretty darn secure application. Too many people are in a rush. They throw their hands up and assume that the frameworks are safer. After all, they do not have enough time to test and figure things out. Moreover, security is a field that requires dedicated study. It is not something mere programmers know in depth by virtue of understanding algorithms and design patterns.

Here. Here. Good points. However, would you agree that many people can study and learn to adopt an MVC system, but not everyone can reproduce it by hand (controllers and server). One can go too far with this point. Do I need to understand my microwave before I heat up my peanut butter pecan cookies my girl friend made me? ;-)

I think your first paragraph is important. Understanding is key. Also, everyone is not working for a company. For a large swath of people, frameworks actually go against the idea of understanding. Getting intimate with the fundamentals may not be valued while working under a deadline, but the do-it-yourselfers out there enjoy getting their hands dirty. Framework developers are not so privileged that everyone else must bow and assume they never make mistakes. The power to make decisions is still important. Who is to say that my framework won't displace some other scheme in the future?

In my opinion, the best way to generally prevent SQL injection in your PHP application (or any web application, for that matter) is to think about your application's architecture. If the only way to protect against SQL injection is to remember to use a special method or function that does The Right Thing every time you talk to the database, you are doing it wrong. That way, it's just a matter of time until you forget to correctly format your query at some point in your code.

Note
Rectangle 27 1

mysql How can I prevent SQL injection in PHP?


@AnthonyRutledge I agree! I think the use-case makes a difference too: Am I building a photo gallery for my personal homepage or am I building an online banking web application? In the latter case it's very important to understand the details of security and how a framework that I am using is addressing those.

@AnthonyRutledge You are absolutely correct. It is very important to understand what is going on and why. However, the chance that a true-and-tried and actively used and developed framework has run into and solved a lot of issues and patched a lot of security holes already is pretty high. It's a good idea to look at the source to get a feel for the code quality. If it's an untested mess it's probably not secure.

Adopting the MVC pattern and a framework like CakePHP or CodeIgniter is probably the right way to go: Common tasks like creating secure database queries have been solved and centrally implemented in such frameworks. They help you to organize your web application in a sensible way and make you think more about loading and saving objects than about securely constructing single SQL queries.

Ah, the security exception to the do it yourself corollary. See, I tend to be willing to risk it all and go for broke. :-) Kidding. With enough time, people can learn to make a pretty darn secure application. Too many people are in a rush. They throw their hands up and assume that the frameworks are safer. After all, they do not have enough time to test and figure things out. Moreover, security is a field that requires dedicated study. It is not something mere programmers know in depth by virtue of understanding algorithms and design patterns.

Here. Here. Good points. However, would you agree that many people can study and learn to adopt an MVC system, but not everyone can reproduce it by hand (controllers and server). One can go too far with this point. Do I need to understand my microwave before I heat up my peanut butter pecan cookies my girl friend made me? ;-)

I think your first paragraph is important. Understanding is key. Also, everyone is not working for a company. For a large swath of people, frameworks actually go against the idea of understanding. Getting intimate with the fundamentals may not be valued while working under a deadline, but the do-it-yourselfers out there enjoy getting their hands dirty. Framework developers are not so privileged that everyone else must bow and assume they never make mistakes. The power to make decisions is still important. Who is to say that my framework won't displace some other scheme in the future?

In my opinion, the best way to generally prevent SQL injection in your PHP application (or any web application, for that matter) is to think about your application's architecture. If the only way to protect against SQL injection is to remember to use a special method or function that does The Right Thing every time you talk to the database, you are doing it wrong. That way, it's just a matter of time until you forget to correctly format your query at some point in your code.

Note
Rectangle 27 1

mysql How can I prevent SQL injection in PHP?


@AnthonyRutledge I agree! I think the use-case makes a difference too: Am I building a photo gallery for my personal homepage or am I building an online banking web application? In the latter case it's very important to understand the details of security and how a framework that I am using is addressing those.

@AnthonyRutledge You are absolutely correct. It is very important to understand what is going on and why. However, the chance that a true-and-tried and actively used and developed framework has run into and solved a lot of issues and patched a lot of security holes already is pretty high. It's a good idea to look at the source to get a feel for the code quality. If it's an untested mess it's probably not secure.

Adopting the MVC pattern and a framework like CakePHP or CodeIgniter is probably the right way to go: Common tasks like creating secure database queries have been solved and centrally implemented in such frameworks. They help you to organize your web application in a sensible way and make you think more about loading and saving objects than about securely constructing single SQL queries.

Ah, the security exception to the do it yourself corollary. See, I tend to be willing to risk it all and go for broke. :-) Kidding. With enough time, people can learn to make a pretty darn secure application. Too many people are in a rush. They throw their hands up and assume that the frameworks are safer. After all, they do not have enough time to test and figure things out. Moreover, security is a field that requires dedicated study. It is not something mere programmers know in depth by virtue of understanding algorithms and design patterns.

Here. Here. Good points. However, would you agree that many people can study and learn to adopt an MVC system, but not everyone can reproduce it by hand (controllers and server). One can go too far with this point. Do I need to understand my microwave before I heat up my peanut butter pecan cookies my girl friend made me? ;-)

I think your first paragraph is important. Understanding is key. Also, everyone is not working for a company. For a large swath of people, frameworks actually go against the idea of understanding. Getting intimate with the fundamentals may not be valued while working under a deadline, but the do-it-yourselfers out there enjoy getting their hands dirty. Framework developers are not so privileged that everyone else must bow and assume they never make mistakes. The power to make decisions is still important. Who is to say that my framework won't displace some other scheme in the future?

In my opinion, the best way to generally prevent SQL injection in your PHP application (or any web application, for that matter) is to think about your application's architecture. If the only way to protect against SQL injection is to remember to use a special method or function that does The Right Thing every time you talk to the database, you are doing it wrong. That way, it's just a matter of time until you forget to correctly format your query at some point in your code.

Note
Rectangle 27 1

mysql How can I prevent SQL injection in PHP?


@AnthonyRutledge I agree! I think the use-case makes a difference too: Am I building a photo gallery for my personal homepage or am I building an online banking web application? In the latter case it's very important to understand the details of security and how a framework that I am using is addressing those.

@AnthonyRutledge You are absolutely correct. It is very important to understand what is going on and why. However, the chance that a true-and-tried and actively used and developed framework has run into and solved a lot of issues and patched a lot of security holes already is pretty high. It's a good idea to look at the source to get a feel for the code quality. If it's an untested mess it's probably not secure.

Adopting the MVC pattern and a framework like CakePHP or CodeIgniter is probably the right way to go: Common tasks like creating secure database queries have been solved and centrally implemented in such frameworks. They help you to organize your web application in a sensible way and make you think more about loading and saving objects than about securely constructing single SQL queries.

Ah, the security exception to the do it yourself corollary. See, I tend to be willing to risk it all and go for broke. :-) Kidding. With enough time, people can learn to make a pretty darn secure application. Too many people are in a rush. They throw their hands up and assume that the frameworks are safer. After all, they do not have enough time to test and figure things out. Moreover, security is a field that requires dedicated study. It is not something mere programmers know in depth by virtue of understanding algorithms and design patterns.

Here. Here. Good points. However, would you agree that many people can study and learn to adopt an MVC system, but not everyone can reproduce it by hand (controllers and server). One can go too far with this point. Do I need to understand my microwave before I heat up my peanut butter pecan cookies my girl friend made me? ;-)

I think your first paragraph is important. Understanding is key. Also, everyone is not working for a company. For a large swath of people, frameworks actually go against the idea of understanding. Getting intimate with the fundamentals may not be valued while working under a deadline, but the do-it-yourselfers out there enjoy getting their hands dirty. Framework developers are not so privileged that everyone else must bow and assume they never make mistakes. The power to make decisions is still important. Who is to say that my framework won't displace some other scheme in the future?

In my opinion, the best way to generally prevent SQL injection in your PHP application (or any web application, for that matter) is to think about your application's architecture. If the only way to protect against SQL injection is to remember to use a special method or function that does The Right Thing every time you talk to the database, you are doing it wrong. That way, it's just a matter of time until you forget to correctly format your query at some point in your code.

Note
Rectangle 27 1

mysql How can I prevent SQL injection in PHP?


@AnthonyRutledge I agree! I think the use-case makes a difference too: Am I building a photo gallery for my personal homepage or am I building an online banking web application? In the latter case it's very important to understand the details of security and how a framework that I am using is addressing those.

@AnthonyRutledge You are absolutely correct. It is very important to understand what is going on and why. However, the chance that a true-and-tried and actively used and developed framework has run into and solved a lot of issues and patched a lot of security holes already is pretty high. It's a good idea to look at the source to get a feel for the code quality. If it's an untested mess it's probably not secure.

Adopting the MVC pattern and a framework like CakePHP or CodeIgniter is probably the right way to go: Common tasks like creating secure database queries have been solved and centrally implemented in such frameworks. They help you to organize your web application in a sensible way and make you think more about loading and saving objects than about securely constructing single SQL queries.

Ah, the security exception to the do it yourself corollary. See, I tend to be willing to risk it all and go for broke. :-) Kidding. With enough time, people can learn to make a pretty darn secure application. Too many people are in a rush. They throw their hands up and assume that the frameworks are safer. After all, they do not have enough time to test and figure things out. Moreover, security is a field that requires dedicated study. It is not something mere programmers know in depth by virtue of understanding algorithms and design patterns.

Here. Here. Good points. However, would you agree that many people can study and learn to adopt an MVC system, but not everyone can reproduce it by hand (controllers and server). One can go too far with this point. Do I need to understand my microwave before I heat up my peanut butter pecan cookies my girl friend made me? ;-)

I think your first paragraph is important. Understanding is key. Also, everyone is not working for a company. For a large swath of people, frameworks actually go against the idea of understanding. Getting intimate with the fundamentals may not be valued while working under a deadline, but the do-it-yourselfers out there enjoy getting their hands dirty. Framework developers are not so privileged that everyone else must bow and assume they never make mistakes. The power to make decisions is still important. Who is to say that my framework won't displace some other scheme in the future?

In my opinion, the best way to generally prevent SQL injection in your PHP application (or any web application, for that matter) is to think about your application's architecture. If the only way to protect against SQL injection is to remember to use a special method or function that does The Right Thing every time you talk to the database, you are doing it wrong. That way, it's just a matter of time until you forget to correctly format your query at some point in your code.

Note
Rectangle 27 0

mysql How can I prevent SQL injection in PHP?


@AnthonyRutledge I agree! I think the use-case makes a difference too: Am I building a photo gallery for my personal homepage or am I building an online banking web application? In the latter case it's very important to understand the details of security and how a framework that I am using is addressing those.

@AnthonyRutledge You are absolutely correct. It is very important to understand what is going on and why. However, the chance that a true-and-tried and actively used and developed framework has run into and solved a lot of issues and patched a lot of security holes already is pretty high. It's a good idea to look at the source to get a feel for the code quality. If it's an untested mess it's probably not secure.

Adopting the MVC pattern and a framework like CakePHP or CodeIgniter is probably the right way to go: Common tasks like creating secure database queries have been solved and centrally implemented in such frameworks. They help you to organize your web application in a sensible way and make you think more about loading and saving objects than about securely constructing single SQL queries.

Ah, the security exception to the do it yourself corollary. See, I tend to be willing to risk it all and go for broke. :-) Kidding. With enough time, people can learn to make a pretty darn secure application. Too many people are in a rush. They throw their hands up and assume that the frameworks are safer. After all, they do not have enough time to test and figure things out. Moreover, security is a field that requires dedicated study. It is not something mere programmers know in depth by virtue of understanding algorithms and design patterns.

Here. Here. Good points. However, would you agree that many people can study and learn to adopt an MVC system, but not everyone can reproduce it by hand (controllers and server). One can go too far with this point. Do I need to understand my microwave before I heat up my peanut butter pecan cookies my girl friend made me? ;-)

I think your first paragraph is important. Understanding is key. Also, everyone is not working for a company. For a large swath of people, frameworks actually go against the idea of understanding. Getting intimate with the fundamentals may not be valued while working under a deadline, but the do-it-yourselfers out there enjoy getting their hands dirty. Framework developers are not so privileged that everyone else must bow and assume they never make mistakes. The power to make decisions is still important. Who is to say that my framework won't displace some other scheme in the future?

In my opinion, the best way to generally prevent SQL injection in your PHP application (or any web application, for that matter) is to think about your application's architecture. If the only way to protect against SQL injection is to remember to use a special method or function that does The Right Thing every time you talk to the database, you are doing it wrong. That way, it's just a matter of time until you forget to correctly format your query at some point in your code.

Note
Rectangle 27 0

mysql How can I prevent SQL injection in PHP?


@AnthonyRutledge I agree! I think the use-case makes a difference too: Am I building a photo gallery for my personal homepage or am I building an online banking web application? In the latter case it's very important to understand the details of security and how a framework that I am using is addressing those.

@AnthonyRutledge You are absolutely correct. It is very important to understand what is going on and why. However, the chance that a true-and-tried and actively used and developed framework has run into and solved a lot of issues and patched a lot of security holes already is pretty high. It's a good idea to look at the source to get a feel for the code quality. If it's an untested mess it's probably not secure.

Adopting the MVC pattern and a framework like CakePHP or CodeIgniter is probably the right way to go: Common tasks like creating secure database queries have been solved and centrally implemented in such frameworks. They help you to organize your web application in a sensible way and make you think more about loading and saving objects than about securely constructing single SQL queries.

Ah, the security exception to the do it yourself corollary. See, I tend to be willing to risk it all and go for broke. :-) Kidding. With enough time, people can learn to make a pretty darn secure application. Too many people are in a rush. They throw their hands up and assume that the frameworks are safer. After all, they do not have enough time to test and figure things out. Moreover, security is a field that requires dedicated study. It is not something mere programmers know in depth by virtue of understanding algorithms and design patterns.

Here. Here. Good points. However, would you agree that many people can study and learn to adopt an MVC system, but not everyone can reproduce it by hand (controllers and server). One can go too far with this point. Do I need to understand my microwave before I heat up my peanut butter pecan cookies my girl friend made me? ;-)

I think your first paragraph is important. Understanding is key. Also, everyone is not working for a company. For a large swath of people, frameworks actually go against the idea of understanding. Getting intimate with the fundamentals may not be valued while working under a deadline, but the do-it-yourselfers out there enjoy getting their hands dirty. Framework developers are not so privileged that everyone else must bow and assume they never make mistakes. The power to make decisions is still important. Who is to say that my framework won't displace some other scheme in the future?

In my opinion, the best way to generally prevent SQL injection in your PHP application (or any web application, for that matter) is to think about your application's architecture. If the only way to protect against SQL injection is to remember to use a special method or function that does The Right Thing every time you talk to the database, you are doing it wrong. That way, it's just a matter of time until you forget to correctly format your query at some point in your code.

Note
Rectangle 27 0

mysql How can I prevent SQL injection in PHP?


$mysqli = new mysqli( 'host', 'user', 'password', 'database' );
$mysqli->set_charset( 'charset');

$string = $mysqli->real_escape_string( $string );
$mysqli->query( "INSERT INTO table (column) VALUES ('$string')" );
$stmt = $mysqli->prepare( "INSERT INTO table ( column1, column2 ) VALUES (?,?)" );

$stmt->bind_param( "is", $integer, $string );

$stmt->execute();
$string = "x' OR name LIKE '%John%";
$integer = '5 OR id != 0';

$query = sprintf( "SELECT id, email, pass, name FROM members WHERE email ='%s' AND id = %d", $mysqli->real_escape_string( $string ), $integer );

echo $query;
// SELECT id, email, pass, name FROM members WHERE email ='x\' OR name LIKE \'%John%' AND id = 5

$integer = '99999999999999999999';
$query = sprintf( "SELECT id, email, pass, name FROM members WHERE email ='%s' AND id = %d", $mysqli->real_escape_string( $string ), $integer );

echo $query;
// SELECT id, email, pass, name FROM members WHERE email ='x\' OR name LIKE \'%John%' AND id = 2147483647

A few guidelines for escaping special characters in SQL statements.

And use of mysqli_real_escape_string is for, as the name says, escaping special characters in a string, so it will not make integers safe. The purpose of this function is to prevent breaking the strings in SQL statements, and the damage to the database that it could cause. mysqli_real_escape_string is a useful function when used properly, especially when combined with sprintf.

Don't use MySQL, this extension is deprecated, use MySQLi or PDO.

For automatic escaping of values with prepared statements, use mysqli_prepare, and mysqli_stmt_bind_param where types for the corresponding bind variables must be provided for an appropriate conversion:

For manually escaping special characters in a string you can use the mysqli_real_escape_string function. The function will not work properly unless the correct character set is set with mysqli_set_charset.

No matter if you use prepared statements or mysqli_real_escape_string, you always have to know the type of input data you're working with.

So if you use a prepared statement, you must specify the types of the variables for mysqli_stmt_bind_param function.

The question is very generic. Some great answers above, but most suggest prepared statements. MySQLi async does not support prepared statements, so the sprintf looks like a great option for this situation.

Note
Rectangle 27 0

mysql How can I prevent SQL injection in PHP?


188 Prepare   SELECT * FROM awa_user WHERE username =?
    188 Execute   SELECT * FROM awa_user WHERE username ='\'\'1\'\''
    188 Quit
189 Query SELECT * FROM awa_user WHERE userame ='\'\'1\'\''
    189 Quit
$stmt = $mysqli->prepare("SELECT * FROM awa_user WHERE username =?")) {
$stmt->bind_param("s", $user);
$user = "''1''";
$stmt->execute();
$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));
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
[1] UNION SELECT IF(SUBSTRING(Password,1,1)='2',BENCHMARK(100000,SHA1(1)),0) User,Password FROM mysql.user WHERE User = 'root'
intval()

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.

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

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 send the query to MySQL server when using prepared statement:

My approach against SQL injection is: clearing user-input data before sending it to the database (before using it inside any query).

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.

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?

Primary defenses: Option #1: Use of Prepared Statements (Parameterized Queries) Option #2: Use of Stored Procedures Option #3: Escaping all User Supplied Input Additional defenses: Also Enforce: Least Privilege Also Perform: White List Input Validation

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 bad person that try to get secret information (bypassing 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.

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.

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.

Note