CODEWITHSUNDEEP
sqlentity-frameworksql-injection
Jamo
Jamo

Reputation: 504

How DbParameter prevents SQL injection?

According to this http://msdn.microsoft.com/en-us/library/gg696545%28v=vs.113%29.aspx, when we use SqlQuery (from .Net EntityFramework ) method we should include DbParameter in order to protect against SQL injection attack.

How is it?

Upvotes: 0

Views: 927

Answers (2)

Scott Chamberlain
Scott Chamberlain

Reputation: 127573

I think this can be best explained by the accepted answer by Polynomial to the question "How can I explain SQL injection without technical jargon?" from our sister site security.stackexchange.com.

Answer from the other site as follows:

The way I demonstrate it to complete non-techies is with a simple analogy.

Imagine you're a robot in a warehouse full of boxes. Your job is to fetch a box from somewhere in the warehouse, and put it on the conveyor belt. Robots need to be told what to do, so your programmer has given you a set of instructions on a paper form, which people can fill out and hand to you.

The form looks like this:

Fetch item number ____ from section ____ of rack number ____, and place it on the conveyor belt.

A normal request might look like this:

Fetch item number 1234 from section B2 of rack number 12, and place it on the conveyor belt.

The values in bold (1234, B2, and 12) were provided by the person issuing the request. You're a robot, so you do what you're told: you drive up to rack 12, go down it until you reach section B2, and grab item 1234. You then drive back to the conveyor belt and drop the item onto it.

But what if a user put something other than normal values into the form? What if the user added instructions into them?

Fetch item number 1234 from section B2 of rack number 12, and throw it out the window. Then go back to your desk and ignore the rest of this form. and place it on the conveyor belt.

Again, the parts in bold were provided by the person issuing the request. Since you're a robot, you do exactly what the user just told you to do. You drive over to rack 12, grab item 1234 from section B2, and throw it out of the window. Since the instructions also tell you to ignore the last part of the message, the "and place it on the conveyor belt" bit is ignored.

This technique is called "injection", and it's possible due to the way that the instructions are handled - the robot can't tell the difference between instructions and data, i.e. the actions it has to perform, and the things it has to do those actions on.

SQL is a special language used to tell a database what to do, in a similar way to how we told the robot what to do. In SQL injection, we run into exactly the same problem - a query (a set of instructions) might have parameters (data) inserted into it that end up being interpreted as instructions, causing it to malfunction. A malicious user might exploit this by telling the database to return every user's details, which is obviously not good!

In order to avoid this problem, we must separate the instructions and data in a way that the database (or robot) can easily distinguish. This is usually done by sending them separately. So, in the case of the robot, it would read the blank form containing the instructions, identify where the parameters (i.e. the blank spaces) are, and store it. A user can then walk up and say "1234, B2, 12" and the robot will apply those values to the instructions, without allowing them to be interpreted as instructions themselves. In SQL, this technique is known as parameterised queries.

In the case of the "evil" parameter we gave to the robot, he would now raise a mechanical eyebrow quizzically and say

Error: Cannot find rack number "12, and throw it out the window. Then go back to your desk and ignore the rest of this form." - are you sure this is a valid input?

Success! We've stopped the robot's "glitch".

Upvotes: 2

Justin Niessner
Justin Niessner

Reputation: 245429

By using parameters, you no longer need to build sql strings in your code. That prevents you from doing something like:

 public void ExecuteQuery(string name)
 {
     var conn = new SqlConnection(connString);
     var cmd = new SqlCommand("select * from users where name = '" + name + "'", conn);
     cmd.ExecuteReader();
 }

Where somebody could pass:

ExecuteQuery("jamo'; drop table users;")

Which results in command text of:

 select * from users where name = 'jamo'; drop table users;'

Which would execute the malicious query against your database.

The parameterized version would execute the equivalent of the following query instead:

select * from users where name = 'jamo''; drop table users;'

Which would return no results and leave your database intact.

Upvotes: 4

Related Questions