How long should I make database field to save useragent strings?

Question

I am customizing a login system and for security reasons have been asked to log all authentication requests.

One thing I plan to do is recording the incoming user agent strings and translating them when requested into displays such as Request from: (Chrome/Windows) or Request from: (Firefox/Mac)

What length should my varchar field be in MySQL?

Do I need the entire useragent string to accurately determine the client's browser/os information in the future? Or is there some specification for useragents that allows me to extract only a substring from it and discard the rest of the "useless information".

Answer 1

Short answer: even if it's arbitrarily large, some User-Agents will still get cut off.

Longer answer: Unless you plan on putting an index on the User-Agent (I don't see why you would if it's only for record/display purposes), there's no reason to use a VarChar instead of a Text field which is long enough.

Alternatively, you could seek out a User-Agent parsing library which turns the string into something more useful such as "Chrome/Windows" or "Safari/iPhone" in which case you could reasonably use a shorter VarChar value.

There is absolutely no specification for User-Agent strings. They are completely arbitrary although many adhere to a few general patterns that you cannot always assume for all User Agents.

In summary, your 3 options are:

• get a library to parse them meaningfully
• pick an arbitrary varchar length and accept that some will be cut off
• use a text field