8

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".

AlanSTACK
  • 4,735
  • 3
  • 33
  • 80
  • 1
    Related: http://stackoverflow.com/questions/15140416/user-agent-strings-what-should-i-capture?rq=1 http://stackoverflow.com/questions/23742935/user-agent-header-abbreviation-for-mysql-storing?rq=1 – Thilo Sep 18 '16 at 05:30
  • Use TEXT instead of varchar – Tirthraj Rao Sep 18 '16 at 06:47

1 Answers1

5

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
Blake O'Hare
  • 1,808
  • 11
  • 16
  • The maximum length for varchar in MySQL is 65535 bytes, not 255. http://dev.mysql.com/doc/refman/5.7/en/char.html – Bill Karwin Sep 18 '16 at 07:46
  • 1
    Ah, looks like it changed in 5.0 and I've been living a lie ever since :P Updated wording, but the general message is the same. – Blake O'Hare Sep 18 '16 at 09:19