Sql Injection in Sql Server

Sql Injection in Sql Server


SQL injection is an attack in which malicious code is inserted into strings that are later passed to an instance of SQL Server for parsing and execution.


Any procedure that constructs SQL statements should be reviewed for injection vulnerabilities because SQL Server will execute all syntactically valid queries that it receives.


The primary form of SQL injection consists of direct insertion of code into user-input variables that are concatenated with SQL commands and executed.

The injection process works by prematurely terminating a text string and appending a new command. 

The following script shows a simple SQL injection. The script builds an SQL query by concatenating hard-coded strings together with a string entered by the user:

var Shipcity;
ShipCity = Request.form ("ShipCity");
var sql = "select * from OrdersTable where ShipCity = '" + ShipCity + "'";

The user is prompted to enter the name of a city. If she enters Redmond, the query assembled by the script looks similar to the following:




SELECT * FROM OrdersTable WHERE ShipCity = 'Redmond'




However, assume that the user enters the following:




Redmond'; drop table OrdersTable--




In this case, the following query is assembled by the script:




SELECT * FROM OrdersTable WHERE ShipCity = 'Redmond';drop table OrdersTable--'









The semicolon (;) denotes the end of one query and the start of another. The double hyphen (--) indicates that the rest of the current line is a comment and should be ignored. If the modified code is syntactically correct, it will be executed by the server. When SQL Server processes this statement, SQL Server will first select all records in OrdersTablewhere ShipCity is Redmond. Then, SQL Server will drop OrdersTable.




As long as injected SQL code is syntactically correct, tampering cannot be detected programmatically. Therefore, you must validate all user input and carefully review code that executes constructed SQL commands in     the server that you are using.







How to Overcome this?

Always validate user input by testing type, length, format, and range. When you are implementing precautions against malicious input, consider the architecture and deployment scenarios of your application. 

Test the size and data type of input and enforce appropriate limits. This can help prevent deliberate buffer overruns.
Test the content of string variables and accept only expected values.




Never build Transact-SQL statements directly from user input.
Use stored procedures to validate user input.



            Never concatenate user input that is not validated. String concatenation is the primary point of entry for script injection.

When you can, reject input that contains the following characters.




Input character
Meaning in Transact-SQL

;

Query delimiter.


'

Character data string delimiter.


--

Comment delimiter.


/* ... */

Comment delimiters. Text between /* and */ is not evaluated by the server.


xp_

Used at the start of the name of catalog-extended stored procedures, such as xp_cmdshell.

Use Type-Safe SQL Parameters
The Parameters collection in SQL Server provides type checking and length validation. 

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;

Use Parameterized Input with Stored Procedures

Stored procedures may be susceptible to SQL injection if they use unfiltered input. For example, the following code is vulnerable:











SqlDataAdapter myCommand = 
new SqlDataAdapter("LoginStoredProcedure '" + 
                               Login.Text + "'", conn);





If you use stored procedures, you should use parameters as their input.

Use the Parameters Collection with Dynamic SQL

If you cannot use stored procedures, you can still use parameters, as shown in the following code example:











SqlDataAdapter myCommand = new SqlDataAdapter(
"SELECT au_lname, au_fname FROM Authors WHERE au_id = @au_id", conn);
SQLParameter parm = myCommand.SelectCommand.Parameters.Add("@au_id", 
                        SqlDbType.VarChar, 11);
Parm.Value = Login.Text;






Filtering Input

Filtering input may also be helpful in protecting against SQL injection by removing escape characters. However, because of the large number of characters that may pose problems, this is not a reliable defense. The following example searches for the character string delimiter.











private string SafeSqlLiteral(string inputSQL)
{
  return inputSQL.Replace("'", "''");
}






LIKE Clauses

Note that if you are using a LIKE clause, wildcard characters still must be escaped:











s = s.Replace("[", "[[]");
s = s.Replace("%", "[%]");
s = s.Replace("_", "[_]");





http://msdn.microsoft.com/en-us/library/ms161953(v=sql.100).aspx

Redbus - Success Story

Redbus - Success Story

Introduction

