Apostrophe got through filter in C#

Question

I'm really sorry to do this, but this problem represents a possibly exploitable security issue on a site I work on, so I'm posting this with a new account.

We have a script that takes in user comments (all comments are in English). We have amassed approximately 3,000,000 comments in two years. I was checking the comments table for any signs of malicious behavior and this time I did a scan for the apostrophe. This should have been converted to an HTML entity (') in all cases, but I found 18 records (out of 3 million) in which the character survived. The thing that is really breaking my head is that in one of these 18 comments, one apostrophe actually was successfully converted -- the other survived.

This indicates to me that we have a possible XSS vulnerability.

My theory for what's going on is that a user is hitting the page on a computer system that uses a non-Western codepage and that their browser is ignoring our page's utf-8 charset specification, that his/her input is not getting converted to the server's local codepage until it hits the database (so C# is not recognizing the character as an apostrophe and thus is unable to convert it, but the database is when it tries to write it to a LATIN1 table). But that's a total guess.

Has anyone encountered this before or know what's going on?

And more importantly, does anyone know how I can test my script? Moving to HttpUtility will probably fix the situation, but until I know how this happened, I can't know the problem is fixed. I need to be able to test this to know our solution works.

Edit

Wow. Already at 20 points, so I can edit my question.

I mentioned in one of my comments that I found several characters that appear to be problematic. They include: 0x2019, 0x02bc, 0x02bb, 0x02ee, 0x055a, 0xa78c. These pass right through our filter. Unfortunately, they pass right through all HttpUtility encoding methods too. But once they get inserted into the database, they get converted either into a actual apostrophe or into a "?".

In review, I think the problem is that these characters do not, in themselves, pose a threat, so HttpUtility has no reason to convert them. In a block of Javascript they are harmless. In a block of HTML they are just character data and are harmless. And in a block of SQL they are harmless (if the database shares the same codepage). The problem for us is that because the codepage we're using in the database is different, the insertion process in the database involves converting these "unprintable" characters into either "known equivalents" (which in this case are "bad") and "unknown equivalents" (which get rendered as "?"). This totally blind-sided us and I'm a little disappointed in MS for not building more into their HttpUtility encoding functions.

I think the solution is to change the collation of the affected tables. But if anyone else has a better idea, please post below.

Answer 1

You're filtering in the wrong place, IMHO. The database should contain the actual characters the user entered. You should leave the escaping of HTML to the presentation layer, which knows better how to do it.

Answer 2

This sounds like your storage inside the DBMS is using a non-unicode column type whilst .net is using unicode.

You could within .net initially convert the unicode to your dbms' collation and then back to unicode to remove any unsupported characters at the application level rather than leaving that to the dbms/connector.

var encoding = Encoding.GetEncoding("Latin1") //this should be matched to the column's collation
foo = encoding.GetString (encoding.GetBytes (foo)); // couldn't see a more efficient way to do this.

Though as previously mentioned, ideally you would store the actual characters in the DBMS and leave the encoding to the presentation step. Of which you'd try and set up the framework in such a way you can't forget to encode string data, eg asp.net 4 uses <%: %>, JSON using JSON.Net rather than string concatenation, for XML XLINQ, etc.