From What Do SQL Parameters Protect You

From what do sql parameters protect you?

Parameterized queries generally quote the parameter if it is a string behind the scene so that the normal SQL operators are not interpreted as such. This means that even if a user enters potentially malicious data it is simply treated as a string input and not interpreted as SQL operators/commands.

There may be technical differences in how it is implemented in the various frameworks, but the basic idea (and result) is the same.

How does SQLParameter prevent SQL Injection?

Basically, when you perform a SQLCommand using SQLParameters, the parameters are never inserted directly into the statement. Instead, a system stored procedure called sp_executesql is called and given the SQL string and the array of parameters.

When used as such, the parameters are isolated and treated as data, instead of having to be parsed out of the statement (and thus possibly changing it), so what the parameters contain can never be "executed". You'll just get a big fat error that the parameter value is invalid in some way.

How does using parameters prevent SQL injection?

Because simply entering "drop database" into the field on its own is not sufficient. You'd have to add some other characters to convince the SQL interpreter to terminate the previous statement and start a new one (to execute the DROP). Parameterisation will, among other things, prevent those kind of sequences from successfully being injected and interpreted in the intended manner by SQL. Characters such as ' for instance, which might close a string, will be escaped so that they are considered part of a variable, not part of the SQL.

Specifically, http://bobby-tables.com/about contains a worked example. In the example, there is some PHP code to insert a row into a table:

$sql = "INSERT INTO Students (Name) VALUES ('" . $studentName . "');";
execute_sql($sql);

If $studentName is set to something normal like "John", then the final SQL string produced by the code is benign:

INSERT INTO Students (Name) VALUES ('John');

Equally, similar to your example, if $studentName was set to "DROP TABLE Students", it still wouldn't have any effect. The final SQL would be:

INSERT INTO Students (Name) VALUES ('DROP TABLE Students');

No harm done.

But...if $studentName is set to something a bit more subtle, like this:

Robert'); DROP TABLE Students;--

The final string looks like this:

INSERT INTO Students (Name) VALUES ('Robert'); DROP TABLE Students;--');

This is then passed to the SQL engine, which interprets it as two statements (with, incidentally, a comment at the end) and executes them both, with unpleasant consequences.

However, if the value in $studentName had been passed as a parameter instead of just joined to a standard string, the final query would have ended up as

INSERT INTO Students (Name) VALUES ('Robert\'); DROP TABLE Students;--');

Notice the escaped ' in the middle, so it's now considered part of the string. It no longer causes the string to stop after "t".

Therefore what gets entered into the Name field in the table will be

Robert'); DROP TABLE Students;--

The DROP TABLE statement won't get executed because the SQL interpreter never sees it - it just treats it as part of the value to be added to the table.

Parameterisation means that anything within the parameter value is treated as a string (or possibly number) only - it can never escape outside that and be considered as part of the SQL statement itself.

How can prepared statements protect from SQL injection attacks?

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 in the mixing of the code and the data.

In fact, our SQL query is a legitimate program.
And we are creating such a program dynamically, adding some data on the fly. Thus, the 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 becomes a part of the program, so the data may alter the program, and depending on the data passed, we will either have 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 exactly the same query is 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?

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

Prepared statements can protect only data literals, but cannot be used with any other query part.

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.

Why do we always prefer using parameters in SQL statements?

Using parameters helps prevent SQL Injection attacks when the database is used in conjunction with a program interface such as a desktop program or web site.

In your example, a user can directly run SQL code on your database by crafting statements in txtSalary.

For example, if they were to write 0 OR 1=1, the executed SQL would be

 SELECT empSalary from employee where salary = 0 or 1=1

whereby all empSalaries would be returned.

Further, a user could perform far worse commands against your database, including deleting it If they wrote 0; Drop Table employee:

SELECT empSalary from employee where salary = 0; Drop Table employee

The table employee would then be deleted.

In your case, it looks like you're using .NET. Using parameters is as easy as:

string sql = "SELECT empSalary from employee where salary = @salary";

using (SqlConnection connection = new SqlConnection(/* connection info */))
using (SqlCommand command = new SqlCommand(sql, connection))
{
    var salaryParam = new SqlParameter("salary", SqlDbType.Money);
    salaryParam.Value = txtMoney.Text;

    command.Parameters.Add(salaryParam);
    var results = command.ExecuteReader();
}