·         redBus is India’s first, largest and favorite bus tickets booking site. It got voted by Forbes among the top 5 hottest start ups in India. It is one of the most loved travel websites with some of the rarest features and has offices in Ahmedabad, Bangalore, Chennai, Coimbatore, Delhi, Hyderabad, Mumbai, Pune, Vijayawada and Vishakhapatnam.

·   redBus.in was founded by three engineers -- Phanindra Sama, Charan Padmaraju and Sudhakar Pasupunuri They were friends from BITS Pilani, one of India's finest engineering colleges.All with top IT MNCs - IBM, Texas Instruments and Honeywell.

  In an interview with Rediff.com, Phanindra speaks about his journey from an engineer working for a company to an accidental entrepreneur, My ambition was to be an electronics engineer and I was happy working for Texas Instruments in Bengaluru after passing out from BITS, Pilani.

·    The seed was sown when I couldn't get a bus ticket to go back to Hyderabad during the long Diwali weekend in 2005.   As I had no other work, I went to a bus ticket agent and asked how the whole process worked. I felt there might have been a bus which went vacant and he didn't know about it.

·   When he explained how bus ticket booking worked, I figured out that there were many inefficiencies in the system.   Suppose there are hundreds of buses from 30 operators running from Bengaluru to Hyderabad, the agents do not have access to all the operators.

·   When an agent gets a customer, he calls a bus operator to find out whether there are tickets available. The operator looks at the chart and tells him the number of vacant seats. The number of agents a bus operator can have is limited as he needs to identify all by phone. The agents also have only limited number of operators to work with. Then, the customers cannot always choose the seats as there is no transparency.

·   Another problem is, as the fares are not published, there is no fixed fare for the customer.  But the major problem was booking return tickets. Every time you went home, you had to call someone and ask them to book the return tickets from there.  At that time, travelling from Bengaluru to Hyderabad was like travelling between two countries.   Being an engineer, when you see a problem, you start thinking about solutions. I felt computers could solve these problems easily.

·   That weekend itself, I wrote a mail to my room mates telling them about the problem, and why I had to stay back. I also wrote, 'I see a solution to this problem and could we work on this?'.   What I planned was, create a software, sell it to bus operators and give the money to some NGO. It was not a business proposition at all at that time.

At that time, it was very exciting for us to find a solution for such a problem that involved thousands of people.  In January, 2006, we -- the seven of us -- divided the work among st us and started working on weekends on the project. When the prototype was ready, we went to the bus operators and tried selling it to them, but they were not even willing to take it for free. It was like, we were trying to disturb the status quo. We didn't know what to do.

·   That was when we heard of TiE, Bengaluru, and we went to them with the business plan. They gave us three mentors to advise on what to do.

    We collected the below information:

·   The number of buses, the number of routes, the average price of a ticket, how people buy tickets, the profile of customers, how much commission a bus operator pays to an agent, etc.  It may not be comprehensive, but it gave us a general idea of the industry. Even today, we continue to study the industry and we cannot stop.

·   We started in August 2006 with Rs 500,000 which was the savings of the three of us. One room of the house where we stayed was our office. In the morning we would keep the other parts closed so that the room looked like an office.By now, three more people who were young relatives of ours had joined us to help out

·   We used to go to the IT companies, stand outside when the employees came out for lunch and then we gave our redBus cards. For the first time, we were on the other side of the fence.  A few weeks back, we were inside a campus and we used to ignore such sales people or brush them aside. Now, we had to kill our egos. It was a big moment of truth for us. In entrepreneurial life, you have many such humbling experiences.

How was the first day when you opened your web site for booking?

·   That is another story. After several visits and many requests, one operator agreed to give us five seats. It was on the August 18. He said, if you sell 5 seats in one week, its fine. If you don't, don't bother me again. We had one week to prove ourselves.

·   We put the seats up! We told all our friends and colleagues and asked them to buy from us. We also requested one of our friends to write about us on their discussion board at Infosys.

