The basics of SQL for Cybersecurity Professionals
HTML-код
- Опубликовано: 4 окт 2024
- Grab Atlas VPN for just $1.83/mo + 3 months extra before the BIG DEAL deal expires: get.atlasvpn.c...
You can find the code to make this database here:
github.com/mcb...
Structured Query Language (SQL) isn't that difficult to learn and and the basics can be grasped in just a few minutes.
Always Use
BEGIN TRANSACTION
ROLLBACK TRANSACTION
Before updating or deleting data.
For uncensored video, check out my substack at:
ryanmcbeth.sub...
Like my shirts? Get your own at:
www.bunkerbran...
Watch all of my long form videos:
• Military Equipment, Ta...
Twitter:
@ryanmcbeth
Join the conversation:
/ discord
Want to send me something?
Ryan McBeth Productions LLC
8705 Colesville Rd.
Suite 249
Silver Spring, MD 20910
USA
Grab Atlas VPN for just $1.83/mo + 3 months extra before the BIG DEAL deal expires: get.atlasvpn.com/McBeth
You can find the code to make this database here:
github.com/mcbethr/BasicSQL
Structured Query Language (SQL) isn't that difficult to learn and and the basics can be grasped in just a few minutes.
Always Use
BEGIN TRANSACTION
ROLLBACK TRANSACTION
Before updating or deleting data.
For uncensored video, check out my substack at:
ryanmcbeth.substack.com
Like my shirts? Get your own at:
www.bunkerbranding.com/pages/ryan-mcbeth
Watch all of my long form videos:
ruclips.net/p/PLt670_P7pOGmLWZG78JlM-rG2ZrpPziOy
Twitter:
@ryanmcbeth
Join the conversation:
discord.gg/pKuGDHZHrz
Want to send me something?
Ryan McBeth Productions LLC
8705 Colesville Rd.
Suite 249
Silver Spring, MD 20910
USA
Good draft, I'd say the explanation of joining tables probably needs a second pass with a little more detail though. I don't think a layperson would clearly understand it.
Congrats on the sponsorship, Ryan! Glad to see your channel growing.
Great video. Heads up though, someone is pretending to be you, doing the whole "your comment wins a prize, contact me on telegram...".
I'm surprised he didn't get sponsored by the Nazi party of Ukraine
Came for the OSINT, stayed for the cybersecurity tutorials.
LOL, I came for the programming videos, but stayed for the OSINT.
As a weathered SQL developer I want to say that I've never encountered any better explanation of the SQL basics! I enjoy every one of your videos.
Red though a whole ass SQL Book and it wasn’t that simplified
Agreed
Thanks, Ryan. I've been studying SQL in my free time. Great overview.
One important keyword that you missed is AND. For those that need it, AND is used to chain together conditions that modify a statement, usually used to combine multiple “=“, LIKE, and JOIN clauses. An example from his table would be “SELECT * FROM employee WHERE Terminated = 0 AND Salary > 100000”, which would return all currently employed employees with a salary greater than 100000.
Great video Ryan! Very concise, and I learned something new, the transaction commands! Don’t think I’ve ever been in a situation where they would have saved me, but I’m young and have plenty of time to make catastrophic mistakes.
Do more of these videos please. I'm trying to break into cybersecurity. Studying my ass off, and you're good at explaining things.
Damn I never thought you'd be teaching in a video what I learned back when I was in college for programming.
Absolutely - wrapping SQL in a transaction should be the first thing newbies learn about! 👍 🇦🇺
The second thing is privileges/user accounts. Typical bad example from 90s is web application written in PHP storing data into MySQL database, using raw queries. The problem is, if you have an SQL injection vulnerability in your PHP code (and you WILL HAVE an SQL injection vulnerability in your PHP code), and the PHP can execute ANY query against that database server, it could also execute an injected malicious SQL query. To mitigate this, first, use prepared statements or functions instead of hand assembling the SQL query, second (prevents injections), connect from PHP to MySQL using account with limited privileges, such as ability to to only execute stored functions or not able to query db schema (prevents disasters even in case of injection).
Those two commands helped prevent me from throwing my career in the dumpster fire countless times. MSSQL for the win.
Came here for the tech stuff a year or two ago, and ended up with a bunch of military stuff too. My two favorite topics (and the two go hand in hand)
Begin Tran and rollback tran, You made my heart sing
As a longtime DB professional that has moved to Management. I’m very happy to see a good overview.
Be very careful with doing Begin/Rollback in production - on a very large dataset this could cause locking and the DBA’s may strongly dislike you depending on isolation and locking.
Suggestion that helps as well is create a select statement for what you want to remove. This will allow you to review what you are going to remove (again harder with larger data sets). After you are happy with the return set, convert the select to a delete. (Set isolation to dirty read might be warranted as well to avoid locking with larger data sets)
One more expansion of your advice would be all IT employees should know a few core things: basic security, excel, SQL and …
Only 3 views? I found this from your substack. Like all of your videos, it looks well done but I have no knowledge or need to learn this.
he uploaded it six minutes ago
@@QuizmasterLaw which is why I'm tripping out. Dudes comment says from a day ago.
@@QuizmasterLaw hahaha
@@michaelcrossley4716 upload and publish are different, maybe he somehow distributed earlier as unpublished e.g. on his substack
I'm a wizard
In actuality, it was unlisted and I found it in a playlist after noticing it from a picture posted to his substack.
I let out an audible chuckle when I saw the BEGIN/ROLLBACK TRANSACTION at the start of your vid. Your query window looked familiar. As a DBA for Oracle & SQL Server for several decades, with the exception of an occasional SELECT, it's STILL the first thing I type when working with data & DDL. Well Done.
This goes way over my head but I still gave you a thumbs up! Keep up the great work!
I'm a highly qualified IT professional and you are a better teacher than 95% of the IT channels on RUclips.
Good to hear, I've been studying for a few years so good to hear Ryan is a great teacher
Good info. I too am a military guy, and a software guy. SQL is the greatest! I learned it from Dr Don Chamberlin, when he taught a grad course on databases in 1979 (@ SJSU). He created SQL while at IBM.
This is why i love your channel, the military and the software, its a blend made in heaven
This is why I love you Ryan! I'm totally into military history and current military affairs. And I'm an IT consultant with roles touching on network security, SQL, cloud and more.
Even if I weren't working in the IT field already I'd happily watch your videos. Fantasticly presented! You could be a CBT Nuggets or Pluralsight instructor
Hi. I'm going to Charleston for six months for cybersecurity training! I'm super excited!
Your IT videos make me miss IT.
I just hated fighting with upper management to make their network safer and run better. Then get told no then something happens then I said this is what I was talking about. Then fix it and 3 months later they want to go back to what it was. Then break-fix and repeat.
Having only 2 guys doing IT for 250 Employees and 6 sites in 3 states.
I couldn't take the stress
LoL
My Boss quit for 3 months then it was just me. He started working for them as a subcontractor and working at his new job. His new job sucked and then he came back. Then the metal prices tanked and the company laid off 75% of the workforce. Told us we had to take a 40% pay cut or get laid off. The last time they did a temp pay cut thing it became permanent. Got laid off and then asked to come 4 months later at a 25% pay cut. It was my last IT job I miss the work but not the stress.
I should have not gotten into the recycling industry. It is a shame how China killed it in the USA. Bought up most of the refiners and then made it so that we had to send the metal to them at a much lower price. It is going to bite us in the butt if we go to war with them.
Hey Ryan thanks for this video! Been watching your Ukraine stuff for a while but this is interesting for me as I have just started my first class on SQL & Databases.
Along with transactions, I’d recommend using a SELECT count(1) to count how many rows exist before and after the DELETE to see how many rows the delete touches. When you’re looking at dozens of rows, it’s hard to see that only a few rows have been touched. It’s good to let the db count this stuff for you.
Or just use a proper software that will tell you how many was affected...
@@ShadowebEB Which might screw up for whatever reason, too, if it just simulates the transaction and you rely on it!
Btw. depending on the actual dialect, you might also be able to just use `select @@ROWCOUNT` for the row count of the last statement, saving you any math etc. (assuming you've got only one action to do).
I used to avoid the videos directly related to my job..... But now that I get to have beautifully orated stories in-between, well I just might have to go be productive today!!
Good mix of military and cs info
Awesome, please do more of these tech videos!
I use both SQL and HANA in my job. Every day is a school day!
Truth. Rollback has saved me a lot of heartache.
I'm learning Databases this semester and most of this information is pretty basic. But I was abit confused with the inner join etc and thank you for giving me some clarification :). Your explanation was the most understandable for me.
This is just such a great channel .. Went to comp-sci school at Quantico Virginia where they taught us Ada. USMC 89-97
I'm so glad I managed to find this channel! As a former 19K who is studying cybersecurity, it feels like this channel was made just for me. Keep up the amazing work!
LOL, select employee "Circleback LeDouche." 👍🏻
I do not know the first thing about programming/developing or computer code, but I found this video so interesting and I kinda understood everything you was explaining,
I enjoy ALL your content Ryan,
Thank you
I've seen IT RUclips "GURUS" who can't teach to save their lives. You could teach professionally. Trust me, I have 20 years in the field and was a highly paid consultant for 12 years. You are a natural teacher! Cool!
I love sql. Always important to have checkpoints and backups.
As a Data Engineer, I endorse this message!
One of the best sponsor presentations I've seen
Nice summary. Not to take away from the value of rollbacks (and transactions), the likely second-most important idea (and already identified below) is to never delete, but rather mark as invisible so that you can always recall data.
very helpful start learning this
Thank you for this, I appreciate you sharing this. I’m interested in learning this industry and glad you are another person I can support while learning.
A key to setting up a DB is to go into the master properties and select a memory limit. That way, if you accidentally create a forever loop, the whole system doesn’t crash. You can just stop and start the MSSQL service for that DB!
Next: RQL and AQL 😁 Maybe a video on R would be cool, and how FORTRAN 77 is different to most common languages. Then a quick tour of LISP 😂
Great video. Can't wait for the SeQueL.
6:23 "I can make a video just on joins" yes please.
Lolz, right when there’s a ton of 1:1 tutorials. Perfect timing.
didn't know this, thanks Ryan
Probably should include (NOLOCK) for TSQL/MSSQL if they are querying PROD data. Less likely to do a table lock and slow down database response times.
Love these cyber security videos
Love it! Pls make more!!!!
These videos are awesome, Thanks!
Another great video. Thanks
I can’t believe you’re talking about something that I know about
Thanks for another great video boss!
Thank you!
I would say it is 3 commands: begin transaction, commit transaction, and rollback transaction. Overall, good overview 👍
I was missing the programming videos!
Great info
Man your awesome.. I watch u because of the military stuff u do.. but I hit this video.. and that poem u did about your sponsor..is hilarious 😂...just a question I wish u could do somthing like a mini series about ww2.. I know u could come up with something..I know it would amazing
Normalisation ~~~ write maximum information using minimum data
Here a stored procedure developer seen SQL, only with Ryan
AS a data architect I can say that if your database was modeled properly, especially considering your use of BEGIN TRANSACTION can easily create a zombie trax if used by unskilled developers, you wouldn't need to open a trax to begin with. Opening transactions in Ad Hoc queries is inherently dangerous and shouldn't be done at all if you don't know about how isolation levels work. PLEASE do not do that on any Production DB.
💯 but I think Ryan was doing a quick and dirty video.
Can you elaborate a bit more on why is it dangerous?
@@ivanbravomunoz1305 Let's say you open a transaction, then perform an action that instantiates a record, page, or tablock (Table Lock). Then a commit failure occurs. Commit failures may not always throw a catchable error... not only do you have a zombie transaction... you have a lock that cannot be dropped until the zombie transaction is discovered and rectified.
@@GoldDinger He was. He was doing an ad hoc deletion. Don't do those in production...
In place of transaction I recommend SELECT * INTO #x From dbo.Whatever Where Terminated = 1 -- Put the soon to be deleted recs into a temp table...
DELETE FROM dbo.Whatever WHERE Terminated = 1
SELECT * FROM dbo.Whatever where Terminated = 1 -- Test... if null set then deletion occurred correctly.
SELECT COUNT(*), Terminated FROM dbo.Whatever GROUP BY Terminated -- This will give a count of all recs based on each terminated value. If there are none with '1', then deletion occurred correctly.
DROP TABLE #x -- Clean up the garbage
@@XMeK agreed and using a temp table is a far better approach.
I’m studying the fundamentals of Cyber Security and this is where I’m stuck. I have to do some exercises and I’m very confused on how to do it.
Do a video just on joins!
ENGAGEMENT
Bloody NoSQL has rekt all my relational DB knowledge!
More sql content pls
this will be cool...
introductions.
your s q l lesson is fair for absolute beginners.
i thought that cyber security types would be more informed.
s q l commands to treat with care start with delete or update.
an option on tables for c y a actions is journaling.
also consider the ramifications of altering a table or tables when.
for some reason a rollback command does not occur.
time sinks using the select command are
those that are nested selects.
or for a cross tab table result.
but overall.
it was cool to walk down my memory lane
It only takes one bad SQL update/delete on a production database to teach you the value of transactions :)
I remember a reddit post sometime ago saying "Accidentally destroyed production database on first day of a job, and was told to leave, on top of this i was told by the CTO that they need to get legal involved, how screwed am i?" 😄
Try/catch good practice
I wish I didn't have a VPN already. I'd so sign up just for that romantic poem. RAH!!
A State Department-backed program using open source data and satellite imagery to document potential Russian war crimes in Ukraine is continuing to add partners to the technology-driven effort, while the agency is considering how such an approach could be applied more broadly in the future.
The Conflict Observatory is intended to serve as a “central hub to capture, analyze, and make widely available evidence of Russia-perpetrated war crimes and other atrocities in Ukraine,” according to the organization’s website.
SQL like JS. Everyone in IT will eventually learn it.
And it needed for “codeless” (hate this term) even more. Because ~50% of “codeless” based on simplified SQL.
Good accounting practices: nothing gets erased.
You should use alias.
Is this an accidental upload of wrong version of the video or something? Why a draft?
@ryan mcbeth: can you please explain what is the difference between something like m777 and himars. I understand himars are a rocket artillery system whereas m777 shoots shells. But wanted to understand in what scenario will someone use m777 as against himars and vice versa
Except this syntax is specific to some SQL database. However, the concept of transaction where you can rollback to when the transaction started is the most important part. In Oracle, you are always in a transaction, so no "begin transaction" but you can do a partial rollback to a savepoint or do a complete rollback. Different databases have different ways of doing transactions.
He's using T-SQL... hence SSMS. 🙂
I love your content, maybe you should open a separate channel for cyber security or software related topics? Just a suggestion, I'm sure you can handle both.
You had me at "rollback transaction". Hope your Valentines Day is bringing you happiness.
Am I correct in seeing your database's name? CrapTech?
Yes. I use Crap Tech a lot.
I think you missed talking about COMMIT for the transaction, someone just learning might not know that is needed just start creating a bunch of open transactions.
This comment needs to be pinned to increase visibility!
Transactions you can rollback are your friend.
I don't pronounce SQL as "sequel", I referred to it by function: "SQueaL".
I have a question, as a military engineer, what is your role in natural disasters?
I have a bit of a odd question I couldn't find the answer to anywhere and in hoping Ryan could do a short on it even though it's not specific to the current conflicts.
In regards to child soldiers what are the rules of engagement? I know children are not ment to be recruited but if they were are there any special protections afforded to them if they were met on a active battlefield?
Why didn't I learn this in school? The subject was Databases, so it should have been covered.
Ok sorta sorry for being a grammar Nazi, but I’m here to help. The irony the you said “general consensus” at 02:48 followed 10 seconds later by saying “eliminating redundancies”. A consensus is general, the phrase “general consensus” is a redundancy. Thanks for all the great videos.
4:17
When it hits too close.
Back when I did databases at University the head of the department, at the beginng of the course briefly went through the history of S.Q.L. One of the things he stressed was how S.Q.L. was a replacement for SEQUEL and one of the quotes, which has stayed with me was from that lecture and which was from one of its designers was:
"S.Q.L. is *not* SEQUEL."
The "Ess-Que-El" prononciation is actually in the formal international standard and if you look at many if not most of the (non-Microsoft) relational database products which use S.Q.L. you'll find they follow the same standard.
It is concerning then that a military man such as yourself would deviate and promote a somewhat -Microsoft- proprietary prononciation. I know the U.S. military sometimes has to fly in civilian technical specialists to fix equipment in the field whereas in the past they could do it themselves, I just didn't know bad the rot had become.
*Edit:* Something else I learned: "There are often many good schemas but only one is right". 😏
If you're doing databases as a University course it may help to figure out _how they_ define 'business process' and 'business data'.
Came here to say the same thing. S-Q-L is how the standard and original specs say that it should be pronounced. It's admittedly mostly just semantics at this point, but for some reason the "sequel" pronunciation has always seemed a bit lazy to me. Either way, I've been working with SQL for 15+ years and I still just sat through a video about basics I learned almost 20 years ago... so, touche.
Currently trying to learn more about SQL queries, any other videos you'd recommend? SF, and thanks for the good videos.
I'd recommend SQL Server Central or Red Gate's Simple Talk for great information to learn how to write good SQL code
The tables are my corn. They keep my house hot.
Im glad you love computers bro im confused
And a poet!
Hey Ryan my question is how do the drone operators get the GPS coordination to the Artillery crew when the drone is usually like half a kilometer to a kilometer away and 200 ft in tha air from the target
Hell yes
When working with SQL Server that has been in production for a while... look in the master-database ;)
In SQL Server the default connection when using SSMS is set to the master database (one of the system databases). Often when not paying attention, you query the master database instead of the database intended... this include when executing queries that add tables to a database. In multiple environments I have found tables in the master database that should not be there (and might have added a table or two myself as well).
Pro tip: don't make changes in production. Unless you have everything tested and reviewed multiple times.
We’re you the lawyer on “Longmire”?
Completely pointless for me to watch, but I watch every video you put out so it had to be done. 😂
But dang you are a Smart Macbeth 💪🏽
I can only do blue colour things 😅
I came for the boom and got bytes