event_statistics and HTTP_USER_AGENT + HTTP_REFERER

[jemm4jemm]

Hello,

I had a look at errors on database logs and noticed this error:
ERROR: value too long for type character varying(255)
STATEMENT: INSERT INTO {PREFIX}visitors (sessID,day,time,ref,browser,ip) values ...

Found out that perfecly legal urls from shots.snap.com (you know, the system of mouseover snapshots for urls) generate urls that are nearly 500 characters long.

So one solution is to alter ref field as varying(500).

In addition I changed the lines 41 and 44 in serendipity_event_statistics.php (ver 1.44):

$useragent = substr($_SERVER['HTTP_USER_AGENT'],0,255);
$referer = substr($_SERVER['HTTP_REFERER'],0,500);

In fact for those having UTF-8 as database encoding, it would make sense to use:
$useragent = utf8_encode(substr($_SERVER['HTTP_USER_AGENT'],0,255));
$referer = utf8_encode(substr($_SERVER['HTTP_REFERER'],0,500));

The idea is that both user-agent string and referer cannot be controlled by blog owner and some common errors can be escaped.

S9Y is well coded and these possible errors show up only in database logs and the blog itself remains operational (and no error messages on client side). In fact trying common hacks as ' and ../ etc in user-agent string revealed that this already well escaped

[garvinhicking]

Hi!

Sadly varchar(255) ist the maximum for varchars on MySQL systems, so that would mean we'd have to write up a blob/text field for those fields, which would increase the size of each entry. And changing SQL keynames is always much work to do for sqlite, mysql and pgsql update files.

So I'd basically prefer to simply truncate referrers and user agent strings using substr() as you indicated. I just committed a fix for this in the current SVN.

Best regards,
Garvin

[jemm4jemm]

Sadly varchar(255) ist the maximum for varchars on MySQL systems, so that would mean we'd have to write up a blob/text field for those fields, which would increase the size of each entry. And changing SQL keynames is always much work to do for sqlite, mysql and pgsql update files.

That's OK, portability between databases yields restrictions

So I'd basically prefer to simply truncate referrers and user agent strings using substr() as you indicated. I just committed a fix for this in the current SVN.

I had a look at database and similar cropping might be needed for input into {PREFIX}spamblocklog and {PREFIX}comments tables, where there is content out of control of blog owner.