To protect against SQL injection attacks,
you should not use the user input value
that you might be using in your queries
just like that.
Instead, just as before,
don't take the user input like that,
escape it instead.
Use the escape user input,
which simply means if you're looking for an author name,
and there are words like "select" or "drop"
in that author name,
it's probably not the name of the author,
and you want to get rid of the overall command
or of these malicious parts.
And the good thing here is that
SQL injection attacks are so common,
that most SQL packages,
which you might be using,
no matter if it's for node
or for other programming languages,
have built in protection.
Here I'm just working around that built in protection.
You might recall for example, that earlier in the course,
when I introduced you to SQL
and the MySQL package here,
we didn't build queries like this.
We did not use this template string feature
and inject values into it like this.
Instead, we used question marks
just as I'm doing it's down here
for inserting values.
And we should always use question marks
because if you do,
the values that will be placed for those question marks
will be automatically escaped by the MySQL package.
So if here, I switch from this,
to this with the question mark,
I can go down here and when I send the query,
set my value for the offer like that.
So this is then the value that will be used in place
of this question mark,
but it will be escaped first by the MySQL package.
So if we now save this,
and I quickly recreate this table which I had dropped,
so I recreate this comments table with its ID,
which is auto incrementing,
and with the author field which is a varchar,
and not null,
and the text, which is a text which is not null,
if I recreate this table,
apply this in here,
then you will see that if you go back to your discussion
to local host 3000 slash discussion,
I can, of course, again,
enter values here and that works,
and I can still find all comments
or just a one by Manu,
of course, Manu has no comments yet
so I don't find anything there.
For max it works or for no value it works,
but if I tried that same trick from before again,
if I take that malicious value here
and I insert this, nothing happens.
It just doesn't find any authors for this strange name,
but if I then thereafter again,
find authors without a filter,
I do find my content again.
And in MySQL work bench, if we refresh here,
the table is also still there.
Because now when using this question mark here,
and letting the MySQL package inject the actual value
into the query,
we don't run into that problem.
And actually the MySQL package also has
another protection step,
which I had to disable to show you this example here.
You should always use these question marks
for adding values into your queries,
but in addition to what I did before,
in this database config I gave to you,
is here in this config.
I actually set multiple statements to true.
That is not the default.
The default if you don't set this option,
is that it's false.
And this does what the name implies.
If it's false,
so if we have the default setting,
and you would try to run multiple SQL statements at once,
for example, because you'll try to send this
as an author name,
then even if you did use the approach from before,
without the question mark,
the MySQL package would block it
because it's not willing to execute
more than one SQL statement at once.
And the one SQL statement here would be selecting
that author by name,
this here would already be the second statement.
So you have multiple layers of protection built in
and you shouldn't work around them.
I did it here to make you aware
of this attack pattern though,
so that you don't accidentally run into this
when you have some other use case
where you do want to actually allow multiple statements
and where you might build your query string,
as I showed it to you before,
where you directly inject your values into the string.
Don't do that.
Strongly consider sticking to the default
of having one statement per a query
and use the question marks for marking placeholders
that should be replaced with concrete values,
because then those values are escaped for you
and they offer you protect against SQL injection.