YTread Logo
YTread Logo

SQL Stored Procedures - What They Are, Best Practices, Security, and More...

Jun 07, 2021
Microsoft sequel server can be a scary place, all this technology and terminology surrounding

what

should really be a simple topic, storing and retrieving data, the thing is the corporate world connects Microsoft sequel server with c-sharp a lot , that means if you want to learn c- ready to use in the business world, you should also know the Microsoft sequel. One of the features of the sequel that I talk a lot about are the

stored

procedures

in this video. I'm going to introduce you to

what

a store procedure is and how to build one. but that's not in the most important part of this video.
sql stored procedures   what they are best practices security and more
I'm going to cover why you should use store

procedures

and how

they

can take your database to the next level. For those of you who are new to my channel, my name is. Tim Quarry and my goal is to make learning C-Sharp and other related languages ​​as easy as possible for you. If that sounds good to you, hit the subscribe button. If you want to go further, join my list by clicking the link in the description below. That's where you get insider information and discounts on my courses. Now let's jump straight to the sequel. Here I am using the latest version of the sequel Sir Manta Studio which is version 17.1 vs a 2016 version of the sequel server developer edition.
sql stored procedures   what they are best practices security and more

More Interesting Facts About,

sql stored procedures what they are best practices security and more...

Now all this is free developer edition is free and sequel server management studio is free now guide database here is called samples in sample database we have only one table dbo dot people table in this table, it's really simple as it has eight records and it's just the first name and last name. name of course also an ID so that's all we have in this database right now, just one table, eight really simple records, so let's start by creating a new storage procedure and then we'll talk about what it is exactly. Now I can do it well. here and I'll do it here inside a query window, but if you want, you can expand the programmability and say storage procedures inside here, here you'll see that I have a bunch of system storage procedures, but no actual

stored

procedures.
sql stored procedures   what they are best practices security and more
None that are mine, now the system store procedures come from Microsoft, so these are from Microsoft, as long as there are many of them. These videos aren't going to explain what all of these are, but there are a lot of tools here to help you with your database. There are a lot of tools here that will help you do different things, but for now we'll just focus on the user store creation procedures, so there aren't any right now and we could say right click on the procedure and say new store procedure and create. a template for us in templates something nice.
sql stored procedures   what they are best practices security and more
I especially like this here because it reminds you to comment on your store's procedure, what exactly it does, who creates it and when, that's really helpful, so with this template you can fill in the different parts, though. We're going to start from scratch, so we'll leave it open, we'll just be able to see it and reference it, but now we'll come here to this blank query window. What is a storage procedure? A storage procedure is simply a precompiled piece of code, for example we might normally say something like select star from DB or dot people, which is a pretty standard simple pre query.
If you run, you'll get these eight people, so this is a standard query that would be dynamic or easily changed. now if you put something like that inside your c-sharp code, that can be a problem because what happens if you specify the specified id, first name and last name columns? So this when you specify what is a good thing to do in a star. If you specify these three columns and then add a fourth column for email, you wouldn't have it in your code, so you would have to go to your c-sharp code, open it, make the change, and then recompile if you want. have that in your code, but that's not really green, why shouldn't you have this new code and we'll look at the reasons why you would do a save procedure on this later, but for now let's see how something is done. similar in a stored procedure, so let's get this out of the way.
I should remove our window below and the third test creates the procedure dbo, that's the owner, which by default is dbo. notice here for a table, it's dbo dot people, that's the owner of the table, so dbo dot is a namespace and we'll call it SP the underscore people get everything. Now I'm starting with SP to indicate that it's a stored procedure rather than any prefix like a table, so my way of doing things is to just say SP, but what? what you don't want to do is you don't want to say SP underscore and the reason is because SP underscores are actually a reserved prefix and that's reserved for system level stored procedures, storage procedures that can be accessed from anywhere. database, so we don't do it.
We want to use that prefix, we don't want to step on the toes for a database-specific storage procedure, so the key takeaway is not to start with the SP underscore. SP is fine though, so the SP people stress getting everything and this is how I name my store procedures. I start an SP to indicate the store procedure, then give it the name of the primary table in question and I would say primary because you have two or

more

