8

I am modifying a login system and have been tasked to log all authentication requests for security purposes.

One of my plans is to record the user agent strings of incoming requests and translate them into readable displays, such as Request from: (Chrome/Windows) or Request from: (Firefox/Mac) whenever the user asks for this information. What length should my varchar field be in MySQL?

Is it necessary to have the entire user agent string to accurately determine the client's browser and operating system information in the future, or is there a specification for user agents that enables me to extract only a portion of it and discard the rest as unnecessary information?

AlanSTACK
  • 5,525
  • 3
  • 40
  • 99
  • 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 Barot Sep 18 '16 at 06:47

1 Answers1

7

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,863
  • 12
  • 16
  • 1
    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