Cross Site Scripting and Cross Site Request Forgery

Jeff Atwood wrote a good article explaining the danger of XSRF and XSS

1) I want to confirm that checking UrlReferrer [hoping to prevent XSRF attack] is a waste of time. UrlReferrer can be spoofed by malicious user. Such spoofing can be done by combination of XSS and XSRF attack: injecting javascript into HTML output (XSS) on one web page and producing forged request (XSRF) pointing to another page of the same web site.
Jeff is also correct that legitimate users may have empty UrlReferrer. Rejecting such users is a mistake.

2) I agree that introducing parameters cuts off the most obvious XSRF attacks.
But if one of your pages is XSS vulnerable (allows javascript injection), then even if you have dynamic parameters to prevent XSRF (on another page), javascript can still read these dynamic parameters and re-submit them, so the request would succeed.
That's how Gmail was hacked -- the hacker used XSS vulnerability on some obscure Google's web site site in order to exploit XSRF vulnerability in Gmail).

Cure for "deadlocked!": learning to use proper SQL hints

Jeff Atwood's article Deadlocked! made me recall my painful experience with default locking settings in SQL Server.
Default select queries with (readcommitted) just don't work reliably in real life (because they cause deadlocks on regular basis).

It was hard to ignore scary stuff from SQL theoreticians about "dirty data", but eventually I've learned to use "with (nolock)" hint for most of my SELECT queries.
That doesn't mean that all queries should be written with nolock, but most of SQL queries should.
Especially if I'm writing web app.

In some situations I want to be sure that the data is consistent no matter what. In this case I use different locking hints. Which hints to use heavily depends on particular situation.

It's important to clearly understand what exactly locking does.
The locking model is relatively simple, however most of SQL Server locking tutorials are just terrible.

How-To learn SQL Server locking Tutorial

The best approach to learn about locking is to experiment:

1) Open three separate windows in SQL Management Studio.
Window One: for lock diagnostic sp_lock
Window Two: for pending transaction with Update/Delete/Insert query.
Window Three: for SELECT statements with different locking hints.

2) In Window One write and execute:
exec sp_lock
Take a look at the results.
Note what locks are there (these locks are generated by sp_lock itself).
Learn about what these locks mean (Google is your friend).

3) In Window Two (that's separate connection to SQL server) write and execute:
============
begin transaction
update MyTestTable set MyColumn = 1
-- rollback transaction
============
Note, that "rollback transaction" is commented out, so transaction won't complete.

4) Return back to Window One and execute sp_lock.
Note what additional locks you see in sp_lock results.

5) In Window Three execute:
select * from MyTestTable with (updlock)
The query won't complete, because it's locked by pending transaction in Window Two.

6) Switch to Window Two and cancel transaction by executing "rollback transaction"
Make sure that SELECT transaction in Window Three is completed now.

7) Try different combinations of locks (readcommitted), (nolock), (updlock), (repeatableread), (serializable), ... in Window Three.
Try other different update/insert/delete statements in Window Two.

8) Try to use "commit transaction" instead of "rollback transaction" in Window Two and see how it would affect SELECT results in Window Three.

9) Try to run:
begin transaction
select * from MyTestTable with (updlock)
in Window Two and:
select * from MyTestTable with (serializable)
in Window Three.

10) Keep monitoring locks using sp_Lock in Window One.

11) Do free-style experimenting and research problem on Google when you are getting unexpected results.

RackSpace - are they really the best web hosting?

About a month ago I switched from Virtual Private Hosting on webhost4life to dedicated hosting on RackSpace.
It was definitely an improvement, but I'm still dissatisfied.


Here are painful parts of my experience with RackSpace:

1) RackSpace wanted me to sign paper contract (WebHost4Life didn't require that). That paperwork took almost a day (several hours of my efforts + some wait time). Sales guy couldn't open several versions of "Microsoft Office Image Writer" that I emailed to him, so I had to resend the document in different format.

2) It is a little unpleasant to deal with RackSpace sales guys. They forget (or "forget") to answer some of my questions; use some slightly unpleasant pushy sales techniques. Is it typical for any sales reps, not only RackSpace's sales?

3) After the contract was signed, it took RackSpace almost 4 days to install the server. I signed the contract Wednesday July 3rd 2008 and was hoping that on Saturday-Sunday night I'll be able to move my web site (www.postjobfree.com) to RackSpace. But RackSpace set up my server only on Monday - not convenient time for me and my users to do the move.