tables that you may be working with, but in this case it's people. table, so people SP and I have an underscore and what in this case is going to get all the records from the people table, so now I say how to start and then I will do my actual store bag or my actual call, so select ID first name and last name from dbo people and then at the end, I'll say end and that's my create procedure statement.
This creates a stored procedure, so it's the same statement as there, it just wraps it in this create procedure, so let's call this. to actually create the save procedure now if the commands completed successfully and if we refresh here we will see that the DB OSP people underline all the kits now so now if I were to create a new query window in the samples database of test, it could say exe c DB or dot SP, people in our score now say shout because I don't, it says I don't know what the store procedure is, but if I do a Ctrl change it will refresh the items cache that are available and now it says ok, no, that's calling the store procedures a little bit differently than calling the actual code, so instead of selecting, we just say run or execute DB or dot and the name of the procedure store, so let's run this notice and come back. same number of records, same values ​​returned, everything, what is the difference between a storage procedure and a direct call?
Well, this direct call here needs to compile and then run now. Microsoft's sequel server is getting smarter and smarter about how to compile them. quickly and how to remember even a dynamic sequel like this in memory, so it is compiled and called multiple times, but a store procedure is always compiled, so if you are going to call this, it will be a little

more

efficient to call to the store procedure. What it will be to call a dynamic sequel statement the first time and again we'll cover more benefits and drawbacks near the end of this video.
The firm points out some of those that are currently in the process of our store. It may not be so. obvious, but down here in our messages it says eight rows affected, which is actually a second set of data that is returned with this stored procedure. Now we often don't need it, in fact most of the time we don't need it, and even in the template. by default you add this statement right here, set without counting, that's not something we did here, so we're going to add that to not return the number of rows affected, so let's change our storage procedure and say set without counting now I can't create here,

they

already created a store procedure, but I can do a modify if you are a modify procedure.
What are you doing? Change the store procedure to this new value or as a new configuration, so let's run this and now. Come back here and run this again, it returns the eight statements as before and the messages saying command completed successfully, it doesn't give me the count or the number of rows that have been affected, so the calls are a little more efficient. and it helps not to mess up your code when I ask you for a data set and you get a data set and affected rows, so now let's see how to create another store procedure, so I close the template and you know.
I'll close this too, they'll create a new query window. I would like to add some extra lines in my initial window, let's say create procedure DB or dot SP, people underline get by last name, we will set it up first the same way. as I start select ID first name last name from DB or dot people and we're going to end here now I want to limit this by last name so I just say what's the last name I want to limit by and that's where I pass a variable in the last name and then the type so that in the last name n varchar' 50 because in the length of my field, if you don't know how it feels, go to the people columns and see that the last name is a N varchar' 50, so I'm passing a string of characters Unicode variable length they call last name and here I can tell where the field is, so the field in DB Oh duck, people equals last name in last name, which is the value that is passed then this is a variable, so given the name of a variable, I have to start with the ax symbol and then I give it the variable type which in this case is a variable length Unicode character or a string essentially with a maximum length of 50 characters, so now that I have this variable completes.
I can use it to limit my query in this case where the last name is equal in the past in value by trailing semicolon. Let's create that save procedure and now I can call this to be a control change. Now we comment. this one, so don't accidentally hit f5 and run it too exe c DB OSP people underline get by last name, but that says you know what you can't do yet because it expects the last name parameter which wasn't provided now. I could say last name equals like this and that will work, but if I'm going to pass all the parameters now, pass them in order, then I don't have to specify the first name, I can just pass the actual value, so this In this case, it says that there's only one parameter, so the first value passed goes in that parameter, so let's run this and notice that there are now four people, Tim John Chris and Maggie Cory, so I've limited my list by last name, which is passed as a variable now if I make width I can pass more than one variable so for example let's modify this and we will pass the name too they put a comma after my last name and a type and the next line output name varchar '50 now usually if I put first and then last name, but I assume there are already stored procedures in place, in which case I wouldn't want to mess up the order because remember I said it comes in order So if I had said "You know what I want", first name, first name and last name and had already received a call, they were calling a stored procedure that would put Cory in the first name instead of the last name and that would be a problem. let's modify that we're going to run it and now if we do Ctrl Shift it's going to yell at us and say it needs the name parameter so I'm going to say Tim and now if I run this it's not going to do anything different and the reason. why can I use the name variable but it still takes both values, so now I can say where last name equals last name and first name equals first name, we can even do an indentation here, just make it a little nicer, we can alter that and now if we run this, we get a value back, so that's really the basics of a stored procedure.
You have the initial line that creates or modifies and specifies the name and then just below that name you have your variables separated by commas. If you want to make a default value you can just equal the default value then you have your add statement and a start and an end and everything between the start and the end will be executed so the actual statement now means your store procedure it can be an insert delete and update join and select all those things can have an internal stored procedure, in fact several things can happen within your store procedure, you can insert and delete, think of a bank transaction, so you are transferring money from a savings account. to a checking account, in a bank transaction, what would happen is that you would remove it from your savings account and add it to your checking account, all in one transaction, so that could happen within a store procedure, for example so you are not limited to just a call now,here's the other good thing about a store procedure and that is that it's a one time transaction which means you can say this transaction has to go through completely or I'm going to roll it back and when I get any transaction on this in this video but essentially what what you do is start right here and say start trans and they have to complete the transaction and so on.
Now what that does is allow you to say that everything has to happen as one part. Think back to that Bank example, what if stored procedure? You got stuck in the middle of that process, the money was removed from your savings account, but then you got blocked before putting it into your checking account, you wouldn't want your mind to disappear, a store procedure allows you to make that transaction, that atomic level transaction happens all at once. First place, you can do it in code, but the risks of problems are much higher, so another big benefit of a stored procedure, that's really all there is to creating store procedures so far that I've created: we have the get all and Also get by last name, which is a bit of a misnomer now, you have to get by last name, but it asks for last name and first name, but hey, no big deal, that shows us how to do a store procedure, but let's talk of some of the benefits. of performing store procedures and the first bathroom I want to talk about is

