How can prepared statements protect from SQL injec

Question

How do prepared statements help us prevent 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 cannot see the reason very well. What would be a simple explanation in easy English and some examples?

Answer 1

ResultSet rs = statement.executeQuery("select * from foo where value = " + httpRequest.getParameter("filter");

Let’s assume you have that in a Servlet you right. If a malevolent person passed a bad value for 'filter' you might hack your database.

Answer 2

I read through the answers and still felt the need to stress the key point which illuminates the essence of Prepared Statements. Consider two ways to query one's database where user input is involved:

Naive Approach

One concatenates user input with some partial SQL string to generate a SQL statement. In this case the user can embed malicious SQL commands, which will then be sent to the database for execution.

String SQLString = "SELECT * FROM CUSTOMERS WHERE NAME='"+userInput+"'"

For example, malicious user input can lead to SQLString being equal to "SELECT * FROM CUSTOMERS WHERE NAME='James';DROP TABLE CUSTOMERS;'

Due to the malicious user, SQLString contains 2 statements, where the 2nd one ("DROP TABLE CUSTOMERS") will cause harm.

Prepared Statements

In this case, due to the separation of the query & data, the user input is never treated as a SQL statement, and thus is never executed. It is for this reason, that any malicious SQL code injected would cause no harm. So the "DROP TABLE CUSTOMERS" would never be executed in the case above.

In a nutshell, with prepared statements malicious code introduced via user input will not be executed!

Answer 3

In SQL Server, using a prepared statement is definitely injection-proof because the input parameters don't form the query. It means that the executed query is not a dynamic query. Example of an SQL injection vulnerable statement.

string sqlquery = "select * from table where username='" + inputusername +"' and password='" + pass + "'";

Now if the value in the inoutusername variable is something like a' or 1=1 --, this query now becomes:

select * from table where username='a' or 1=1 -- and password=asda

And the rest is commented after --, so it never gets executed and bypassed as using the prepared statement example as below.

Sqlcommand command = new sqlcommand("select * from table where username = @userinput and password=@pass");
command.Parameters.Add(new SqlParameter("@userinput", 100));
command.Parameters.Add(new SqlParameter("@pass", 100));
command.prepare();

So in effect you cannot send another parameter in, thus avoiding SQL injection...

Answer 4

The key phrase is need not be correctly escaped. That means that you don't to worry about people trying to throw in dashes, apostrophes, quotes, etc...

It is all handled for you.

Answer 5

The idea is very simple - the query and the data are sent to the database server separately.
That's all.

The root of the SQL injection problem is mixing of the code and the data.

In fact, our SQL query is a legitimate program. And we are creating such a program dynamically, by adding some data on the fly. Thus, this data may interfere with the program code and even alter it, as every SQL injection example shows it (all examples in PHP/Mysql):

$expected_data = 1;
$query = "SELECT * FROM users where id=$expected_data";

will produce a regular query

SELECT * FROM users where id=1

while this code

$spoiled_data = "1; DROP TABLE users;"
$query        = "SELECT * FROM users where id=$spoiled_data";

will produce a malicious sequence

SELECT * FROM users where id=1; DROP TABLE users;

It works because we are adding the data directly to the program body and it become a part of the program, so the data may alter the program and depending on the data passed, we will have either a regular output or a table users deleted.

While in case of prepared statements we don't alter our program, it remains intact
That's the point.

We are sending a program to the server first

$db->prepare("SELECT * FROM users where id=?");

where the data is substituted by some variable called a parameter or a placeholder.

Note that the very same query being sent to the server, without any data in it! And then we're sending the data with the second request, essentially separated from the query itself:

$db->execute($data);

so, it can't alter our program and do any harm.
Quite simple - isn't it?

However, it worth to be noted that not every time you're using a placeholder, it is processed as a prepared statement.

A placeholder is a general idea for substituting the actual data with a variable for the future processing (see printf() for example), while a prepared statement is the only a subset of it.

There are cases (notably PDO in PHP can do it) when a prepared statement can be emulated, and a query is actually composed along with data and sent to the server in one request. But it is important to understand that this approach is equally safe, because every bit of data is properly formatted according to it's type and therefore nothing wrong could be happen.

The only thing I have to add that always omitted in the every manual:

Prepared statements can protect only data, but can't defend the program itself.
So, once we have to add, say, a dynamical identifier - a field name, for example, prepared statements can't help us. I've explained the matter recently, so I won't repeat myself.

Answer 6

When you create and send a prepared statement to the DBMS, it's stored as the SQL query for execution.

You later bind your data to the query such that the DBMS uses that data as the query parameters for execution (parameterization). The DBMS doesn't use the data you bind as a supplemental to the already compiled SQL query; it's simply the data.

This means it's fundamentally impossible to perform SQL injection using prepared statements. The very nature of prepared statements and their relationship with the DBMS prevents this.