4) RackSpace promised me that they would help with the migration. They gave some tips, but not all of them were good. For example, they suggested me to shut down my web site for few hours while I will copy my database. Not a good approach for 24/7 service. So, basically I was mostly left on my own with the migration.
Fortunately, I used advise of Omar Al Zabir about smooth web hosting migration
Ironically -- it was Omar's recommendation to use RackSpace for web hosting that made me pick them.

5) Average response to my ticket requests is about few hours (2-3 may be?). Sometimes ticket response time is shorter; sometimes it's longer (up to a day or even more in some cases). It's an improvement in comparison with WebHost4Life, but is that really the best in hosting industry?

6) Most of the time responses are good, rarely great, and in some cases responses are incompetent :-(
For example, I asked RackSpace if it would be a good idea to run SMTP server on separate IP address (same physical machine). A RackSpace guy replied that it would be a good idea. So we did the switch. But it turned out that RackSpace cannot monitor ports on another IP addresses (only on primary IP address). So we updated SMTP server settings again to allow listen both IP addresses. That caused SMTP server to use primary IP address to send emails, but at this point smtp.postjobfree.com DNS record was pointing to second IP address, and this caused painful issues with spam filters on some servers. In the end we returned back to using primary IP for SMTP, but went through some pain because of incompetent advice.

7) RackSpace seems to be not really good in analyzing past problems. They simply ignored my request about digging into this incompetent SMTP advice. Nothing like "sorry, we screwed up and would do this and that to prevent it in the future". Nothing like "sorry, it was misunderstanding and you (me) should do this and that to avoid problems in the future". I would understand if cheap hosting provider would skip such "past failure analysis". But if hosting provider claims "fanatical support" - I expect to do a little better.
Well, may be it happenned because I still didn't have a chance to talk with my account manager. RackSpace doesn't have one for me yet (after being with RackSpace for a month):

8) Rackspace's ticketing system creates unneeded noise. For example, after I create a ticket on my.rackspace.com, it adds meaningless auto-response to the ticket and sends me notification email. If I update a ticket, I get notification email again. Why would I need notification about ticket updates I made myself? That's distracting.

9) Maintenance downtime. :-(
I mentioned already that RackSpace considered few hours web site downtime during migration as "ok" practice. That attitude goes toward other maintenance things too. Today they installed hardware firewall on my server. They brought down my server for almost an hour (!). Could anybody explain me why installation of hardware firewall should bring server down for almost an hour? It should be less than a minute downtime, or preferably zero downtime. That was disappointing. I managed to migrate web server from different hosting with no downtime (I copied 4 GB database between WebHost4Life and RackSpace and still managed to avoid downtime) and now trivial installation of hardware firewall caused almost an hour downtime:

10) The hardware firewall installation caused another issue as well -- my web site was not able to send out emails after the firewall installation. RackSpace didn't notice that, because their SMTP port monitoring didn't catch the issue. We noticed it few hours later and reported in a ticket. We got no reply for couple of hours, so I had to call RackSpace and remind that the issue is still there. They were not very good in pinpointing the issue. They were trying to re-test SMTP server and it worked. So we (at PostJobFree) had to find it out the problem ourselves. The problem was that smtp.postjobfree.com could be pinged from every computer, but not from my server itself, because hardware firewall didn't resolve the request from my server back to the server itself.
RackSpace techies couldn't grasp that for a while even after I pointed them into that direction. Eventually they recommended to update my application and made it use my new 192.168.x.x IP address for sending emails. Imagine that: update and redeploy my application web app to accommodate to hardware changes. And do it in the hurry after(!) hardware firewall is installed.
I suggested better solution: simply add one record to C:\WINDOWS\system32\drivers\etc\hosts:
127.0.0.1 smtp.postjobfree.com
RackSpace techies still cannot grasp that solution and comment on it. Fortunately my solution works so far.

11) There were few other minor issues, but I think my saga is getting too long already.

On the bright side:

1) When my server works (and it usually works) it works really fast. I'm happy with the speed so far. Though I'm not sure if I should attribute it to RackSpace or to dedicated server. With WebHost4Life SQL server was shared with 4 other clients and in the end I was getting 50+ timeouts/day.

2) Some RackSpace folks taught me some useful stuff, for example about DNS [re-]configuration.

