none
Preventing Sql injection while using stored procedures RRS feed

  • Question

  • I am developing an .net based website which has stored procdeures to exceute the database queries.
    As per my understanding from various websites, stored procedures are also vulnerable to SQL injection attack.
    Can somebody please share some checklist having exact things to be done/kept in mind while writing and calling stored procedures to protect them from sql injection.
    e.g. in input validation we must check for "select, union, delete,update, OR, --, insert, %%,', Grant Control, Print,DROP,!,;,=,+,||,Concat,ASCII,admin,),Having,Group by, order by,NULL,Convert,sum,where,top,waitfor,*,count," should not be there.

    I need these kind of exact checklist for database programmers to execute rather than first coding and then checking using any static analysis tool.

    Cheers
    Sunday, July 12, 2009 2:34 PM

Answers

  • the xss library is indeed targeted for preventing cross-site scripting (and you're correct it also has a http module to encode all incoming inputs), but the utilities is provides helps you with sql injection as well. As Fredeikm mentioned, you've to encode your input before it's being processed and the xss library has some encoding and decoding methods to help you do that. Basically, you send your raw input to the encoding methods and get a safe encoded output which you can process and / or send to the database.

    saying that, i did a bit more research and came up with this little baby that i think is more suitable for you: http://channel9.msdn.com/posts/Jossie/SQL-Detect/
    I fetched that link from the blog of the security tools guys: http://blogs.msdn.com/securitytools/

    Fernando Felman Solution Architect Unique World My blog

    Wednesday, July 15, 2009 12:13 AM

All replies

  • Hi

    first off, msdn has a how to on this:
    http://msdn.microsoft.com/en-us/library/ms998271.aspx

    in short:
    -> make sure that you limit the input fields to not contains various characters (like <,>, %, --)
         which can be done by using regex validators. 
    -> use typesafe sql parameters + stored procedures (in the case you're not using an ORM), avoid dynamic sql
    -> use an account that only has read rights on the db (not drop rights on tables, etc)


    Hope this helps you out
    Please close the thread if your question is answered, and don't forget to rate the best responses!
    Sunday, July 12, 2009 8:40 PM
  • frederikm is correct, you've to evaluate your inputs for potential threats.
    The good news is that there are some tools from Microsoft to facilitate this:
    * the Anti XSS: latest version is BEAT 3 (http://antixss.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=20333) but you can stick with a more stable (older) version. This library has some useful encoders/decoders methods you can use to prevent code injection (SQL and scripting).
    * the code analysis tool (http://www.microsoft.com/downloads/details.aspx?FamilyId=0178e2ef-9da8-445e-9348-c93f24cc9f9d&displaylang=en). This tool allows you to evaluate threats in your codebase.


    hope this helps.

    Fernando Felman Solution Architect Unique World My blog

  • Frederikm,
    Thanks for your useful inputs.
    Can you please give your comments of the other inputs restrictions mentioned by me like DROP,SELECT,Delete etc.

    Fernanado,
    Can Anti XSS library be used for SQL injection as well , as i understand that it is used for preventing Cross site scripting.
    If you have some idea about Anti XSS, can you please tell me as how it would be implemented in an application design as it
    is not clear from codeplex documentation.
    Does it goes in HTTPModule for the whole application and do the magic.

    Cheers

    Monday, July 13, 2009 11:19 AM
  • the xss library is indeed targeted for preventing cross-site scripting (and you're correct it also has a http module to encode all incoming inputs), but the utilities is provides helps you with sql injection as well. As Fredeikm mentioned, you've to encode your input before it's being processed and the xss library has some encoding and decoding methods to help you do that. Basically, you send your raw input to the encoding methods and get a safe encoded output which you can process and / or send to the database.

    saying that, i did a bit more research and came up with this little baby that i think is more suitable for you: http://channel9.msdn.com/posts/Jossie/SQL-Detect/
    I fetched that link from the blog of the security tools guys: http://blogs.msdn.com/securitytools/

    Fernando Felman Solution Architect Unique World My blog

    Wednesday, July 15, 2009 12:13 AM
  • Thanks a lot Fernando for taking pain for me and searching so much good information.
    I will go through all the information and URl provided by you and get back to you incase of any difficulty.
    Thanks again.

    Cheers
    Wednesday, July 15, 2009 12:03 PM