'Your Code Has a SQL Injection!' | Code Cop #007

Hello everybody, I'm Nick, and in this video, I'll show you what SQL Injection actually is and explain why people on LinkedIn shouldn't be talking about security and things they don't understand.

Don't forget to comment, like and subscribe :)

Social Media:

#csharp #dotnet

[kaizer-]

Getting programming advice from LinkedIn is like getting your news from Facebook.

[CryShana]

You can almost _feel_ the anger emanating from Nick. I can relate.

[regiondeltas]

Well, I' only a few minutes in and I've just seen the code snippet and I look forward to seeing how a C# Integer can be used for an injection I love what you're doing with these videos, sick of seeing authoritatively presented nonsense by people who clearly don't have the first clue about what they're talking about. They're clearly just regurgitating things they've read without understanding

[SerafimMakris]

One of the biggest problems with these posts on social media is that they confuse beginner programmers, and as a result, they don't know what is right and what is not. Because a beginner who sees a post on LinkedIn assumes it is correct since they believe it is on a professional platform and no one would write nonsense there. In the past, we had it with forums and blogs, now with posts on LinkedIn and newsletters. Very good work, keep it up.

[davideglass]

There is actually a way to get the int version to be injectable, though it is much harder and requires access to the machine running the code. If you can set the culture settings, for example: = "1' OR 1=1 --";
Then pass in a negative number, that would output "SELECT * FROM Newsletter WHERE Id = '1' OR 1=1 --5'"

[andrewjosephsaid]

I remember Jon Skeet's blog post "The BobbyTables Culture" where he created a contrived example where integers could cause SQL injection.

[MoZaKx]

well, I believe that the guy who created this example simply forgot to change int to string, and that his idea was just to say to people to use parameters. But I agree, he should be more careful in public places.

[LukaszLech]

Parameterization is important for another reason. In MsSQL (and in different databases it is similar, I suppose): it makes SQL reuse the execution plan, which is way more efficient.

[rauberhotzenplotz]

In Germany, we say "dangerous half-knowledge". Nonetheless i would use a parameterized query even for the integer argument to have a constant query string for various performance reasons. You also have to think about other effects: What if the conversion to string introduces thousands separators or other formatting artifacts? The tip from LinkedIn is not wrong, but the reason is vague.

[marvinjno-baptiste]

Your third db entry made me chuckle, a proper oldskool LOL 😂

[EricSellers-mk]

Coming from back in the day of Classic ASP, SQL injection was a much bigger issue. Plain old ADO was capable of parameterized queries, but old legacy "do it fast" code was often guilty of ugly string concats. As you demonstrated, once C# as a statically typed language came on the scene, some of those concerns were indirectly addressed. Ultimately using parameters to properly escape and sanitize values was the right answer.

Appreciate you going through and demonstrating how the attack actually works. Not everyone has seen the horrors of what an adhoc string concat query can do to your database.

[Isrd]

parametrized queries are very useful, not only for preventing from SQL injections; but it also gives the ability to store them as constants, which reduces memory usage and increase reusability. Saves you headache from type conversions (CLR to DB), and it can also help the DBMS Query Optimizer to create a one query plan, and reuse it for that query. (using string interpolation will create a query plan for every value for the same query, which is something you don't want).

[asdfxyz_randomname]

I really hate that people still think that input sanitation is a real solution to sql injections.

Don't get me wrong, it can be, but it's not a good solution if you want to be sure.

Instead, people should understand that the problem with sql injections is, that the user input can influence the parsing of the query, because adding the user input happens before the parsing of the query.

And the obvious solution to that is, that you invent a way to parse the "unfinished" query before the user input is added to is, and that's exactly what parameterization is.

Now, there are situations when you cannot do that, where the query actually dynamically depends on the user input, but not in a way that can be exploitet by the user.

[willhunt]

as usual an enlightening and useful topic and presentation - great job, Nick.

[clashclan]

I'm not going to skip ads for this series. Thanks, Nick. Love your content.

[CharlesBurnsPrime]

The correct way to handle this situation is NOT merely to parameterize queries, as the LinkedIn author awkwardly stated, it is to use a database account that cannot modify data in the first place. Why should a query ever use a DB user that can alter data?

[shaihulud]

English is not my first language, but I'd say I can speak and understand it quite decently. Nick is often a bit fast for me, but I can usualy keep his pace. Now, the Code Cop series is a whole different story: you immediatelly realise when some "advisors" really piss him off - it's like fast forward, and he's breaking the sound barrier of talking. Even my girlfriend can't keep up with him!
Aside from this: the content is so good - I am a happy subscriber!

[terjeber]

You should also say something about why you want to use parameterized queries even when SQL injection is not possible (with an int parameter)

When you pass a SQL statement to a database it will (have to) compile that SQL statement and create an execution plan for it. If you use string interpolation for the query, the database will not be able to cache the execution plan, so it will do a compile of the SQL statement each time. With a parameterized query the database will cache and re-use the execution plan even when the parameters changes, which means that subsequent queries (even with different parameters) will be significantly more efficient.

[anton_zhernosek]

Thanks, Chap. This was actually IMO the best video in the series because it highlights something very fundamental whereas the first ones were more about syntax

[ChristianHowell]

But after two decades of corporate development, I've noted that managers need an intervention or something... 3rd party frameworks have value in limited scenarios and hiring better people is the correct path, not more mediocre slackers...

I have horror stories about REALLY BAD software management...