It's really easy if you can express the entire query in a single string,
but what if you need to compose a string from various expressions?
How you do that impacts the security of the SQL you're sending to the database.
So,
I think it's important to take a little time to be sure you're aware
of the various paths and what their effect is.
But right up front,
I want to make sure you know that the golden rule for sending raw SQL is to
never create strings with parameters embedded directly in them.
That's how somebody with bad intentions can get SQL injection
attacks passed through to the database,
and you definitely want EF Core to send parameterized queries.
You'll find a link in the resources at the end of this module,
if you want to learn more about SQL injection attacks.
I also think it's important before I dive into these queries to make sure
you're familiar with the different ways you can build up your strings so that
when I do show you the variations in SQL strings,
you'll understand what I'm referring to.
I'll start with a typical way of combining strings and
expressions using concatenation; in other words,
just adding the info together.
I've got a variable, name, with the value,
Josie, and then I'm combining a string, Happy Birthday,
with the variable, and the result is Happy Birthday,
Josie.
Formatted strings contain numeric placeholders,
and the placeholders are surrounded by curly braces.
Then, after the string, you just list the data,
other strings or expressions that result in strings.
The formatted string knows to cast the int to a string
when it's building up the result, and so we have Happy 6 Birthday,
Josie.
And yeah,
I'd have to add a bit of additional logic to turn those
numbers into adjectives like 3rd and 5th.
Finally, we come to interpolated strings,
and interpolated strings just squish down the formatted string,
so instead of having placeholders and then following that up with
the actual values or expressions that go in there,
we put those expressions directly into the curly braces.
You do have to preface the entire string,
and that's before the quote sign, with a dollar sign.
That's a signal to C# that this is an interpolated string.
So now we can look at expressing raw queries with EF Core that have parameters
using these various ways and seeing the cause and effect.
Because it's so easy to accidentally send unsafe SQL,
I'm going to show you a variety of queries and how they work out,
and notice that I've named these methods Safe and Unsafe,
so you can refer to this code later.
Safe queries will send parameterized commands.
The Unsafe methods will not parameterize the queries,
and thereby allow SQL injection attacks through.
First,
are some queries using FromSqlRaw, the method we already looked at. Any
SQL that you build with concatenation will be unsafe because it will
always put the parameters directly in the SQL.
So, that's an easy rule, don't ever use concatenation for parameters
with raw SQL. Formatted strings will not work, well,
they'll work, but they will be unsafe if you build them in advance.
Here's an example where I'm using the String.Format command to express a
formatted string, but the String.Format command will return a string, and
that string will have the parameters embedded.
So the variable is now just a plain string, and when I
pass that into FromSqlRaw, again, we've got an unsafe query.
But, if you put the formatted string directly into the from raw SQL
method, and note that I'm not using the String.Format command, that was
the problem, that's what will turn it into a string, but if you just pass
all the parameters for a formatted string,
then the FromSqlRaw method knows what to do, and the
resulting command will be parameterized and safe.
Before I go further,
I do want to point out something that I'm using in these
queries that you may not have seen before, at least I know
I haven't shown it to you.
I'm using a method called TagWith in the query. TagWith
will add commented text to your commands.
The comments will show up in your logs, in your
profilers, or wherever you're looking at that.
And because I've got so many different methods and queries here,
I wanted to make sure that reading the logs,
it made it clear which queries I was sending in.
Now we'll finally take a look at using interpolated strings. In
this method, I am using an interpolated string,
but I'm still using the FromSqlRaw method.
And, again,
I'm pre‑creating that string in the SQL variable, and so it gets
preprocessed, and the variable that I'm passing in is now a plain, old
string, and the parameter is embedded directly into it.
The compiler doesn't care about this, and the command that it sends
to the database won't be parameterized, and bam, there's the
possibility of another SQL injection attack.
See why logging or profiling your database is so important?
Remember the formatted string worked as a parameter of
FromSqlRaw, what about the interpolated string?
Nope,
it's still unsafe. FromSqlRaw doesn't know how to create
parameterized queries from interpolated strings, it knows how to do
it from formatted strings, but not interpolated.
So it looks like you're doing the right thing, but again,
this is unsafe and will allow an injection attack through.
So, finally,
let's use an interpolated string with the FromSQL method. Passing my
interpolated string directly in as the parameter of that method
finally results in a parameterized query, which is safe, and this is
why we have to have a second method.
There was no way for one method to be able to handle
all the different variations here.
There are two more things I want you to be aware of about the FromSql
method where the compiler will catch two problems for you.
The method accepts one, and only one, parameter, and it can't
be a string, it has to be a for string.
Let's see what that looks like.
What if you pre‑create the interpolated string? Well,
again,
the resulting variable is now just a plain, old string. But it's
still safe, it's so safe because the FromSql method won't take a
string as a parameter, so it won't even compile, it complains that
it wants a formattable string.
But wait a minute, if it wants a formattable string,
why not just pass in the other formattable string expression?
Nope,
that won't work because the method only takes one parameter,
and the formattable string expression has multiple parameters,
so this won't even compile either.
Well,
that was a lot of introspection into using raw SQL in EF
Core with parameters, but it's so important to be sure that
if you're passing in raw SQL,
you don't lose the security that EF Core can give
you with parameterized commands.
And in the end,
there are really only two safe and compilable ways that I showed you, but
I think it was really important to understand those other syntaxes where
you would be creating unsafe SQL, and it was also nice to know about
syntax that wouldn't even compile.
Unfortunately,
IntelliSense in the compiler could lead you astray here, and
that happens a lot when developers want to just use their
tools without learning how they work.
But you're here taking this course, and you're
learning, and that makes me so happy.
And, hopefully you've also got someone on your team who's got your back
and can check to make sure you're not accidentally allowing injection attacks through as you're growing your skills.