Video: How Do I: Prevent a SQL Injection Security Flaw in an ASP.NET Application?

Presenter:Todd Miranda

SQL injection is an attack in which malicious code is inserted into strings that are later passed to an instance of SQL Server for parsing and execution. SQL Injection attacks are typically the result of misplaced trust in the data inputs to a system. In this video, Todd Miranda demonstrates a simple SQL Injection attack and how to prevent it.

The following text is a software generated transcript of the video. Click on a minute link to jump to a location within the video



Minute 0

Pillow life Todd Maranda this video to demonstrate how to prevent SQL injection attacks in your ASP net applications with begin by creating a new website will create a new ASP net website will choose a folder while putting in judo could write sort or default form here this began by creating some type of input for a user to build input data into this could be a real simplified example of SQL injection attack will talk older more about how it could be more complex and especially more dangerous but for the simple example let s just begin by putting an is a text box art form and will service the Xbox has an ID of text input will keep it simple and will say that maybe it s not a hundred columns because we won t give plenty of room


Minute 1

For our hacker to be able to input information and lets in addition Santa Maria Shiseido attacker which is low but the little bit more by guests politically correct show Catullus bitterly your input form book assumes you know it is really only need there we go a text box we want to add a button to your soul is doable and will give it an ID of a button submit and will study on click we want to do input submit manually or our click event


Minute 2

Are run at the server and live for some Texan sewer bananas and was he texts equals submit data book is so it looks pretty good and then let s go in here just below our our text box are but an analyst to throw a data grid on her rest of us would simulate some type of output for the date of the router tree from our database so was called us grid data and run at sir so very very simple you see we ve simply got an input box a submit button in a place where were going to bind our data to router display the data we get our database so let s look at our code


Minute 3

For this page where a they a page load event but we did have and input submit this from our button and it takes an object and our standard event arcs for it right right so let s start off here with our connection string lead for a database again normally you would store this connection string somewhere with a being the whether that be in the arm registry or in the web config file or in some encrypted location somewhere this case I will use my local database


Minute 4

Use the adventure Works database that comes with filter with SQL Server 2005 so will indicate our event are in this catalog is an adventurer works and in this particular situation I m just going to use integrated security open so there we have our connection string going on in order to use SQL Server will add one additional using statement here so can use the libraries for our SQL Server and we use the system data SQL client and I will go back here and order create a SQL connection


Minute 5

In our panelists call this object connection and I will say this is new a SQL connections and will pass it or connection string right so you open a connection to an nail it is going to want create our our SQL statement and you know the majority of the time a SQL injection attack occurs when someone is using in line SQL or using a SQL it s in the code for using strings for SQL instead of stored procedures so one of the ways that ruination is what ways you can do a lot to protect yourself with not a lot of work is to move your stored procedures


Minute 6

Or move your queries into stored procedures if you re using SQL Server so get out of the in line SQL that you got in your code moving into SQL Server stored procedures however a lot of our applications nowadays might be hitting SQL Server leveraging Oracle and by beheading of some other data sources and shooting all of the same applications of living is not something that can help you out but a moving of this to store procedures can be a big help promising that will completely protect you from SQL injection however that really if you stopped the majority of SQL injection problems that are out there for the most part quite so in our case we ll have that luxury lets say so we re creating a SQL command and will just stick with our convention will call this object command will say in new SQL command and we re going to specify our strings


Minute 7

Pool that will create and our connection object or create a new on Troy and solicit a look at what our command is so similar call that a strain single and you will say this is going to be select e mail dress from person com tight gives of the adventure Works database or hitting here where the first name equals tick and this is where were to put our forever put our text input txt and we should probably trim that but since were just doing a pretty straightforward simple a demonstration here will leave off the trend focus of


Minute 8

Are torturing were effectively playing pulled e mail address from the contact were the first name is equal to whatever we pass in right now in our case is coming from text blocks a lot of times you ll see websites where if you look up in the URL to query string you ll see some key value pairs of the query string welcomes those key value pairs are used as direct input into queries that way you can pass the data as a get but you can also do gets wear your simply using a URL which means that maybe you can call that URL from other places making from a win form application and tree that website almost like a service is of a poor man s services were called up so we see that a lot where the actual parameter that goes into our query comes from the query string and is even more dangerous than the text and input


Minute 9

Input box so men really want is I can call it just crawls the Web from any other site I ll have to submit the form and get a post variable makes using get variables to put into my in my query so you know that something that you get a careful about that is that he is the one thing that you see come out of this and it s the one golden rule if you will of a lot of security when you re dealing with webpages and early any application but were a talk freely about webpages here never trust your input never ever trust and put go Utes don t know where it s coming from you where it s been no that were purchased else in the wash hands you know where things have been so wash your data to trust your data you don t know where it s been just the golden rule there here we trust our data was used to get right into our query string in


Minute 10

Are SQL command so let s do a sequel data reader this will be an easy way to do this will stick with our convention was no object data reader equals a checked command and will do and execute reader and will say plan behavior should be close connection or they will come down here will do our grid data Dave Soros and Orange said that to our object data reader and revenue our grid data is not data aren t


Minute 11

Early and last but not least our object data reader close okay so there we have it we ve will create a connection tour database we created this in line SQL string SQL command here with taking input from our user and reuse it created a base let s build submitting errors will build succeeded with firmness and see what happens desperate or web config file article the first first Berks and our webpage comes up here for big input form a list type in for saying that I was in the database will submit in with their there is the e mail address coming back as I think what a number of them come back for more query and so


