Database fields size

Jul 15, 2010 at 8:48 AM
Hello, We use the AntiXss library to encode user inputs before sending them to the database. Unfortunately we have sometimes noticed some free-text inputs were refused by the database because once encoded their size exceeded the size of the DB field. Example: in my database I have a field of type NVARCHAR(20) and in my ASP.NET application I have an input control (asp:TextBox) set with a maximum length of 20 also. If user types 20 characters having to be encoded before going into the DB, the resulting string will have a size of 20 x 6 = 120 characters! My DBA and I would like to know what are your recommendations and best approach to handle this problem. Thanks in advance, Spirou
Jul 15, 2010 at 1:34 PM

Usually I recommend you don't store encoded data in the database for a couple of reasons;

  1. If you encode before storing your data is "stuck" in that format; for example it would be stuck in HTML and you may decide you now want to render the data as RSS. This is then a problem.
  2. If there's a bug in the encoding library and I fix it you don't see the benefit for older data, as it's already been encoding and may have buggy encoding.
  3. Un-encoded data is easier to search!

So I would strongly suggest you store "raw" input in the database, and when it's pulled out you encoded it before display. Of course this has a performance cost, so if you must encode the the maximum size for an encoded entity is 8 times the size of a character, or, if you are using named entities 10 times the size of a character, so multiply your field widths accordingly.