·   On the 22nd of August, we sold our first seat. A lady working at Infosys booked a ticket to go to Tirupati. We were so tense that we went to the bus station and waited till she boarded the bus. We didn't tell her that we were from redBus. We sold all the seats in five days and went back to him. Slowly, we could add more operators to our inventory.

·   When did you scale up your operations?

·   When TiE selected ours as one of the three ideas out of 300 for mentoring, it became news. It was followed by venture capitalists contacting us as they found our idea interesting.Before we went to TiE, we didn't know anything about VCs (venture capitalists)! That was the time there were many VCs and very few ideas. So, people were willing to put up money for our idea.

·   We asked our mentors and they told us to take the money and begin developing the idea.

·   The VCs asked us how much money we needed, we said Rs 30 lakh (Rs 3 million) as that was a big amount for us then.Then one of the VCs spent several hours with us and we revised the plan seven times. At last, we found that we needed Rs 3 crore (Rs 30 milion) to scale up the business. The agreement was that money would be invested in three years.

·   That was in February 2007 and the money was supposed to last till February 2010. We spent all the Rs 3 crore in one-and-a-half years.

·   The VCs also asked us to change from an online bus ticketing company to just bus ticketing company, and that is what redBus is now.

·   Once you take money from someone, it becomes a business and you have to return profits on that money.

·   
    The first year was not a full year and we did Rs 50 lakh (Rs 5 million) worth of business in the first financial year. There were no profits.Today you can book a redBus ticket at over 75,000+ outlets!

    Today, RedBus has 230 employees, offices in nine cities, and tie-ups with over 700 bus operators across the country.
F   From a turnover of 50 lakh in the first year of operations, redBus expects revenues of about 150 crore this year. redBus posted revenues of 60 crore in the previous year.

redBus has the largest network of bus operators in their list (350+ and growing) and very satisfied customersOn offer are over 4500+ (and growing) routes across the Indian map.

http://www.redbus.in/

Response.Redirect(url,true) Vs Response.Redirect(url,false)

Response.Redirect(url,true) Vs Response.Redirect(url,false)

To avoid ThreadAbortException while Redirect

You don't need to put the redirect in a try-catch block. Just replace all calls to Response.Redirect(url) with the following lines:

Response.Redirect(url, false);

That will avoid the exception being thrown and gracefully end execution of the thread and event chain.

The second parameter overload of Response.Redirect is nice because it doesn't call Response.End, which is responsible for throwing the ThreadAbortException.  BUT...

The drawback to using this is that the page will continue to process on the server and be sent to the client.  If you are doing a redirect in Page_Init (or like) and call Response.Redirect(url, false) the page will only redirect once the current page is done executing.  This means that any server side processing you are performing on that page WILL get executed.  In most cases, I will take the exception perf hit over the rendering perf hit, esp since the page won't be rendered anyway and that page could potentially have a ton of data. Using Fiddler I was also able monitor my http traffic and see that when using this redirect method the page is actually being sent to the client as well.
I don't usually do redirects in try/catch blocks, but if you do the ThreadAbortException will be handled by your catch and potentially cause a transaction Abort (depending on what you are doing of course).  If you do put the redirect in the try block, then you'll need to explicitly catch the ThreadAbortException or create a wrapper method that does that for you.

Something like this would work.

 ///

     /// Provides functionality for redirecting http requests.
     ///

     public static class RedirectUtility
    {
         ///

         /// Redirects to the given url and swallows ThreadAbortException that is raised by the Redirect call.
         ///

         /// The url to redirect to.
         public static void Redirect(string url)
         {
             try
             {
                 HttpContext.Current.Response.Redirect(url, true);
             }
             catch (ThreadAbortException)
             {
             }
         }
  }

ASP.NET Impersonation

When using impersonation, ASP.NET applications can execute with the Windows identity (user account) of the user making the request. Impersonation is commonly used in applications that rely on Microsoft Internet Information Services (IIS) to authenticate the user.

ASP.NET impersonation is disabled by default. If impersonation is enabled for an ASP.NET application, that application runs in the context of the identity whose access token IIS passes to ASP.NET. That token can be either an authenticated user token, such as a token for a logged-in Windows user, or the token that IIS provides for anonymous users (typically, the IUSR_MACHINENAME identity).

