Have you ever got the following SQL Insert statement issues

"There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement."

Like me you probably went and counted your columns and then counted your values and realised they were the same so spent ages scratching your head trying to figure out what on earth was going on. Well here is how I managed to reproduce the issue.

insert into myTable ([columnA], [columnB ) values (1,2)

Did you see what I did in the above statement? I left the "]" off the end of "columnB" the above statement will give you the above mentioned error message. It was pretty much a typo on my part and it took me ages to find it in a large SQL Insert statement.

Hope this helps anyone who has gone about trying to solve the above problem and found they do have equal columns and values.

I've spent many weekends looking into this problem and thought I'd best blog it so some one else at least gets the benefit of it.

The issue I am talking about centres around the following error message.

"The connection cannot be used to perform this operation. It is either closed or invalid in this context."

The above error message has been a true bane to me. It was an issue on a classic ASP site that was quite happily ticking away for many years. I spent ages looking through the code ensuring that the SQL Connection was properly closed after each use and that ADODB.Recordsets were being used correctly. The error didn't make sense to me because the problem only happened occasionally and I was convinced it was either an issue with MDAC or the version of IIS (we had moved to a new server a few months ago) .

The solution
To cut a long story short, the solution I discovered  was in SQL Server 2005! Looking through the SQL server logs I discovered that after the last request SQL Server would "Auto Close" the connection and release resources. When the website made another request SQL would be busy spinning up which would then return the error above!

To stop this happening right click on your database in SQL Server Management Studio, select properties, then select Options and set "Auto Close" to false. I believe this option is now removed in newer versions of SQL Server.

I've been meaning to do a quick blog article about this for some time so I don't forget. I found setting up DasBlog on Windows Server 2008 pretty difficult. I currently run DasBlog on a Windows Server 2008 server with the following app pool ".Net Framework v2.0 Application pool in Integrated Mode"

One of the issues I discovered was setting up the permissions so that DasBlog could read and write the to the content folders. To do this follow the steps you find here http://learn.iis.net/page.aspx/624/application-pool-identities/ 

Basically you need to give the Application Pool that DasBlog is running under, permission to these folders. So for example setting permission on the content folder to allow the following user IIS AppPool\[your app pool name] read and write access.

Following the news on the LizaMoon injection attacks which have been publicised a lot in the press lately really made me want to find out more. Being a technically minded person I wanted to scrape past the general media version of what was happening and get down to what this means to people who run websites that might be vulnerable.

Reading posts on Stack Overflow it seemed to be the same old vulnerabilities that have been around for a very long time were once again being exploited.   Even though I have checked many sites I have worked on in the past, you can't help but wonder if there is anything you have forgotten. Security vulnerabilities in websites is not something you can say "yes I fixed it" its an on going battle (a bit like an arms race) where you have to keep up to date with the latest vulnerabilities.

One of the classic vulnerabilities I have seen from such attacks in the classic query string SQL injection attack. Take for example the following url on a website.

readmessage.asp?messageid=234

or

readmessage.php?messageid=234

There is nothing wrong with the above urls as long as what happens behind the scenes makes sure that whichever SQL database you are using be it MySQL or MS SQL Server is protected from bad input. Basically you cannot trust any input you get from the web.

One of the things I like doing with the above type of input before I even reach SQL is to ensure that the query string I am being sent in this case messageid is an integer. So in what ever language you are coding in, a very simple step is if messageid is indeed intended to be a query string test it to make sure it is. If you find it is not a query string you can either boot the user back to the page they came from or just send them to a generic error page that basically says that you can't understand what they wanted to do. Never display a detailed error message that divulges SQL statements and lines of code.

If messageid is supposed to be a string such as say a GUID? Test that all the characters used in the GUID are in a whitelist of acceptable characters first so for example accept A-Z, a-z, 0-9 and -  and reject everything else. In addition you can also HTML Encode or escape the input before sending it along to your code that persists it to SQL. In your code that does SQL persistence you can also help prevent such attacks by trying to use parameterised SQL statements instead of building your SQL update or insert statements as strings.

Other methods I have seen being used (although not a fan of) is where no text input is expected is to literally remove words and symbols such as "update", ), (, ',"insert" and "delete" this however can only be done where you definitely know these words are not intended as text values in a table field. If not used properly this could backfire and you could end up loosing data in sentences a user may have been innocently entering into a system.

The other thing to remember is just because the content went into the database safely doesn't mean that when you display that same content back to the user its going to be safe. Take for example a message board that uses a SQL server to store its messages, its pretty easy to escape what a user enters so that its perfectly preserved in SQL. Lets for example say that happened to be some JavaScript and that the JavaScript functionality was to redirect a user to a malicious site.  If you do not HTML Encode the message board text when displayed in the users browser you are basically putting users that trust your site at risk. HTML Encoding what you display to the user ensures that the user sees text of what is being presented and that the browser doesn't suddenly kick in and starts to execute the code its been given. Remember that this is just about any text you display to the user including the browser title tag which may be  something like this..

<title>Does anyone know how to make green widgets?</title>

The above if not encoded could quite easily be changed to the following by a malicious user post on your message board.

<title>Does anyone</title><script>document.location='somesite'</script><title></title>

The code above could potentially redirect a user to a malicious site.

This is more for my own reference more because I keep on forgetting how to do it and am constantly look it up all the time. If it helps you out, even better! And before you say "..but in MVC you can do it like this..". I know, but some of us still have to work with Webforms working with legacy apps. 

My main problem with DropDownLists in Repeater control examples on the net is they don't show you how to figure out which DropDownList in your Repeater list fired the SelectedIndexChanged event.

   1:   

   2:  // This is bound to the ItemDataBound event on the repeater.

   3:  protected void RepeaterBasketItems_ItemDataBound(object sender, RepeaterItemEventArgs e)

   4:  {

   5:      DropDownList DropDownListQuantity = 

   6:          (DropDownList)e.Item.FindControl("DropDownListQuantity");

   7:   

   8:      // hint after typing += you can hit TAB TAB in Visual 

   9:      // Studio for it to create the event handler for you.

  10:      DropDownListQuantity.SelectedIndexChanged 

  11:          += new EventHandler(DropDownListQuantity_SelectedIndexChanged);

  12:  }

  13:   

  14:  // Handles the Selected Index changed event. 

  15:  void DropDownListQuantity_SelectedIndexChanged(object sender, EventArgs e)

  16:  {

  17:      

  18:      DropDownList dropdown = (DropDownList)sender;

  19:   

  20:      // Cast the parent to type RepeaterItem

  21:      RepeaterItem repeaterRow = (RepeaterItem)dropdown.Parent;

  22:   

  23:      // Inside the RepeaterItem find a hidden Literal I 

  24:      // placed there which contains the Item Id of the row. 

  25:      // You could use the DataItem if this is being persisted

  26:      Literal LiteralItemId = (Literal)repeaterRow.FindControl("LiteralItemId");

  27:      

  28:      // Parse this string into an integer

  29:      int itemId = int.Parse(LiteralItemId.Text);

  30:      

  31:      //You can do some error handling here if the parse doesn't work..

  32:      

  33:      

  34:      // Get the value from the dropdown list.

  35:      int newQuantity = int.Parse(dropdown.SelectedValue);

  36:      

  37:      // Over here you could put your update method. that uses itemid and new quantity.

  38:  }