3) RackSpace is expensive, but it's not THAT expensive. I got my server + SQL Server license + hardware firewall for a little over $600/mo (with 1 year contract)


So, what do you think, is it typical to have issues like these with any hosting provider, or there are better hosting providers out there?

Any other comments?


Update (2008 August 03):
Problems with RackSpace are getting worse.

RackSpace technician configured my SMTP server as open relay
:-(

That's how it sounded on the ticket:
"We did find a setting in your SMTP that was set incorrectly, and we corrected that."

In fact, SMTP server was configured properly and the problem was with DNS configuration. Instead of fixing DNS (after installing Hardware Firewall) RackSpace guys made the problems much worse by turning my SMTP server into spam-machine and enabling prompt access to all spammers through newly installed hardware firewall.

Crazy stuff.

TechCrunch pushes Start-ups to fail

If you want to announce your start-up on TechCrunch50 the only thing you need to do is to sell your soul to the Devil deny your prospective customers access to your web site. Here's the quote:

Rules of participation in TechCrunch50
Until its presentation on stage at the conference, a company will keep its site password protected with limited private access to alpha or beta users for testing purposes.

That means that you have to decrease your popularity, get less testing and less feedback, get less word of mouth, and ultimately get less revenue.

On the other hand, your participation in TechCrunch50 would be free.

Free is good, isn't it?

Encrypt App.config

// Run "Encryption.EncryptConnectionStrings();" in the beginning of your winservice or winforms app.
// You need to add .NET reference to System.configuration.dll
using System.Configuration;
public static class Encryption
{
public static void EncryptConnectionStrings()
{
Configuration configuration = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
ConfigurationSection section = configuration.GetSection("connectionStrings");
if (!section.SectionInformation.IsProtected)
{
section.SectionInformation.ProtectSection("DataProtectionConfigurationProvider");
section.SectionInformation.ForceSave = true;
configuration.Save(ConfigurationSaveMode.Modified);
}
}

public static void DecryptConnectionStrings()
{
Configuration configuration = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
ConfigurationSection section = configuration.GetSection("connectionStrings");
if (section.SectionInformation.IsProtected)
{
section.SectionInformation.UnprotectSection();
section.SectionInformation.ForceSave = true;
configuration.Save(ConfigurationSaveMode.Modified);
}
}
}

How to encrypt and decrypt string in .NET application

using System;
using System.IO;
using System.Security.Cryptography;
using System.Text;

namespace MyCompany.Library
{
public class Encryption
{
public static string EncryptString(string valueToEncrypt)
{
return EncryptString(valueToEncrypt, "My password key");
}

public static string DecryptString(string valueToDecrypt)
{
return DecryptString(valueToDecrypt, "My password key");
}

public static string EncryptString(string valueToEncrypt, string secretPhrase)
{
CryptoStream encryptStream = null; // Stream used to encrypt
RijndaelManaged rijndael = null; // Rijndael provider
ICryptoTransform rijndaelEncrypt = null; // Encrypting object
MemoryStream memStream = new MemoryStream(); // Stream to contain data
byte[] key;
byte[] IV;
GenerateKey(secretPhrase, out key, out IV);
try
{
if( valueToEncrypt.Length > 0 )
{
// Create the crypto objects
rijndael = new RijndaelManaged();
rijndael.Key = key;
rijndael.IV = IV;
rijndaelEncrypt = rijndael.CreateEncryptor();
encryptStream = new CryptoStream(
memStream, rijndaelEncrypt, CryptoStreamMode.Write);

// Write the encrypted value into memory
byte[] input = Encoding.UTF8.GetBytes(valueToEncrypt);
encryptStream.Write(input, 0, input.Length);
encryptStream.FlushFinalBlock();

// Retrieve the encrypted value and return it
return Convert.ToBase64String(memStream.ToArray());
}
else
{
return "";
}
}
finally
{
if (rijndael != null) rijndael.Clear();
if (rijndaelEncrypt != null) rijndaelEncrypt.Dispose();
if (memStream != null) memStream.Close();
}
}

public static string DecryptString(string valueToDecrypt, string secretPhrase)
{
CryptoStream decryptStream = null; // Stream used to decrypt
RijndaelManaged rijndael = null; // Rijndael provider
ICryptoTransform rijndaelDecrypt = null; // decrypting object
MemoryStream memStream = null; // Stream to contain data
byte[] key;
byte[] IV;
GenerateKey(secretPhrase, out key, out IV);
try
{
if( valueToDecrypt.Length > 0 )
{
// Create the crypto objects
rijndael = new RijndaelManaged();
rijndael.Key = key;
rijndael.IV = IV;

//Now decrypt the previously encrypted message using the decryptor
// obtained in the above step.

// Write the encrypted value into memory
byte[] encrypted = Convert.FromBase64String(valueToDecrypt);
memStream = new MemoryStream(encrypted);


rijndaelDecrypt = rijndael.CreateDecryptor();
decryptStream = new CryptoStream(memStream, rijndaelDecrypt, CryptoStreamMode.Read);

byte[] fromEncrypt = new byte[encrypted.Length];

//Read the data out of the crypto stream.
decryptStream.Read(fromEncrypt, 0, fromEncrypt.Length);

// Retrieve the encrypted value and return it
string decryptedString = new string(Encoding.UTF8.GetChars(fromEncrypt));
return decryptedString.TrimEnd(new char[] {'\0'});
}
else
{
return "";
}
}
finally
{
if (rijndael != null) rijndael.Clear();
if (rijndaelDecrypt != null) rijndaelDecrypt.Dispose();
if (memStream != null) memStream.Close();
}
}

/// Generates an encryption key based on the given phrase. The
/// phrase is hashed to create a unique 32 character (256-bit)
/// value, of which 24 characters (192 bit) are used for the
/// key and the remaining 8 are used for the initialization vector (IV).
private static void GenerateKey(string secretPhrase, out byte[] key, out byte[] IV)
{
// Initialize internal values
key = new byte[24];
IV = new byte[16];

// Perform a hash operation using the phrase. This will
// generate a unique 32 character value to be used as the key.
byte[] bytePhrase = Encoding.ASCII.GetBytes(secretPhrase);
SHA384Managed sha384 = new SHA384Managed();
sha384.ComputeHash(bytePhrase);
byte[] result = sha384.Hash;

// Transfer the first 24 characters of the hashed value to the key
// and the remaining 8 characters to the intialization vector.
for (int index=0; index<24; index++) key[index] = result[index];
for (int index=24; index<40; index++) IV[index-24] = result[index];
}
}
}

Generate Sequential GUIDs for SQL Server 2005 in C#

Why generate sequential GUID in C#?

Originally, uniqueidentifier (GUID) column in SQL Server was not supposed to be sequential. But in my case, having sequential GUID is quite useful.
My application needs to know, what record was inserted first.

Fortunately, SQL Server 2005 supports "default newsequentialid()" constraint, that makes uniqueidentifier column grow sequentially [with every inserted record].

That worked quite well for me, until I decided to generate sequential GUID in C#.
(I needed it, because I use SqlBulkCopy and try to save two tables that share the same generated GUID key).

That turned out to be a tricky task. The reason -- .NET and SQL Server treat GUIDs quite different. In particular, they sort them quite differently.

Searching for solution

Alberto Ferrari's post How are GUIDs sorted by SQL Server? gave me a good idea about how to handle the problem.

I used modified Alberto's SQL code to find out what C#.NET GUID bytes are more [or less] significant from SQL Server 2005 ORDER BY clause perspective.

With UIDs As (
Select ID = 3, UID = cast ('01000000-0000-0000-0000-000000000000' as uniqueidentifier)
Union Select ID = 2, UID = cast ('00010000-0000-0000-0000-000000000000' as uniqueidentifier)
Union Select ID = 1, UID = cast ('00000100-0000-0000-0000-000000000000' as uniqueidentifier)
Union Select ID = 0, UID = cast ('00000001-0000-0000-0000-000000000000' as uniqueidentifier)
Union Select ID = 5, UID = cast ('00000000-0100-0000-0000-000000000000' as uniqueidentifier)
Union Select ID = 4, UID = cast ('00000000-0001-0000-0000-000000000000' as uniqueidentifier)
Union Select ID = 7, UID = cast ('00000000-0000-0100-0000-000000000000' as uniqueidentifier)
Union Select ID = 6, UID = cast ('00000000-0000-0001-0000-000000000000' as uniqueidentifier)
Union Select ID = 8, UID = cast ('00000000-0000-0000-0100-000000000000' as uniqueidentifier)
Union Select ID = 9, UID = cast ('00000000-0000-0000-0001-000000000000' as uniqueidentifier)
Union Select ID = 10, UID = cast ('00000000-0000-0000-0000-010000000000' as uniqueidentifier)
Union Select ID = 11, UID = cast ('00000000-0000-0000-0000-000100000000' as uniqueidentifier)
Union Select ID = 12, UID = cast ('00000000-0000-0000-0000-000001000000' as uniqueidentifier)
Union Select ID = 13, UID = cast ('00000000-0000-0000-0000-000000010000' as uniqueidentifier)
Union Select ID = 14, UID = cast ('00000000-0000-0000-0000-000000000100' as uniqueidentifier)
Union Select ID = 15, UID = cast ('00000000-0000-0000-0000-000000000001' as uniqueidentifier)
)
Select * From UIDs Order By UID


Note, that first line with ID=3 corresponds to:
new Guid(new bytes[16]{ 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 }
That means:
(new Guid("01000000-0000-0000-0000-000000000000").ToByteArray()[3] == 1)


Now, when I run modified Alberto's query, I'm getting the following sequence:
3, 2, 1, 0, 5, 4, 7, 6, 9, 8, 15, 14, 13, 12, 11, 10

That means, that GUID's byte #3 is the least significant and GUID's byte #10 is the most significant [from SQL Server ORDER BY clause perspective].

Final solution

Now we're ready to write C# code, that would sequentially increment any given GUID.
I also made it more convenient to increment GUID, by using "++" operator.
Here's how it's used:

private void Test()
{
SequentialGuid = new SequentialGuid(Guid.Empty);
SequentialGuid++;
}


and C# code that increments GUIDs sequentially:

public class SequentialGuid
{
Guid _CurrentGuid;
public Guid CurrentGuid
{
get { return _CurrentGuid; }
}

public SequentialGuid()
{
_CurrentGuid = Guid.NewGuid();
}

public SequentialGuid(Guid previousGuid)
{
_CurrentGuid = previousGuid;
}

public static SequentialGuid operator ++(SequentialGuid sequentialGuid)
{
byte[] bytes = sequentialGuid._CurrentGuid.ToByteArray();
for (int mapIndex = 0; mapIndex < 16; mapIndex++)
{
int bytesIndex = SqlOrderMap[mapIndex];
bytes[bytesIndex]++;
if (bytes[bytesIndex] != 0)
{
break; // No need to increment more significant bytes
}
}
sequentialGuid._CurrentGuid = new Guid(bytes);
return sequentialGuid;
}

private static int[] _SqlOrderMap = null;
private static int[] SqlOrderMap
{
get
{
if (_SqlOrderMap == null)
{
_SqlOrderMap = new int[16] { 3, 2, 1, 0, 5, 4, 7, 6, 9, 8, 15, 14, 13, 12, 11, 10 };
// 3 - the least significant byte in Guid ByteArray [for SQL Server ORDER BY clause]
// 10 - the most significant byte in Guid ByteArray [for SQL Server ORDERY BY clause]
}
return _SqlOrderMap;
}
}
}

SqlBulkCopy in CLR SQL Stored Procedure

When I was trying to run SqlBulkCopy in CLR (C#) SQL Stored Procedure, I've got at exception:

System.InvalidOperationException: The requested operation is not available on the context connection.
System.InvalidOperationException:
at System.Data.SqlClient.SqlBulkCopy.CreateOrValidateConnection(String method)
at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount)
at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowState rowState)
at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table)

It seems that it's impossible to run SqlBulkCopy within "context connection" ...

Here's the only tip I was able to find:

http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-clr/9/SqlBulkInsert

> Does SqlBulkInsert not run in CLR? Why does not?

Nope. It (BulkInsert) uses a different provider library if I'm understanding
things correctly.

Not much ... I guess CLR Stored Procedures work fast enough already...

Batch Update doesn't work in CLR Stored Procedures [on context connection] either.

When I tried to execute this code:

SqlDataAdapter da = new SqlDataAdapter();
cmd.UpdatedRowSource = UpdateRowSource.None;
da.UpdateCommand = cmd;
da.UpdateBatchSize = 1000;
da.Update(table);

I got this exception:

"Batching updates is not supported on the context connection."

Note the grammar: "... updates IS not supported ..."

It seems that nobody really cares about batch functionality in CLR SP.


What do you think? Please, let me know.