Synopsis: Deploy a pre-built web site which is subject to SQL injection, understand how the underlying database can be compromised, and fix the code to prevent the SQL injection.

Prerequisites:

  1. Access to SQL Server with write (or better) privilege via Windows authentication to a copy of Microsoft's Northwind example database (e.g., completion of exercise Example DB & Simple Queries).
  2. Access to Visual Studio (or some other method of running ASP.Net & C# web sites).
  3. Basic familiarity working with Visual Studio projects and solutions as well as (at least) rudiments of coding in C#. 

Notes:

  1. The web application is very simple and meant to highlight how SQL injection works and can be prevented.  A 'real life' web application would have more complexity and abstraction including authentication and authorization schemes but those aspects would not prevent the injection.
  2. The injection attack illustrated requires some 'inside knowledge' of how the underlying database is configured (e.g., table and column names).  True SQL injection attacks often take months or more to perpetrate while the hackers gain this inside knowledge; again, our goal is to see an injection in action and then prevent it so we shortcut the database discovery aspects of the hack.
  3. Letting the cat out of the bag here but if you avoid SQL concatenation when coding in favor of parameterized queries you can virtually eliminate SQL injection; this is especially true when the parameters are user input (and internet users most especially). Alway use parameterized SQL when writing code.
  4. Be a little careful when 'playing' with your injections (e.g., to see what you can do): it is quite possible to hose the entire database.  That's why this exercise uses a personal copy of the database which you can alway restore to its original form by repeating, e.g., the exercise Example DB & Simple Queries.
  5. The demonstration accesses a SQL Server instance named Dyn365FO1-1 using Windows Authentication. If your SQL Server has a different name you need to find it and change the application as indicated in step I.C. Among other approaches the Server name appears on the connection dialog when using SQL Server Management Studio.

  1. The demonstration uses a database named tschultzNorthwind in the SQL Server; you will need to substitute the name of your own Northwind database in completing the exercise as indicated in step I.C.

The exercise guidance below is meant to supplement the video A SQL Injection Scenario which provides step-by-step guidance of "look and feel" for this demonstration.

  1. Install the web site
    1. Remote into your RdpDO VM (or login wherever you have the prerequisites mentioned above configured) and download NWFreight.zip into (recommended) your Documents folder (or somewhere you can remember).  Extract the zipped folder NWFreight.zip into (recommended) your Documents in a NWFreight folder (creation of the folder will probably be automatic). This contains an ASP.Net website with SQL injection issues.
    2. Start Visual Studio, use File | Open | Web Site, and then browse to the extracted folder NWFreight and click the Open button. Open the folder itself, not a folder or file within the NWFreight folder.
    3. Visual Studio wants to create a solutions (.sln) file to keep track of projects meta-data so to keep from getting messages about a missing solutions file just use File | Save All and let VS save the .sln file where it wants (or put it where you want).
    4. Using Solution Explorer double-click Web.config and for the NorthWind connection string be sure you are pointed to the correct server (i.e., Data Source) and and database (i.e., Initial Catalog); see notes 5 and 6 above and please note that other than for testing and demos or very specific applications, Integrated Security (which authenticates the database connection based on the Windows credentials of the user running the application) is a poor practice and typically not workable on production web sites.

    1. You can run the web application in debug mode by pressing F5; the web.config and any other files will be saved and the application compiled and loaded into a development web browser.  Enter a valid Northwind CustomerID and click Balance (or press Enter) and their total Freight is shown.

    1. If you close the browser or return to Visual Studio and choose Debug | Stop Debugging the web application will stop.
  1. A SQL injection
    1. A look at Default.aspx shows a very basic ASP.Net server driven web page: a text box with ID txtCustomerID where the user enters the customer ID of interest, a button with ID btnBalance that when clicked executes the method btnBalance_Click, and a label with ID lblResult where the code will place the result of the query.

    1. A look at the code behind the form in Default.aspx.cs is also basic and applies ADO.Net for doing the SQL Server query.  The SQL injection culprit is in use of 'concatenated SQL' to embed the user entered customer ID in the query.

    1. Finally, the injection:  Start the application again if it isn't still running (i.e., press F5). We noticed above that customer BERGS owed Northwind $1500+ for freight charges. If the following is used in the txtCustomerID textbox 

'; update orders set freight=0 where customerid = 'bergs'; --

instead of a customer ID, clicking Balance returns the following (which doesn't mean much)

 

A re-query for BERGS freight charges, however, shows they now owe nothing for freight!  We 'injected' an update in SQL that was meant to only report a value.

    1. Notice the injected SQL begins with '; which ends the original, intended query.  Then comes the injected problem script ending with ; followed by -- which comments-out any other characters concatenated from the original intended query.  Want to know how many customers Northwind has?  After zeroing out BERGS freight charges try the following injection and then requery BERGS freight charges; the value shown will be the number of Northwind customers, something that the user has no business knowing (enter the injection in the txtCustomerID box but don't press enter where the line breaks below ... just keep typing or simply copy and paste it).

'; update orders set Freight = (select count(customerid) from customers) where orderid = (select top 1 orderid from orders where customerid = 'bergs'); --

    1. We can essentially do anything with the database that the user in the connection string can accomplish though we have to be a little tricky about it. Our purpose is to prevent SQL injection but you might contemplate the injections required to
      1. Show Northwind's total inventory value (the sum of UnitsInStock * UnitPrice for all products) as the (fictitious) BERGS freight charges 
      2. (injection 1) Insert you as a Northwind Shipper (use, e.g., your own name for CompanyName) and then (injection 2) assign all BERGS orders to be shipped by you.  Hint:  You will need to update ShipperID on BERGS orders to the ShipperID you got assigned on injection 1 so maybe a sub-query like (SELECT ShipperID FROM Shippers WHERE CompanyName = 'My Name') for the value of ShipperID.
  1. Prevent the SQL injection
    1. In this case ... and virtually all SQL injection problems ... the issue comes from how the application prepares the SQL to access the database.  There are object/relational mapping frameworks that help produce good SQL but the core solution is to parameterize user-entered values rather than concatenate them into the SQL command. 
    2. In our code make the following changes:  Update the WHERE class to use @prmCustomerID rather than the concatenated value and add the line of code which adding parameter to the command defining the value for @prmCustomerID.  Note that SQL parameters need to begin with @ but the prm is just a convention (that is, the parameter could be named @Anything).

    1. That's it.  Try your injections now and they won't work because we have put the parameter in its place and not elevated it to potential script.  Plus the code is cleaner and its purpose is clearer. And cases where a CustomerID includes an apostrophe (e.g., JOE'S for Joe's Bar & Grill) won't wreck the concatenation.
  1. Short version
    1. SQL concatenation BAD
    2. SQL parameterization GOOD