You control impersonation using the identity configuration element. As with other configuration directives, this directive applies hierarchically. A minimal configuration file to enable impersonation for an application might look like the following example:

< configuration >
  < system.web >
    < identity impersonate="true"/ >
  < /system.web >
< /configuration >

You can also add support for specific names to run an application as a configurable identity, as shown in the following example:

< identity impersonate="true" 
  userName="contoso\Jane" 
  password="********" / >

ASP.NET Authorization

Authorization determines whether an identity should be granted access to a specific resource. In ASP.NET, there are two ways to authorize access to a given resource:

• File authorization   File authorization is performed by the FileAuthorizationModule. It checks the access control list (ACL) of the .aspx or .asmx handler file to determine whether a user should have access to the file. ACL permissions are verified for the user's Windows identity (if Windows authentication is enabled) or for the Windows identity of the ASP.NET process. For more information, see ASP.NET Impersonation.
• URL authorization   URL authorization is performed by the UrlAuthorizationModule, which maps users and roles to URLs in ASP.NET applications. This module can be used to selectively allow or deny access to arbitrary parts of an application (typically directories) for specific users or roles.
  
  
  The following example grants access to the Kim identity and members of the Admins role, and denies access to the John identity (unless the John identity is included in theAdmins role) and to all anonymous users:
  

  
  < authorization >
    < allow users="Kim"/ >
    < allow roles="Admins"/ >
    < deny users="John"/ >
    < deny users="?"/ >
  < /authorization >
  

  
  
  The following authorization section shows how to allow access to the John identity and deny access to all other users:
  
  

  < authorization >
    < allow users="John"/ >
    < deny users="*"/>
  < /authorization >

  
  The following example allows all users to perform an HTTP GET for a resource, but allows only the Kim identity to perform a POST operation:
  
  
  < authorization >
    < allow verbs="GET" users="*"/ >
    < allow verbs="POST" users="Kim"/ >
    < deny verbs="POST" users="*"/ > 
  

HTTP Status Code

HTTP Status Code 

HTTP/1.1 defines unique status codes and reasons. A
reason is nothing more than a very brief description of the status code. Table 1-3 shows a list
of common status codes and reasons.


Status Code Reason
100 Continue
200 OK
201 Created
300 Multiple Choices
301 Moved Permanently
302 Found
400 Bad Request
401 Unauthorized
403 Forbidden
404 Not Found
407 Proxy Authentication Required
408 Request Time-out
413 Request Entity Too Large
500 Internal Server Error
501 Not Implemented

Session State Mode in Asp.net

Session State Mode in Asp.net

If your application needs to scale to thousands of users, then you should strongly consider
using the client for storing application state. Removing this burden from the server frees
up resources, allowing the server to process more user requests. ASP.NET provides several
techniques for storing state information on the client. These include the following:

■ View state ASP.NET uses view state to track values in controls between page requests.
You can also add your own custom values to the view state.
■ Control state Control state allows you to persist information about a control that is
not part of the view state. This is useful to custom control developers. If view state is
disabled for a control or the page, the control state will still function.
■ Hidden fields Like view state, HTML hidden fields store data without displaying that
data to the user’s browser. This data is presented back to the server and is available
when the form is processed.
■ Cookies A cookie stores a value in the user’s browser. The browser sends this value
with every page request to the same server. Cookies are the best way to store state
data that must be available for multiple webpages on a website.
■ Query strings A query string is a value that is stored at the end of a URL. These
values are visible to the user through his or her browser’s address bar. Use query
strings when you want a user to be able to use email or instant messaging to store
state data within a URL.

The following example shows settings in a Web.config file that cause the session state to
be stored in a SQL Server database identified by the specified connection string.
< configuration >
< system.web >
< sessionState
mode="SQLServer"
cookieless="true"
regenerateExpiredSessionId="true"
timeout="30"
sqlConnectionString="Data Source=MySqlServer;Integrated Security=SSPI;"
compressionEnabled="true"
stateNetworkTimeout="30" />