how to minimize prevent sql injection

  • Hi All,

    We are having sql injection from website. The login used in the connection having only data reader and execute all SP. We use SP, view and function, no adhoc query. To find we are running server side trace. From that, I have found someone executed SP where this sp do number of items like select, insert and update based on action parameter passed etc. It accepts more than 10 parameters.

    exec sp_xxx_name @action=N'select',@U_Id=N'xxx_transfer',@Password=N'xxxx'.

    exec xx_xx_Login @id=N'11xx',@pass=N'xxxx'

    The login used is connection string login and app is .Net SqlClient Data Provider NT user is NETWORK SERVICE.

    Any suggestion, how to prevent and make our code better etc.

     

     

  • don't use dynamic sql in your stored procedures! ( or use only parameterized queries with the semi generated sql )

    don't grand datareader/datawriter !! Only grant exec for sprocs it is allowed to use and have those sprocs use "with execute as ..." to provide the needed authorizations for that sproc )

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Thanks Johan.

    Can you share some example of parameterized stored proc.

    Ex: something like this.

    Create sp_name (@u_id int,@pass varchar(100))

    as

    begin

    declare @l_u_id int

    declare @l_pass varchar(100)

    code here

    end

    2. If I remove reader, how he login used in application can read data, by stored proc.

     

    • This reply was modified 1 week, 6 days ago by  Raja mssql.
  • When it comes to validating users’ input, dynamic SQL or not, I like to use the belt and braces method of validating the input by running it through the sys.dm_exec_describe_first_result_set procedure before executing the code.

    😎

    If the procedure does not return a result set description, then don’t run the code 😉

  • It's a soup to nuts, everything is on the table, proposition to really get proper protection  from SQL Injection.

    First, and most important, use the least-priviledge principal when setting up your security. Only the access things MUST have should be allowed for any given login or role. Too much access is the biggest problem.

    Next, use stored procedures (as was already mentioned), or, parameterized queries such that you're not building and executing strings of SQL code. Along with this, use the correct data types. If it's a number, use a number, not a string. You need a date or time, use a date or time data type, not a string. In addition, as Eirikur says, validate the input on the code side of things to ensure that dates are dates, etc..

    Additionally, SQL Injection requires exploration. There are common errors, make darned sure you're monitoring for them. And, speaking of errors, ensure that you have appropriate error trapping in place such that it's not exposing details of your infrastructure when errors occur, a common approach to understanding if you can be hacked.

    Do all this, and you'll be protected. Just some of it isn't enough. I've seen code where it's all in stored procedures, but you're passing giant strings and doing ad hoc query builds instead of parameterized queries. However, it all starts with the right security settings. Just that, and only that, can minimize the harm of a SQL Injection attack if you do nothing else. However, I'd suggest pursuing everything. Defense in depth.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2022 Query Performance Tuning, 6th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Eirikur Eiriksson wrote:

    When it comes to validating users’ input, dynamic SQL or not, I like to use the belt and braces method of validating the input by running it through the sys.dm_exec_describe_first_result_set procedure before executing the code.

    😎

    If the procedure does not return a result set description, then don’t run the code 😉

    Thank you, can you refer any example for this method.

  • @Raja mssql ,

    This is meant as a friendly but serious suggestion... IF you even think that you're experiencing "SQL Injection" and YOU are asking these types of questions on a forum, then you folks are the wrong ones to handle it.  You folks need to stop guessing and hire an expert company to look at your systems and DodGammit and fix it.  Then, also hire them to run PenTesting on a regular basis so that you folks don't end up reading about yourselves on the internet.

    On the latter... you should do that even if you don't believe you're experiencing "SQL Injection" or think that your systems are immune from it.  😉

    --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)
    Intro to Tally Tables and Functions

  • Raja mssql wrote:

    Eirikur Eiriksson wrote:

    When it comes to validating users’ input, dynamic SQL or not, I like to use the belt and braces method of validating the input by running it through the sys.dm_exec_describe_first_result_set procedure before executing the code.

    😎

    If the procedure does not return a result set description, then don’t run the code 😉

    Thank you, can you refer any example for this method.

    Here is the documentation.

    😎

    Seriously advise you to carefully read Grant's and Jeff's fine comments and act accordingly!

  • Thanks everyone. I will speak to the team and see what we can do.

     

  • There's a good explanation of SQL injection (and how to prevent it) here.

    https://www.sqlinthewild.co.za/index.php/2009/04/03/dynamic-sql-and-sql-injection/#more-230

     

Viewing 11 posts - 1 through 10 (of 10 total)

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