how to overcome sql injection

  • Hi

    we are not to keep our database or tables secured inspite of having password changed ,firewalls and spyware live. the hacker is injecting the java script code in the records of the tables . we found that code insertion is done at where the user is giving the input data. how to avoid this problem.

  • Parameterise all queries in your front end app. Do not concatenated together SQL statements and execute them.

    Preferably, use stored procedures for data access only and do not allow the application user to have any rights to the base tables. The application user should have only execute rights on the stored procedures.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hi,

    Here are some recommendations for you:

    1. Secure the Web Tier

    You need to ensure that your application/website tier validates all user input before passing it on to the database layer. Rather than checking for invalid characters (quotations etc.), as there are potentially many, I recommend to my clients that they define a list of “valid” input values for their interfaces/forms etc.

    2. Use Bound Parameters

    In order to negate SQL Injection you need to ensure that any parameters that are passed to SQL calls are adequately bound.

    3. Use Different Connections

    Use different connections/logins for different tasks. I.e. the connection that is validating a user’s email address does not need to have update permissions to the database.I often recommend to clients that they use a connection/account with minimal privileges for all operations (i.e. logging a user into their system) unless otherwise necessary. Once a user has been authenticated they can be provided with access to/via another connection that has more privileges.

    4. Use Stored Procedures

    Use stored procedures to interact with your database rather than generating/building SQL dynamically.

    Also take a look at the following Microsoft article: http://msdn.microsoft.com/en-us/library/ms161953.aspx

    Hope this helps.

  • In other words, you will need to rewrite part of the app to use stored procedures instead of dynamic embedded SQL.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • The best way to avoid SQL Injection is use of Stored Procedures.

  • Hari.Sharma (7/14/2008)


    The best way to avoid SQL Injection is use of Stored Procedures.

    The only way to 100% for certain avoid SQL injection is to use properly parameterised queries or stored procedures.

    And don't make the mistake of using stored procedures that dynamically build up SQL strings inside the proc and then exec it. Procs like that are just as vulnerable to SQL injection as dynamic string on the front end.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • A decent overview with examples and links is in Earland Sommarskog's article here;

    http://www.sommarskog.se/dynamic_sql.html#SQL_injection

    There's a lot more out there covering in more detail, but IMO this is a good starting point for getting the basic idea

    hth Andrew

  • some brilliant links and advice there, way to go guys 😀

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • I'd say GilaMonster has it right. Dynamic SQL in any form (in an application or in a stored procedure) is vulnerable to SQL injection. The safest and best place for SQL DML code is in the database as a stored procedure. You could think of the stored procedures as the methods to a database object. In the .NET world you don't grant access to the internal code and data structures except thru properties and methods, so don't treat your database differently. Never grant users the ability to select, insert, update or delete anything directly from a table or view. Only allow them to execute stored procedures.

    This is a point of control for the data. Besides, you don't want to face a news crew (or your boss) to try and explain how the data was compromised. Especially since these practices have been

    best practices

    for over a decade.

    --Paul Hunter

  • paulhunter (7/15/2008)


    In the .NET world you don't grant access to the internal code and data structures except thru properties and methods, so don't treat your database differently.

    THAT is one of the best justifications for stored procedures that I've seen. Thanks, Paul.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • It's important to point out that stored procedures and parameterized queries aren't the full answer. The comment about dynamic SQL is key. If there's any place that queries are being built and executed, such as using EXEC() or sp_executesql in a stored procedure, you are still potentially vulnerable. The general recommendations:

    1) Use stored procedures

    2) Use parameters at the application layer

    3) Do not use any dynamic SQL

    4) Validate your input anyway

    If you have any dynamic SQL, #4 is an absolute must. Even if you don't have dynamic SQL, you should still do #4. #1 and #2 should be non-negotiable for an application facing the Internet.

    K. Brian Kelley
    @kbriankelley

  • I agree with what everyone has said, paramterised stored procs etc.

    Also I would remove all permissions from the public role as well, as these permissions override any login/user permissions in the database.

    Also do you have the SQL scripts to clean your infected data?

    --Shaun

    Hiding under a desk from SSIS Implemenation Work :crazy:

  • Agree with all that has been said without exception.

    Ran across this new Microsoft tool for use with an IIS server that could be of some use to you.

    Microsoft has released a new security tool which Microsoft states:

    "UrlScan version 3.0 Beta is a security tool that restricts the types of HTTP requests that Internet Information Services (IIS) will process. By blocking specific HTTP requests, UrlScan helps prevent potentially harmful requests from being processed by web applications on the server."

    Download URL:

    http://www.microsoft.com/downloads/details.aspx?FamilyID=ee41818f-3363-4e24-9940-321603531989&DisplayLang=en

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • This is a good article on how to avoid SQL Injection, even if you do not use stored procedures.

    Always Use Parameters. Even if you don't use Stored Procedures.

    http://weblogs.sqlteam.com/jeffs/archive/2006/07/21/10728.aspx

  • bitbucket (7/16/2008)


    Agree with all that has been said without exception.

    Ran across this new Microsoft tool for use with an IIS server that could be of some use to you.

    Microsoft has released a new security tool which Microsoft states:

    "UrlScan version 3.0 Beta is a security tool that restricts the types of HTTP requests that Internet Information Services (IIS) will process. By blocking specific HTTP requests, UrlScan helps prevent potentially harmful requests from being processed by web applications on the server."

    Download URL:

    http://www.microsoft.com/downloads/details.aspx?FamilyID=ee41818f-3363-4e24-9940-321603531989&DisplayLang=en

    URLScan can block certain patterns, but for that sort of thing it can be problematic because it'll block the pattern, even if it's legit. So if you have some who enters -- and that's the pattern you are keying on, it's blocked.

    K. Brian Kelley
    @kbriankelley

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply