SQL Injection Attack Demo

A SQL Injection Attack is one of the many security issues that must be address when designing and developing applications that access a database.  The injection vulnerability is potentially present on pages or forms where the user must enter a value to be submitted to the server. If the user input is not properly validated and the database doesn’t protect itself then SQLiA can occur. I have posted a sample application under the Demos link in the Downloads section of the mail portal. To download the SQL Injection Attack Sample Web Site and SQL Script click here: VB.Net Version orC# Version To run this demo code you will need Visual Studio 2008 or higher and SQL Server 2000 or higher installed

SQL Injection Demo:

The SQLInjectionDemo.zip file consists of a T-SQL Script file named CustomerOrdersDB_SQLInjection.sql used to create the database, tables (including sample data) and stored procedures (Stored procedures were created usingCRUD Script) and a Visual Studio 2008 project called Demo.sln. The Visual Studio Solution contains 2 pages: SQLInjection.aspx and SQLInjectionFixed.aspx that as the names imply illustrate a page that is vulnerable to SQL Injection and one that is not (not all possible SQL injection attacks are prevented but most).

To test the search feature that’s vulnerable to SQL Injection:Open the Solution (Demo.sln)Select SQLInjection.aspx in the Solution ExplorerPress Ctrl + F5 or Select Start without Debugging from the Debug menuType Antoine into the search boxPress the Search button
Note: You will notice that the results displayed on the page are filtered to show only Antoine Victor. Try a couple more searches then continueCopy the injection statement from the bottom of the pagePaste into the search boxPress the Search button
Note: You will see a list of all of the tables defined in the current database and all columns defined in those tables. Think credit card table, employee table with Social Security Numbers. Armed with this information a hacker could use the same SQL Injection vulnerability in this page to then request columns and rows from the credit card or employees table.
Fortunately there is a relatively easy fix for this. The fix is a 2 part process, first we validate the user input before sending it to the server and removed any special characters or malicious code, and second we make all calls to the database through stored procedures (created automatically usingCRUD Script or theSSMS Toolkit)
To see the page with the Injection issue resolved in the current browser window navigate to SQLInjectionFixed.aspx and follow the previous steps. This SQL Injection issue is now resolved.
For a list of other common injection attacks to test with this demo see: SQL Injection Cheat Sheet.

YouTube Demo showing the SQL Injection Fix:

Add stored procedures to prevent SQL Injection:

SQL Formatting Trick

I learned a very useful T-SQL script formatting trick today. I often talk about formatting and commenting for the “person from mars” which is usually “you” coming back to look at your code 3 years from now having forgotten everything you ever knew about this project and learned better ways to do everything that was done in this project but now you just need to make a “quick” update. How do you find the one piece of code in the thousands of lines of code (perhaps millions) that make up this solution? Well hopefully you left yourself a few clues AKA comments that call out important lines or sections of code and hopefully the code has been formatted in a way that makes it easy to read and debug. Well developers cannot survive on google searches and hope alone! We need to help our future selves out a little. So let’s take a look at a formatting trick that will make your T-SQL statements easier to read and debug. When creating stored procedures, views and user defined functions you may need to comment out one or more lines for debugging or testing reasons, however when all of your code is on a single line it makes it very difficult to comment out a single column reference or where clause. So while debugging stored procedures I used to spend a lot of time (before I learned this new trick) using a keyboard shortcut. The keyboard short I had been using was “Ctrl + Right Arrow” this keyboard short cut skips to the beginning of the next word (commas count as words so it will stop at every comma as well) so in a statement such as: “Select FirstName, LastName, Phone, EmailAddress from Person.Person” the keyboard shortcut will move us from the beginning of the word Select to the beginning of the word FirstName the next time we press the arrow key we will move from the begging of FirstName to just before the comma (after FirstName). Using this short cut the key patter would be Ctrl + Right Arrow 2x (press the right arrow twice while holding down the Ctrl key) then Press Enter (to move “, FirstName” to the next line) then Ctrl + Right Arrow 2x, Enter (to move “, LastName” to the next line). Repeat for each column in the table. This works and I’ve been using it for years but there is a better way! The New Way… Using Search and Replace we can replace all commas with a carriage return and a comma.
Ctrl + H opens the search and replace window.
In the find what box type a comma.
In the Replace with box type \n,
then in Find Options select the “Use:” checkbox
then choose Regular Expressions from the dropdown.
This will replace all commas with a carriage return and a comma.
Converts this: Select FirstName, LastName, Phone, EmailAddress from Person.Person
To this: SelectFirstName, LastName, Phone, EmailAddressfrom Person.Person

Thanks for the great tip, Cal!

What is SQL Server

If you are reading this post you have of course heard the term DataBase… Depending who you are and what you do for a living that term may or may not mean anything to you. If you are a DBA (DataBase Administrator) look away! This post is not for you. lol This post is intended for those who have no idea what a database is or what Microsoft SQL Server is or why they should care.
OK so now that we’ve got that out of the way on with the definition.

What is SQL Server? 
SQL Server is Microsoft’s RDBMS (Relational DataBase Management System). Like its competitor Oracle, Microsoft’s SQL Server is the database applications that a website or other application might use to store its list of Customers, Orders, Products and Employees. If you have booked a flight reservation on line (or by phone) or you have a Costco or Netflix member ship then you have no doubt been exposed to a database and perhaps even had your purchasing habits stored in the database and used to market products to you that the vendor knows you want. Put simply Microsoft SQL Server is a DataBase product used to store the data collected and used by a variety of computer software applications. It is a giant electronic file cabinet for your computer based data.
These days most organizations (Microsoft shops) are running Microsoft SQL Server 2005, Microsoft SQL Server 2008 or Microsoft SQL Server 2008R2. You can install Microsoft SQL Server 2008R2 software on a server in your office or you can run SQL Server in the Cloud! For more information about Microsoft SQL Server in the cloud see the following article: Introduction to the Cloud and Windows Azure

SQL Server Bulk Updates and Locking

When doing a large update of a table in a SQL Server database you may gain some performance by locking at the Table level instead of allowing SQL Server to do Page or Row level locking.
This can be accomplished using Table Hints. Table hints override the query optimizer default plan for the executed query. Table hints apply to data manipulation language (DML) statements such as Select, Insert, Update and Delete. Table hints should be specified in the FROM clause. Table hints allow the user to choose a specific locking method, indexes to use, or other options for DML statements.
It should be noted however that you are second guessing the query optimizer and if conditions change in the database that might make the query work better SQL Server may still use your less efficient table hint.
The ProDataMan way
If you use Optimizer Hints AKA Table Hints make sure to document any scripts or procedures that use table hints so they can be reviewed when changes to the database schema are made that might affect the Table Hint.

Index (zero based) must be greater than or equal to zero and less than the size of the argument list.

This is an error that you get when you try to view the built-in reports in SQL Server 2008 when the database compatibility is set to anything less than 2005(90).
To fix this and make the built-in reports work:
Right click on the database
Select properties
On the options tab change the compatibility level to SQL 2005 or later.

Auto File Growth by Percentage of file size

In SQL 2000 and before setting auto file growth to a percentage meant that the file would grow by 10% of the initial file size (regardless of how large the file had grown since its creation).
In SQL 2005 and later the growth by percentage setting is based on the “current” file size and not the “initial” file size.  The means the amount that the file grows will vary greatly from the time that the file is created to its size 2 years later.
The ProDataMan way
In SQL 2005 and later do not use a percentage setting used a fixed Megabyte setting based on the amount of data that your users typically add between maintenance periods.

Fix for SharePoint Search error: The search service is currently offline

psconfig -cmd upgrade -inplace b2b -wait
Fix is in the comments by “maxmorgan” on the link below

SQL Server 2005 Bug

I was just made aware of a new bug (oops I mean undocumented feature) in SQL 2005 that prevents the backup of File Groups that scroll beyond the viewable area of the Backup Dialog box.
This is not a problem if you have only a few file groups but if you have enough that you can’t see them all without scrolling then you will only be able to back up the ones that you can see before you touch the scroll bar.
Thanks Peter!!