Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Why does using parameterized queries or entity framework prevent sql injection?

I've got a good grasp on SQL injection. It's when a SQL query that is supposed to be something like

SELECT FirstName, LastName 
FROM Customers 
WHERE CustomerId = @valueFromApplication

Gets turned into a query like

SELECT FirstName, LastName 
FROM Customers 
WHERE CustomerId = '' ; DROP DATABASE Foo --

When the user inserts a malicious value into your app, website, client, whatever.. I'm also aware that instead of just dropping the DB the attacker can try to discover the names of tables and get info out of them.

I also know some things that help prevent this are:

  1. Using stored procedures which take parameters (SQL Server)
  2. Using parametrized SQL queries
  3. Using Entity Framework / LINQ to Entities (C#, maybe F#?)

How do these things actually prevent SQL injection from occurring? Why can't the attacker just pass the same malicious value into whatever input he or she is already using and have the same result.

like image 620
Josh R Avatar asked Apr 23 '14 22:04

Josh R


People also ask

Does parameterized queries prevent SQL injection?

Can parameterized statement stop all SQL injection? Yes, as long as your database driver offers a placeholder for the every possible SQL literal.

How does Entity Framework prevent SQL injection?

To avoid the risk of SQL injection, you should never combine user input with Entity SQL command text. Entity SQL queries accept parameters everywhere that literals are accepted. You should use parameterized queries instead of injecting literals from an external agent directly into the query.

Is Entity Framework safe against SQL injection?

Generally speaking, Entity Framework uses LINQ-to-Entities parametrized queries, and it is not susceptible to traditional SQL Injection attacks. However, Entity Framework does allow for the use of raw SQL queries when working with a relational database, introducing the risk of writing injectable queries.

Why are parameterized SQL queries important for secure applications?

Parameterized queries force the developer to first define all the SQL code, and then pass in each parameter to the query later. This coding style allows the database to distinguish between code and data, regardless of what user input is supplied.


2 Answers

Your first example is parameterised and is not vulnerable to SQL injection.

Parameterised queries aren't simply replaced by the server with values (like you might do manually replacing @var with value). They are sent and received exactly as you sent it.. with @valueFromApplication.

The server will parse the query.. and when it gets to a variable it will look up the value supplied. If that value is '' ; DROP DATABASE Foo --.. then that becomes the value it uses. It doesn't parse that.. it just uses it as text/number/whatever type it is.

To add about Entity Framework, it internally uses Parameterised query so it is also SQL injection safe.

like image 182
Simon Whitehead Avatar answered Sep 27 '22 18:09

Simon Whitehead


Parameters are not simply replaced in-line into the SQL - they are sent separately from the query to the SQL Server.

So, SQL Server gets something like:

Query:
   SELECT FirstName, LastName FROM Customers WHERE CustomerId = ?
Parameter 1:
   '' ; DROP DATABASE Foo --

And therefore it compiles a query that checks for a customer whose CustomerId is literally equal to '' ; DROP DATABASE Foo --. The parameter value is never executed as SQL.

like image 28
Blorgbeard Avatar answered Sep 27 '22 17:09

Blorgbeard