EntityFramework Core 2.0 How does the original query prevent SQL injection?

Objective

The next time we talk about EntityFramework Core Foundation, streamlined content, in simple words, hope to learn entityframework core of children's shoes to provide a little help.

EntityFramework Core executes the original query

Execute the original query in EntityFramework core we implemented it with Fromsql, as follows:

            using (varnew  efcoredbcontext ())            {                var orders = context.} Orders                    . Fromsql ("select * FROM dbo. Orders")                    . ToList ();            }    

This is the simplest and without any conditions of the query, and then we look at the conditional query how we should query, as follows:

            using(varContext =NewEfcoredbcontext ()) {                varParameters =Newsqlparameter[] {NewSqlParameter () {parametername ="@p0", Value =1, SqlDbType =System.Data.SqlDbType.Int}}; varOrders =context. Orders. Fromsql ("SELECT * FROM dbo. Orders WHERE Id = @p0", parameters).            ToList (); }

In addition to using parameterized query methods, if we also use String.Format or C # 6.0, the new feature string interpolation is the dollar sign $ to query whether the resulting SQL is still a parameterized query it, let's see.

            using (varnew  efcoredbcontext ())            {                var orders = context.} Orders                    . Fromsql ($"select * FROM dbo.) Orders WHERE Id = {1}")                    . ToList ();            }

From the above we see even if the use of string interpolation is eventually still translated into parameterized SQL. Next, let's look at the string concatenation query method.

            using(varContext =NewEfcoredbcontext ()) {                varSearchString ="Jeffcky"; varBlogs =context. Blogs. Fromsql ("SELECT Id, Name, Createdtime, URL, modifiedtime from dbo. Blogs"+"WHERE Name = '"+ SearchString +"'")                    .            ToList (); }

At this point we can see from the console print that the resulting SQL statement is presented as a string, and that the API that executes the original query on EntityFramework Core 2.0+ is fromsql overloaded, as follows:

 Public Static  This where class;

We use the Fromsql overloaded method above to pass string parameters, while adding a database table operation to the query string to verify that EF core prevents SQL injection.

            using(varContext =NewEfcoredbcontext ()) {                varSearchString ="Jeffcky; DROP TABLE dbo. Blogs;"; varBlogs =context. Blogs. Fromsql ("SELECT Id, Name, Url, Createdtime, modifiedtime from dbo. Blogs"+"WHERE Name = {0}", SearchString).            ToList (); }

After verification you will find the above SQL statements that we injected into the blogs table above, the final table will not be deleted. We see that when string concatenation is not done using overloaded methods, the arguments are presented as strings, which can easily cause SQL injection problems. C # 6.0 introduces a string interpolation (string interpolation) that allows C # expressions to be embedded directly into string literals, providing a good way to build strings at run time. In the EF Core 2.0 feature, special support for the insert string is added to both the Fromsql and Executesqlcommand methods. Support for this new feature allows the use of C # string interpolation in a secure manner. This prevents SQL injection problems that can occur when SQL is built dynamically at run time.

Is this the end of the place? Obviously not, let's take a look at another situation, as follows:

            using (varnew  efcoredbcontext ())            {                var"jeffcky Wang";                 var query = $"select * from Blogs WHERE Name = {author}";                 var blogs = context. Blogs.fromsql (query). ToList ();            }

This syntax error is obvious and we need to enclose the variable in single quotation marks to avoid grammatical errors, as follows:

 using  (var  context = new   Efcoredbcontext ()) { var  author =  jeffcky Wang   " ;  "  select * from Blogs WHERE Name =  ' {author} '    "                 var  blogs = context. Blogs.fromsql (query).            ToList (); }

In these cases, EF core will still execute the plaintext string instead of being parameterized as a variable query. If the variable contains a malicious string, then EF core will not be able to guard against and protect SQL at all. So, if we need to execute raw t-SQL through EF Core, we should use parameterized SQL or take advantage of Formatttablestring,fromsql with two overloads, one of which is to format string parameters through Formatttablestring. The second is the original string and the query parameters can be passed. So the above error, we can use formatttablestring to execute, and after the use of Fromsql method query we can still continue to query, such as the following query posts table data.

            using(varContext =NewEfcoredbcontext ()) {                varSearchString ="Jeffcky Wang"; Formattablestring SQL=$@"SELECT Id, Name, Url, Createdtime, modifiedtime from dbo. Blogs WHERE Name = {searchstring}"; varBlogs =context. Blogs. Fromsql(SQL). Include (d=d.posts).            ToList (); }

By "[email protected]" and using formattablestring overloading or passing parametric variables to prevent SQL injection problems, I hope you find this new feature in EF Core 2.0, and don't forget that it is also used to assume greater responsibility, Because of SQL injection attacks, there is no vulnerability to the code we write.

Summarize

In this section we explain in detail how the original query in EF Core 2.0 can prevent SQL injection problems, streamlined content, simple explanations, and hopefully help you. We'll have a farewell tomorrow.

EntityFramework Core 2.0 How does the original query prevent SQL injection?