Minute 12

Fairly straightforward fairly and in a fairly innocent looking if you will but let s see where the SQL injection actually comes into play right if you know anything about those SQL statements and you know how we can and we can look at SQL Server s only way minimizes for medical and go back to our SQL command your in and noticed here are some or selecting odoriferous and we have the single tick were put at a purpose and a straying into first name and write in and we never ending tick I ll solicit how we will see how we fudge that first of all here for input to say we ve got tick to close the first Texan out of that first link equals ticket will we not only for empty first names in the database so we need to go further with adding or into that


Minute 13

To wear first name is equal to dictate or in them is a something that is always there be true so will legislate one equals one so we have our first hit and now we ve closed that first hit so or if the SQL statement rates for same equals dictate which we know to but we ve added horse was a poor one equals one which will always be the case that he s retrieved all records of first name will always match right now if you remember though was thick with the get stuck on into this as an attack could close the tick at the end of our signal strength we get rid of that other tick so how we get rid of the bad tick or in many cases how we get rid of just the rest of the SQL statement will we would come in and out and become it out with a double with to comment out everything that comes after that


Minute 14

So let s run this as a reference okay so I was never and I just gained to decide one way or another by gutting the site and I was able to inject this into the query and by doing that I was able to get everyone s e mail address to come up right now that may seem it may seem like that s not that big of a deal as well okay I could just enter their names over and over and over again I could ve eventually gotten everybody s e mail address that s true but let s take this a step further this is a very simple example is that this is that for let s say what if I registered on the website and I entered my head into my first name and last name or address the username and password with standard form a first name with the injured some statement here and maybe


Minute 15

Did any of them had a draw database at the end of this okay so we came in here if we know about SQL we know that we can separate SQL statements with us so let s put here and let s say would either do a drop and database name of the table or to do a delete in a table name if we could guess table names or calls an error to occur in our error message might give us the table name data so I might be putting here some information that recalls the SQL statement error and for not catching my exceptions I might get table data in my exception message it will not table manias and I could come in here and say my table name in again terror to do the rest of it to use the


Minute 16

It I can be very malicious here and when I say that data to the database be less a low profile nail some administrator comes into the system administrator pulls up a list of users and in deadliest it when it queries that list maybe it pulls this particular disfigure item and uses that into a subquery so many uses it plugs that data value back into the my SQL statement so I can actually potentially save bad queries out of the database have somebody else run them that has more elevated privileges to the bottom line here is I can do a live end and if as is often the case the user of that are ASP net process is using has access to as or this is the database administrator then are really have a lot more capability to inject some horrible sequel in


Minute 17

Your can drop tables like it potentially could create new tables this could be a very long very long strain that could submit here and dissenting in the URL right so we see how we do it we see where it could be a problem talking insert these these bad strings into my URL or into text fields and get data backward or manipulate the data poorly I could potentially pull back passwords if they were encrypted here I could collectively here and I could do select password from user table if that existed so lots of things I can do so see what I can do is see why is bad else see how to protect against it okay so the easy things we can do to protect against the first thing we can do is never trust data target


Minute 18

That over and over again another way we can do is eat the input fields that are then fed into your SQL statements so excellent only slinks you know if you know that your users you have any users in your database for flame lower than 20 characters set the max length point I doesn t completely protect you can will we get a few minutes of her short SQL statement but will protect you from from some longer more complex and dangerous statements so that one set them as though the link on your input fields another thing you can do if you re using ASP net which many of us that are what his view probably are this is the cost of other technologies as well validate your input in ASP net you can use validator s on your own or form fields to validate input to validate your input at the client level here don t trust the data though even if you re validating the data and the client validated again at


Minute 19

Backing and so do many coasts grubbing on the data are removed for dual replace and replace any bad characters with no with if the spaces or with nothing to do this effectively removed a bad character from your text or only allow good characters in a way he wanted to scrub your data don t trust your data and last but not least parameterized commands so let s take a look at that and what copy this and will comment it out was talked about the fact that we want to we want to really be careful with our data we once grow our data here with a two a parameter instead of the actual text print rights and they ll recommend here in no create a SQL parameter and recall this just for him


Minute 20

Equals new SQL parameter and rest say it is F9 the same name as our attribute bearing or query them to specify what type it is correct so by doing this and saying this is a type of fair care the single parameter knows how to tokenize our strengths and Osama serialize our strings so that we remove any kind of bad characters out of it so there s our SQL parameter and nail we can do parameter value and we set its value to text input txt and we should really time that we were not here and now we have our SQL command lines believe that in place and below are SQL command we do our object


Minute 21

Ed and we add our perimeter to her command to know where are we directly in putting or text input into our query right so if we create a SQL parameter we ve set the value of the SQL parameter type of the SQL parameter and we ve added a SQL parameter to her command so let s build a list builder website succeeded and let s run it so again we can come in and we can tighten our cam casino that works you try to type our are bad SQL statement here and we get nothing back and it s because these bad characters get tokenized they get serialized or encrypted and the SQL parameter note


Minute 22

Out encrypt the data to a lowering code that data should say not encrypt the data should encode that data and the less we can inject those bad characters into our SQL command to create so number of steps we can do to protect ourselves from SQL injection attack I would recommend you do more than just one noon do a lot of these and again the goal rule never trust your data always scrub your data if you describe it on the client and the server do that will get something out of this of this enables you to war write more secure code with your Internet webpages you can find more resources for secure development www hellosecureworld com so check that out and begin writing should more secure code

Click here to go to original video page