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 upfront,
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 and 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 a value Josie.
And then I'm combining a string Happy Birthday with a 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 5 Birthday,
Josie.
And yeah,
I'd have to add a bit of additional logic to turn those
numbers into adjectives like third and fifth.
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 have 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 FromSqlRaw 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 FromSqlInterpolated 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 FromSqlInterpolated 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 formatted 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 FromSqlInterpolated 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 SQL raw interpolated
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 were 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 and 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.