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:
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.
Can parameterized statement stop all SQL injection? Yes, as long as your database driver offers a placeholder for the every possible SQL literal.
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.
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.
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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With