“Your Code Has a SQL Injection!” | Code Cop #007
- November 5, 2023
- Posted by: MainInstructor
- Category: .Net C C# Go Software Engineering SQL Web Development
Video Title: “Your Code Has a SQL Injection!” | Code Cop #007
Hello everybody I’m Nick and welcome to another episode of Code cope the series where we go over advice presented as good advice and best practices on places like Twitter LinkedIn or blog posts that actually is absolute terrible advice that shouldn’t be followed but unfortunately it is promoted because
Specifically in places like LinkedIn as long as you get like a couple of comments and two likes LinkedIn would promote this to everybody now today’s piece of advice comes from LinkedIn and it is insane to me because it is coming from a very highly followed individual
And it has been well liked and well promoted as yeah that’s what you should be doing that is right advice and I find it very problematic because not only is it bad advice on a topic that is misunderstood anyway SQL injection but it also doesn’t present the real problem
Or the real sort of best solution so in this video we’re going to take a look in all that I’m going to show you what the problem is how SQL injection actually works and you’re going to be able to understand that as part of this video as
Well and what you should really be doing if you like the content and you want to see more make sure you subscribe and more training check out my courses on doet tr.com so let me show you the advice first and before I show you the advice I want to remind you this piece
Of advice got like a, likes on LinkedIn and many comments and many reshares like this was one of the most shared advices for C content and I find it insane now let’s start with the actual code presented and then I’m going to go into the context that is also provided in
Text form so preventing SQL injection attack do not do this over here where we go and get the newsletter based on its ID from the database so we can see that Dapper is being used here Dynamic parameters a query and then the parameters are pass we get a database
Connection and we return that what you should be doing instead is you should be using a parameterized query over here with the add symbol not string concatenation or interpolation which is fair for daer and also verbatim strings for some reason and then pass the parameters and then that is good there
Is no potential for Cal injection now the solution to the problem even though technically if you shift it a bit is correct doesn’t really point out the actual problem this is not problematic I mean technically it is but this is not SQL injectable what you have over here
And we’re going to prove that as part of this video now in terms of context this is what we have so how to prevent SQL injection attack in D applications SQL injection is a Cyber attack and by the way if you’re not a security expert or you don’t know what you’re talking about
Don’t try to provide security advice like come on anyway proper input validation is crucial for prevention which technically it kind of makes sense and you know it says you should use fluent validation okay that’s irrelevant for the validating inputs but actually SQL injection is preventable even without parameter validation so that point even
Though it technically could make sense shouldn’t be sort of advice to help you prevent SQL injection it’s just the cherry on top in our doet API we can avoid this by using parameterized queries or store procedures yeah okay technically both through First Co snippet is displaying the wrong approach
Because hackers can take advantage and do equal injection how you don’t show how and I’m going to prove that you can it doesn’t actually matter the second snippet is displaying appropriate ways to deal with parameters fine sure one of the appropriate ways okay but I want to
Comment in some of those points and then I talk about Security in my net newsletter don’t talk about security if you don’t understand what you’re talking about you don’t have to just say something because you want to create content this type of content is dangerous I don’t mind people just
Talking about something that’s har L but when you talk about security at least you have to understand what you’re talking about let me show you what I mean that this is problematic if I go back to the example as you can see we’re dealing with these pieces of code which
I have over here in my ID now before I move on I’d like to let you know that we just launched a brand new course on D train called from Z to hero grpc in.net in that course Arena SCU will teach you everything you need to know from the
Very basics of what GPC is and how it works into some pretty Advanced features and by the end of it you’ll be able to write clean and scalable GPC applications G is one of the most know Technologies especially for an internal service communication and it’s one of the three skills that every net
Developer should have if you’re doing any sort of API development that is rest API grpc and graphql enena has been using grpc for years and teaching it around the world she even has a book on web development in.net so you know you can trust her she’s really really good
And I know her personally Now to celebrate the launch the first 200 of you can use discount code gpc2 at checkout to get 20% off your purchase so use a link in the description apply the discount and the cost is yours to keep now back to the video so I already
Created this example over here what I have is a real database running in Docker which we can see over here I have the newsletter table running postest and if I click on this I have two newsletters the hello world and the goodbye world one by Nick chaps one by
Chap nus now if I go back to my code you can see that I can get a newsletter by its ID and I can have a database connection exactly as that user had it basically the connect method that they were using was an open a sync to a new
Database connection so if I run this and I say go ahead and get the first news leer I’m going to get it from the database and if I say get two I’m going to get two and if I say get three I’m going to get nothing because singular or
Default so you don’t actually see anything cool nothing wrong with this now a few things going on here first this ID parameter which by the way is in the example just to remind you over here it is here these Dynamic parameters they aren’t actually used anywhere because your string interpolating or sorry
Concatenating that parameter so these parameters over here can go byby that’s sort of irrelevant but you should understand that it’s not part of the example so at least use an example of code that is decent and as you can see it still works without me having to provide that parameter it doesn’t make
Any difference now another point to this example is that because this is an integer as we can see in the example as well over here you don’t actually need single quotes so I could go ahead and delete the single quotes and this would just work fine in fact I could turn this
Into string interpolation and this would still work fine and in fact I can go ahead and run it and as you’re going to see I’m going to get the user back from the sake of argument I’m going to revert it and use it like this as it was before
And of course the end is not needed but I could use interpolation and by the way it would be technically bad to use interpolation I’m not saying that doing it that way is the correct way you should actually parameterize your queries what I’m saying here is that because the argument we’re accepting
Here is an integer we can’t really have a vulnerability with SQL injection again I want to remind you this is what the example is saying get an integer pass it down as a string tation and here we have it passed as a string that is converted both of the approaches will work and
Just for the sake of argument I will revert it just so you say I’m not cheating by changing the example but there is no way that you can actually pass a parameter as an integer and get something out of this that would be exploitable I can change this to I don’t
Know in do mean value if I want to the minimum minus like 2 billion whatever as an integer and this will still work I’m just not going to match any anything any parameter passed here by the user know what of the input will always be an integer because that’s a restriction on
The method that can be a problem here’s where actual SQL injection comes into picture and can be problematic the first thing would be if this method was accepting a string so the ID as a string if I did that and I said one then we have an issue and the real issue of
Course is if the user input for example one over here is user controllable if it is something we control it’s unlikely that this but value would go in here but for the sake of argument let’s just say that this one parameter is user controllable then as you’re going to see
I will still get the news letter here in the console but now this is SQL injectable now to make it more visual to understand why it’s SQL injectable I’m going to change this to return an i numerable of newsletter so I’m going to return newsletters um Again by ID so I’m going
To match one newsletter I’m going to change this to a query a sync over here and then I’m just going to say Json serializer do serialize all the newsletters and if I just say run this I’m still going to get that single newsletter matching that ID all good
Until now however why is this equal injectable well it’s equal injectable because if the user from an API post request or query string parameter is controlling this ID they can pass down anything they want so what I can say for example if I wanted to attack this query
Is okay what if I say that where ID is one and then stop it by adding another single quote and then say or 1 equals 1 or I could do something else I don’t have to say 1 equals 1 which will make everything match by the way and return
Me everything in the database but I could also say or empty string by single quotes equals to individual single quote which will be matched with this one over here returning true returning me everything in the database and if I go ahead and I run this if someone passed
This as a post request or as a query you’re going to I see that both things are now returned from the database and if I had a third one over here for example three I shouldn’t talk about things I don’t understand by author LinkedIn then if I go ahead and commit
That you’re going to see that we’re going to get all three now because now I can get everything in the database by having a SQL injectable query if this didn’t have a single quotes and I had something like this this of course would still be injectable I could go ahead and
Just delete that and change this to 1 and then or 1 equal 1 this would also match anything so one of the ways you can attack this is to get everything but once you have an injectable way into this you can do anything and a very
Dangerous thing that you can do is you can drop a table you can delete data for example I can go here and I can say if I’m a user yes this is one or this is one stop the query here with a semicolon and then say delete from newsletter and
If I go ahead and I run this what you might expect will happen first I’m going to get the first newsletter over here and this will work absolutely fine and when I run it again you’re going to see nothing will match why nothing will match because I have nothing in the
Database because I just run if I go ahead and I refresh this a delete query across everything so that’s why you can have SQL injection and how you can actually have SQL injection and what’s the right approach to this well the right approach is to parameterize it so
If I go ahead and just quickly recreate all the columns over here there we go both created then I can go back and I can say as the example shows use a parameter in this case called ID I don’t need the dynamic parameters approach you can have it if you’re doing programmatic
Things but in my case I don’t need it all I can say here is pass the query and then a new object ID equals ID and then this ID will match that ID parameter over here and you don’t need a veral string you don’t need the triple quotes
You don’t need anything this will just work fine so even if someone tries to do something funny like this if I go ahead and I say run it it will be matched as a parameter and I’m going to get nothing as a response the operator does not support integer equals text the reason
For that is because of how this will try to be matched if I remove this by the way and I pass it down then will still not work and the reason for that is because well as you can see the arrow says integer is not text and I have an
Integer filled in the column so I would need to change that to an integer now for argument sake even if this was a string in fact I’m going to go ahead and quickly change this integer I’m going to say modify column and change it to a
String which in our case it is text by the way so let’s go ahead and say that here we go then if I go ahead and I run this this will return the string correctly or the newsletter and if I return it back to the bad injected
String and I go ahead and I run it then nothing will be matched of course because the string that will be matched is the whole thing which of course won’t work as always if you want to grab the code and play around with it all of that
Will be in the description down below so now hopefully you understand what the actual problem is how it works and how it can be problematic and don’t follow bad advice blindly but now I want to know from you do you ever have to deal with SQL injection and was it ever a
Production issue leave a comment down below let me know well that’s all I had for your first video thank you very much for watching and as always keep coding
Video Keywords: SQL, Elfocrash,elfo,coding,.netcore,dot net,core,C#,how to code,tutorial,development,software engineering,microsoft,microsoft mvp,.net core,nick chapsas,chapsas,dotnet,.net,code cop,code cop nick chapsas,code cop nick,sql injection,sqlinjection,sql injection .net,sql injection c#,sql injection dapper,sql injection ef
-
Sale!
Wireless WIFI Repeater Extender Amplifier Booster 300Mbps
$29.99$14.99 Add to cartWireless WIFI Repeater Extender Amplifier Booster 300Mbps
Categories: Electronics, Wi-Fi Router, Wireless Wi-Fi Extender Tags: 300Mbps, 802.11N, Amplifier, Booster, Extender, mobile wi-fi booster, Remote, WIFI, Wireless, Wireless WIFI, Wireless WIFI Repeater, Wireless WIFI Repeater Extender, Wireless WIFI Repeater Extender Amplifier, Wireless WIFI Repeater Extender Amplifier Booster, Wireless WIFI Repeater Extender Amplifier Booster 300Mbps$29.99$14.99 -
Sale!
Full RGB Light Design Gaming Headset Headphones with Mic
$24.99$14.99 Add to cartFull RGB Light Design Gaming Headset Headphones with Mic
Categories: Electronics, Gaming, Gaming Headsets Tags: Design, Full, Full RGB Light Design Gaming Headset, Full RGB Light Design Gaming Headset Headphones, Full RGB Light Design Gaming Headset Headphones with Mic, Gamer, Gaming, Gaming Headset Headphones, gaming headset wireless, Headphone, Headphones, Headset, Light, Mic, Package, RGB$24.99$14.99 -
Sale!
Wireless BlueTooth Multi-Device Keyboard Mouse Combo
$39.99$19.99 Add to cartWireless BlueTooth Multi-Device Keyboard Mouse Combo
Categories: Electronics, Gaming, Gaming Keyboards, Keyboard Mouse Combos Tags: Combo, Keyboard, keyboard mouse combos, Mouse, MultiDevice, Set, WireKeyboard Mouse Combo, Wireless, Wireless BlueTooth Keyboard Mouse Combo, Wireless BlueTooth Keyboard Mouse Combos, Wireless BlueTooth Multi-Device Keyboard Mouse Combo, Wireless BlueTooth Multi-Device Keyboard Mouse Combos$39.99$19.99 -
Sale!
High Back Leather Executive Adjustable Swivel Gaming Chair with Headrest and Lumbar
$199.99$139.99 Add to cartHigh Back Leather Executive Adjustable Swivel Gaming Chair with Headrest and Lumbar
Categories: Gaming, Gaming Chairs Tags: Adjustable, Chair, computer chairs, Desk, Executive, Gaming, Girl, Headrest, High, High Back Leather Executive Adjustable Swivel Gaming Chair, High Back Leather Executive Adjustable Swivel Gaming Chair with Headrest, High Back Leather Executive Adjustable Swivel Gaming Chair with Headrest and Lumbar, High Back Leather Executive Adjustable Swivel Gaming Chairs, Leather, Lumbar, Office, Racing, Swivel$199.99$139.99 -
Sale!
Professional LED Light Wired Gaming Headphones with Noise Cancelling Microphone
$29.99$19.99 Select optionsProfessional LED Light Wired Gaming Headphones with Noise Cancelling Microphone
SKU: N/A Categories: Electronics, Gaming, Gaming Headsets Tags: Cancelling, Gaming, Gaming Headphones with Noise Cancelling Microphone, gaming headset, Headphones, Headset, LED, Light, Mic, Microphone, Noise, Professional, Professional LED Light Wired Gaming Headphones, Professional LED Light Wired Gaming Headphones with Noise Cancelling Microphone, Wired, Wired Gaming Headphones, Wired Gaming Headphones with Noise Cancelling Microphone$29.99$19.99 -
Sale!
Gaming Desk with LED Lights USB Power Outlets and Charging Ports
$349.99$249.99 Select optionsGaming Desk with LED Lights USB Power Outlets and Charging Ports
SKU: N/A Categories: Computer Desk, Gaming, Gaming Desk Tags: and Charging Ports, Charging, Desk, Desks, Gaming, gaming desk with led lights, Gaming Desks with LED Lights, Home, LED, Lights, Monitor, Office, Outlets, Port, Power, Room, Stand, USB, USB Power Outlets, White, Workstation$349.99$249.99 -
Sale!
Wired Mixed Backlit Anti-Ghosting Gaming Keyboard
$99.99$79.99 Add to cartWired Mixed Backlit Anti-Ghosting Gaming Keyboard
Categories: Electronics, Gaming, Gaming Keyboards Tags: Antighosting, Backlit, Blue, brown, Gaming, Gaming Keyboard, gaming keyboards, gaming keyboards and mouse, Keyboard, Laptop, Switch, Wired, Wired Mixed Backlit Anti-Ghosting Gaming Keyboard, Wired Mixed Backlit Anti-Ghosting Gaming Keyboards, Wired Mixed Backlit Gaming Keyboard$99.99$79.99 -
Sale!
Wireless Bluetooth 5.3 ANC Noise Cancellation Hi-Res Over the Ear Headphones Headset
$119.99$59.99 Add to cartWireless Bluetooth 5.3 ANC Noise Cancellation Hi-Res Over the Ear Headphones Headset
Categories: Electronics, Gaming, Gaming Headsets Tags: 5.3 ANC Noise Cancellation Hi-Res Over the Ear Headphones Headset, ANC, Audio, Bluetooth, Cancellation, Ear, Earphone, gaming headset, Headphones, Headset, Hi-Res Over the Ear Headphones Headset, HiRes, Noise, Wireless, Wireless Bluetooth 5.3 ANC Noise Cancellation Hi-Res Headphones, Wireless Bluetooth 5.3 ANC Noise Cancellation Hi-Res Over the Ear Headphones Headset, Wireless Bluetooth 5.3 ANC Noise Cancellation Hi-Res Over the Ear Headphones Headsets$119.99$59.99 -
Sale!
Wired Sports Gaming Headset Earbuds with Microphone
$19.99$9.99 Select optionsWired Sports Gaming Headset Earbuds with Microphone
SKU: N/A Categories: Gaming, Gaming Headsets Tags: Accessories, Earbud, Earphone, Earphones, Gaming, gaming headset with microphone, Headphones, Headset, IOS, Microphone, Sports, Wired, Wired Sports Gaming Headset Earbuds, Wired Sports Gaming Headset Earbuds with Microphone, Wired Sports Headset Earbuds$19.99$9.99 -
Sale!
150W Universal Multi USB Fast Charger 16 Port MAX Charging Station
$49.99$29.99 Add to cart150W Universal Multi USB Fast Charger 16 Port MAX Charging Station
Categories: Charging Stations, Electronics Tags: 150W, 150W Charging Station, 150W Universal Multi USB Charging Station, 150W Universal Multi USB Fast Charger 16 Port MAX Charging Station, 150W Universal Multi USB Fast Charger 16 Port MAX Charging Stations, 150W Universal Multi USB MAX Charging Station, 16 Port MAX Charging Station, 3.5A, Charger, Charging, Fast, laptop charging stations, Max, Multi, Port, Stand, Station, Universal, USB$49.99$29.99
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).
The advice was not only terrible because of the parameter type, but because it promoted inline sql in c# code. Although you can use inline sql in c# code, I fail to understand why people as smart as you Nick promote it.
Writing Sql in stored procedures gives you, the programmer, control and influence over the query optimiser. For simply queries (1 to 3 table joins), an ORM can do a decent job, but when you are dealing with a much more complex query (think 10 to 20 table joins) then there is no chance that an ORM can produce a decent query.
When dealing with such a complex query, you want to have a sub-query using only those tables that perform the filter, then in an outer query add in those other tables that just add *fluff*. This allows multiple indexes to be used on a single table and can greatly improve the performance of the query.
I have recently (past 6 months) improved the performance of a number of stored procedures this way by 90% – yes, the cost (logical reads, cpu, time) of the manually created stored procedure is 10% of the time of the original!
Lmao ppl still saying sequel
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!
To be honest I don't really like these Code Cop videos. Usually they aren't as interesting as other videos from you Nick. Thanks for showing us how exactly SQL injection works, but generally the video can be shortened to something like "Hey, you have an int parameter in the method, so SQL injection isn't really possible. And btw you shouldn't use DynamicParameters is such simple cases". Without hate, just expressing my thoughts. Maybe somebody will agree with me and it will be helpful for you. Cheers!
I was laughing even before finishing the video because the guy who posted that one on LinkedIn knows nothing about SQL injection.
I'm not sure about C#, but in some languages (like Java) the string parameterization is dependent on the database driver. Meaning that in some (rare) cases the driver could just concat the string together before sending it to the db, rather than going through the prepared statement step.
My point is that proper prepared statement behavior is not NECESSARILY guaranteed. While using prepared/parameterized statements makes code more robust against SQL Injection, it may not always be sufficient.
So you should also always check / cast your inputs as well. (Casting to an int, for example is great protection)
I would also show what kind of request ultimately comes to Postgres in the Docker container, using the example of SQL injection (with try to delete data from table)
Smirked reading comment section. Parametrization is not a silver bullet and most devs blindly rely on it without a second thought. Ofc it's fine and dandy in a simple query inside a value of where clause. Try it on a column name or table name or custom producere name/arguments, nested subqueries etc. Alot of db-drivers/orm/you-name-it can't properly handle such scenarios thus leading to SQLi.
Don't get me wrong you definitely should use parametrization but still pay attention at your exact query. Remember parametrization purpose is not to create dynamic queries. It's to separate query and data.
Sqlinjection with integers, nice
What ide is this? Or is it a visual studio theme
the example is pretty bad, but the given advice to use parameterization is still valid in my opinion. I've seen too many young developers not adopting query parameterization, which on one hand is a huge benefit for database performance. Also imagine, if the app changes later and there will be a second method retrieving the newsletters by text search. What will a developer do? reinvent the wheel? No, he will just copy/paste the existing method and change the argument. boom!
The real question is: Who does direct SQL querying in their C# application in 2023? 🤣
Chap Nicksas 🤣🤣
Nick the code above would have pinged on a tool like checkmarx due to not using parameters and the input is not validated though technically int wont inject unwanted input
The original advice is more out of time than out of place. Back in the day, when PHP 4 was the thing, and PHP 5 was new, the legacy MySql library had no support for parameters. Nada. You had to make input validation. Not semantic, syntactic validation. And sanitisation. Because you had only string concatenation at your hands. Which was a pain in the ass because of the plenty of encodings valid all around http, blacklisting is a nightmare.
What are you doing to get your program to start so quick? Mine typically takes a while to build no matter how small the program or how much resources I have on my machine! But as you are changing code and then running, you'd have to go though that build process right? So it would normally be slow…
nobody gonna talk about raw dogging SQL queries rarther than executing a SP?
Oh, yes… at a past company, 15 years ago at this point, we absolutely had issues with SQL injection. Our software's login page could accept and execute sql. The lead software engineer of our company demonstrated how to reset passwords for all our users' in one our staging environments. Well, he THOUGHT he was on one of the staging environments. At some point he got bounced to production and didn't realize it. So, yea… that was a fun day for support 😂 As far as we knew, WE were the biggest threat to our production security, so I guess that's good at least.
Security newsletter!!