security

and this is one that a lot of people miss, so if I have access let's close if I have access to do this, let's run that, so here I have these eight logs.
I just made a selected star of people. Well, what if there's a social

security

number or a credit card number or something more sensitive on one? columns, well if I have access to all the columns in DB ODOT, I can see all those columns. Now a lot of people say that logging into a database is essentially an administrator and they create all their logins with administrative level access, that's a problem now. The next thing I do is say, well, I'll only allow them to have access to certain tables, that's great, but anything they have access to, they usually have full access because if you want to allow the application that they use to insert data, you have to allow them. insert data via login, so essentially if they are using an app, unless you try really hard, still, it's a little iffy if you can do this if they use the app and that user's permissions. talk to the database if they use those same permissions in Sequel Server Management Studio, they can do many things, essentially the same things that the application can do now, the application can say that this record cannot be deleted because of this business rule in the Sequel server.
You manage to make the business rule not exist, therefore you can go ahead and delete that user. Several times I used an app where I couldn't do something because it was locked, but I really needed to do it. so I use those same permissions to go into the sequel server manager studio and go ahead and do that action now. I wasn't being malicious, but those permissions actually gave me more access than the designers originally intended me to have, so that's why I'm not a big fan of giving credentials that have all this access. In fact, I don't like people to even see what tables are available or what data is in those tables.
I don't like them to have read access to my tables now. the question is how to do that and still give them access to use your app and that's why I love store procedures and that's why I default to store procedures first before moving away from them so let's start by creating a new role for the database Mattox is not familiar with database security. I'll try to walk you through it in the simplest terms possible, but the reality is that you don't need to know much about security to actually lock down your database, so let's start with Korea, a database function, now a function is a name for essentially a type of group where you say these permissions are granted to this role and then anyone who has that role has those permissions, so for example a role could be delete users and then that role. has delete access from specific tables then you could say that Tim has the user delete role which means that Tim can delete from these specific tables so let's create a new role for us to use and it's really simple to create a role , the role is now database specific. or at least in this case, we're not going to roleplay for a database role, so create a role and if you look down here under security you'll see roles, you'll see the DB underscore and the name of the data writer.
DD l administrator denies the data writer and everything else, these are the standard roles, so I create a role that I'm going to call D will be access only to stored procedure. I won't do DB underline the same kind of beginning in the SP underscore I just don't want to have that habit and now I also know that this is my role, not a role that Microsoft gave me, so now I can press the Run button and that creates that role if we go down here to the database roles and Press the Refresh button and I will see that I have the database stored procedure.
I extract it from the unstored store. Oops, there we go. I'm going to create a new function and delete that old row, so let's right click and say delete, press OK and now. we have access to just the database stored procedure, so now that I have this function, now I can say grant execution and that function name, so this grants this grant here saying: I'm going to give you something, I'm going to give you access to something in this case. I will give the execute action for this role, which means that this role will now be able to execute.
Remember when we called the stored procedure we said exe C and the name of the stored procedure, so run, that's the permission we're giving it and I ran that and the command completed successfully, so now that services function DD, its only access function allows a user to call store procedures, so now what we need to do is go down to our server, not to the database, now to the users of the database, but to the server users. when our security for server logins we will right click and say new login, we will call this login bill now, if you are not familiar with creating users, basically I have two methods for Korean user, one It's Windows Authentication, which is why I'm currently logged in. under the name Tim, so if I searched for Tim I would find that user.
I can use Windows authentication, which means that if I log in to Windows I will have access to the sequel server. I do not want to do that. I want to create a record in sequel specific meaning using sequel server authentication not Windows so I will say Bill the password I will also say is Bill actually wrong password is wrong login combination and password, but I'm going to uncheck the boxes that enforce the password policy, which means that the password won't expire and I won't have requirements for a request for lowercase letters and numbers and a special character or a certain length, so that I'm not going to do anything. of the ones I go with username and password, both of Bill's come here as server roles, nothing, just let it be a public user mapping to the samples database.
I'm going to check the box that says access only to the database storage procedure, so I apply this. rule for this account so now the invoice will have access only to the store procedure and I hit OK so now let's close so I log out of this server and log back in. I close this and go to reconnect, but I say about Windows login and authentication, which is what I do. Does my Tim login have admin permission for this server? Instead, I don't log in with sequel server authentication. The record is Bill. The password is Bill.
I pressed OK. I go to databases, open a sample database and look at the tables. Note that there are no tables in this list. go to store procedures I see store procedures so let's open a new query window. I'm going to try and trick the system first. I know there is a table called people like star selected from DB ODOT people intellisense doesn't work but no big deal, I press f5 and it says select permission on the people object was denied so I don't have access to read the table of people, the doctor came and saw him, but he left, instead it is DX CC DD OSP, people in a score get all their or all people you could also do exe c DB OSP people get by last name pass in the quarry and in the name of John and I'll run this highlight just that one and then I'll get John Cory back so I can access it.
Run these store procedures, but I don't have access to do things like this database delete or people can't do it, so you can see the power here with just a couple of commands: create a function and grant execution to that role and then applying that role to a user, I have said that this user is Bill can only call store procedures which means Bill can't do anything except if he explicitly said he can do it, so he can't delete rows, you can't insert rows. you can't update rows you can't filter by name only all you can do are the store procedures which I already allowed you to do now the run function allows me to say you can do any store procedure so there is a procedure store in your list bill can do it, so if I have stored procedures said SP people underline delete everything and delete every row from the people table, the bill could run it, so if you want to be more granular instead of granting that grant run two you could specify only certain store procedures that are granted execution so I would say only these store procedures can generate access or this role can access so you can get rid of granular if you want, but from the beginning with two lines of code, create the role and grant execution.
With those two lines of code you've created a rule that locks your database only for what you've allowed people to do, so in my opinion it's a really compelling reason to use store procedures because if you use only store procedures to access update and delete data, then you can lock your database very, very simply, very quickly and yet completely to the point where they can see what tables are available, so, in In my opinion, that is a great benefit. The storage procedures are also faster than writing a star selected so far. like running inside the sequel server, not all the time sometimes they are equally static if you have stored the same select statement, but in general the store procedures are faster.
The other thing that still benefits from store procedures is that they are clearly defined, you know exactly. What will happen at the sequel level because it is defined exactly. What will happen in one more security element that comes along with the store procedures is the reduced possibility of a sequel injection attack. It's not complete security, but it's much safer to use a store procedure when it comes to protecting against fallout injection attacks, so at this point you've seen a lot of benefits of store procedures, not all, but a lot of them, You've also heard my opinion on why I use store procedures. security speed reusability clearly defined protection against fallout from server injection attacks and everything else, but you will hear some drawbacks in storage procedures so don't cover them and at least talk to yourself about them and then you can decide whether the inconveniences are worth it.
So the first drawback is probably the one that affects me the most, the one that I wish had a better history, and that is that the procedures in this store are not under source control. You can put your C-Sharp code in git and then you can say. it's version and it's under source control and you can have different branches, you can have all of this to protect your code and make sure your code is discoverable and make sure you have copies of previous versions of your code and all the other things that that source control provides, but there is not something similar for the sequel server, you don't get it for the sequel server, well at least not really, now there is a paid option that you can do and it is from Red Gate and if you can Allow it, it is an excellent option.
Gate provides the ability to have source control directly into our database, it's really powerful, it's really easy to use and it works in the same flow. Now, the difficult part here is that databases are radically different from code,so some peculiarities arise. with this, that is an option, but it is not cheap, it is a little expensive, another option would be a little cheaper and also has more features. It will follow the path as the Sequel Suite Examine. Sequel Examine will take a different approach to source control and that is, it makes differences between your database and, for example, your development database or your development database and your production database and says here are the changes that have occurred and creates a script for you.
You can actually lock that script in your source control and run it from the Sequel Browse Suite, so it's a little cheaper, has a little more features, does more than just source control, it actually does some comparisons, it can compare data and creates some great statements that allow you to recreate the changes in a new database. The reality is that you should be making live changes to your production server; In fact, you should roughly not be making live changes to your test or even development server, but instead make the changes to your local database and then start that process early to commit your code and replicate it. to your development database and then to your production database, so there are ways to do source control right with Sequel server.
It's not the same and it's not the great story that we have with C-Sharp or other programming languages ​​where source control is just a natural part of the process, but it can be done, it can fit well into your deployment process, and it can work. with the latest third-party tools or even just manually. The next drawback that I hear a lot is one that I have a bit of a fundamental conflict with and that is that this business logic does not belong in a database, the job of databases is to store data, it should not have logic in its base. data and I really don't agree with this and here's why I see these storage procedures down here really like the user interface of my database, so I allow the idea that business logic could be in these procedures of the store.
Now I don't include a ton, but I like the idea of ​​checking the data before it goes into the database. I don't like the idea of ​​trusting anyone when we talk about creating user interfaces in C-Sharp. One thing my tenants always talk about is don't trust the user if they give them a name field that they're going to enter. their age they are not going to play anything they are going to put in an information book whatever is wrong the terrain tries it not necessarily maliciously just because and the users do strange things that I do not expect in the same way why Would I trust a application?
Why would I trust her? Only one application is talking to my database again. Let's rerun with just the store procedures if I didn't have that and someone ran this code right here in SSMS. Works. I could remove or remove all the people from the people table, that doesn't make me fuzzy, so I want to have some kind of protection against that tactility called business logic. I agree with that if you are familiar with building websites, especially websites with JavaScript, we have the idea of ​​front-end meaning and validation in jQuery, we have the Validate with jQuery or with other tools we have other ways to validate, but essentially if you have a form and you say nothing more for the name field and press send before sending, it will tell you no, you can't send because you have entered something. in that name field which is the client-side or front-end validation, JavaScript says that you can't even talk to the backend yet because you haven't given me the correct information;
However, once you give the correct information, JavaScript says yes. that is valid data and you press submit, the backend also does the validation and the reason is because the backend cannot trust the front-end because you can bypass JavaScript and you can call the form directly instead of calling it through the buddy form. and there are many ways to avoid that buddy dilation, so we actually do the validation twice, especially in asp.net MVC, so we do the validation on the JavaScript side and then you pass the data, we do the validation again and then we do something that I know the data is valid in the same way.
I want to do some validation again when you send data to the database because I can't trust your application to have fully validated the data and I can't trust it to be an application sending its data. data, that's why I reject that idea that there is no business logic in the database and the last drawback I hear sometimes is that tools like entity framework and other large ORMs don't like to use too many procedures. I don't like it, they like to write their own select, insert and update statements, and here's my deal. I really reject the idea of ​​using entity framework.
If you've seen my other videos, you'll see that I don't really like the entity framework. now it's a really amazing tool, like itself, it's done some amazing things with it, but at the end of the day it doesn't let me protect my database, it's slow and it writes the sequel for me, instead it lets me specify exactly what I want. my sequel now saves a step in the sense that you don't come here to send SMS and write queries, but the downside is that you have this black box of I ask for data and it appears until it doesn't appear once it does.
I probably have a debug, so in any framework, it adds a lot of complexity, which slows things down tremendously and doesn't allow me to protect my database the way I want, so I don't really care. that you can't use any framework to its full potential with this method, now you can use the entity framework and still do just procedures in the store, but it's not really beneficial there, so those are the drawbacks. The source control story is not a big deal when releasing the database, if that's a concern for you, it's not for me and again, not a very good story for the entity framework, but I'm personally according to that, so those are drawbacks, the benefits, they are quickly reusable, they are clearly defined, they are safer when it comes. to sequel injection and is much safer from a database perspective, so those are store procedures.
I think it's a really compelling reason to use them and I think they're actually pretty simple to do, but I'd love to hear your feedback on whether you've used them, what do you think you've seen other benefits or drawbacks? Oh, you'd like to talk about them or give us our questions, so leave all those questions and comments below. I will try to answer each one you post. Don't forget if you haven't already. I love to like this video and also subscribe. Thank you, have a great day.

If you have any copyright issue, please Contact