Dim sql As String = "SELECT empSalary from employee where salary = @salary"
Using connection As New SqlConnection("connectionString")
    Using command As New SqlCommand(sql, connection)
        Dim salaryParam = New SqlParameter("salary", SqlDbType.Money)
        salaryParam.Value = txtMoney.Text

        command.Parameters.Add(salaryParam)

        Dim results = command.ExecuteReader()
    End Using
End Using

Edit 2016-4-25:

As per George Stocker's comment, I changed the sample code to not use AddWithValue. Also, it is generally recommended that you wrap IDisposables in using statements.

Are Parameters really enough to prevent Sql injections?

Placeholders are enough to prevent injections. You might still be open to buffer overflows, but that is a completely different flavor of attack from an SQL injection (the attack vector would not be SQL syntax but binary). Since the parameters passed will all be escaped properly, there isn't any way for an attacker to pass data that will be treated like "live" SQL.

You can't use functions inside placeholders, and you can't use placeholders as column or table names, because they are escaped and quoted as string literals.

However, if you use parameters as part of a string concatenation inside your dynamic query, you are still vulnerable to injection, because your strings will not be escaped but will be literal. Using other types for parameters (such as integer) is safe.

That said, if you're using use input to set the value of something like security_level, then someone could just make themselves administrators in your system and have a free-for-all. But that's just basic input validation, and has nothing to do with SQL injection.

Can parameterized statement stop all SQL injection?

The links that I have posted in my comments to the question explain the problem very well. I've summarised my feelings on why the problem persists, below:

Those just starting out may have no awareness of SQL injection.
Some are aware of SQL injection, but think that escaping is the (only?) solution. If you do a quick Google search for php mysql query, the first page that appears is the mysql_query page, on which there is an example that shows interpolating escaped user input into a query. There's no mention (at least not that I can see) of using prepared statements instead. As others have said, there are so many tutorials out there that use parameter interpolation, that it's not really surprising how often it is still used.
A lack of understanding of how parameterized statements work. Some think that it is just a fancy means of escaping values.
Others are aware of parameterized statements, but don't use them because they have heard that they are too slow. I suspect that many people have heard how incredibly slow paramterized statements are, but have not actually done any testing of their own. As Bill Karwin pointed out in his talk, the difference in performance should rarely be used as a factor when considering the use of prepared statements. The benefits of prepare once, execute many, often appear to be forgotten, as do the improvements in security and code maintainability.
Some use parameterized statements everywhere, but with interpolation of unchecked values such as table and columns names, keywords and conditional operators. Dynamic searches, such as those that allow users to specify a number of different search fields, comparison conditions and sort order, are prime examples of this.
False sense of security when using an ORM. ORMs still allow interpolation of SQL statement parts - see 5.
Programming is a big and complex subject, database management is a big and complex subject, security is a big and complex subject. Developing a secure database application is not easy - even experienced developers can get caught out.
Many of the answers on stackoverflow don't help. When people write questions that use dynamic SQL and parameter interpolation, there is often a lack of responses that suggest using parameterized statements instead. On a few occasions, I've had people rebut my suggestion to use prepared statements - usually because of the perceived unacceptable performance overhead. I seriously doubt that those asking most of these questions are in a position where the extra few milliseconds taken to prepare a parameterized statement will have a catastrophic effect on their application.

How do parameterized queries help against SQL injection?

Parameterized queries do proper substitution of arguments prior to running the SQL query. It completely removes the possibility of "dirty" input changing the meaning of your query. That is, if the input contains SQL, it can't become part of what is executed because the SQL is never injected into the resulting statement.

How does Command.Parameters.Add() prevent SQL injection? C#

The short version:
Basically, the sql engine tearts sql parameters as placeholder for values.

This means that even if your @Name parameter would be name';drop table tableName;-- you end up with a query like this:

SELECT * FROM table WHERE Name = 'name'';drop table tableName;--'

As apposed to if you would have concatenate strings to create the sql - that would create a query like this:

SELECT * FROM table WHERE Name = 'name';drop table tableName;--

As you can see, the first query is safe, while the second one is not. It allows you to add harmful code to your sql.

While if you are using parameters, your harmful code will not be treated as code.

Important note: If you are sending parameters to a stored procedure that use these parameters to create and run dynamic sql, you are still not safe - since it is concatenating strings inside the stored procedure.

As a side note, some database engines such as oracle will allow only one command in each execute, but can still be hacked using name' OR 1=1 that will create

SELECT * FROM table WHERE Name = 'name' OR 1=1

From What Do SQL Parameters Protect You