Logging to MySQL without empty rows/skipped records?
- by Lee Ward
I'm trying to figure out how to make Squid proxy log to MySQL. I know ACL order is pretty important but I'm not sure if I understand exactly what ACLs are or do, it's difficult to explain, but hopefully you'll see where I'm going with this as you read!
I have created the lines to make Squid interact with a helper in squid.conf as follows:
external_acl_type mysql_log %LOGIN %SRC %PROTO %URI php /etc/squid3/custom/mysql_lg.php
acl ex_log external mysql_log
http_access allow ex_log
The external ACL helper (mysql_lg.php) is a PHP script and is as follows:
error_reporting(0);
if (! defined(STDIN)) {
define("STDIN", fopen("php://stdin", "r"));
}
$res = mysql_connect('localhost', 'squid', 'testsquidpw');
$dbres = mysql_select_db('squid', $res);
while (!feof(STDIN)) {
$line = trim(fgets(STDIN));
$fields = explode(' ', $line);
$user = rawurldecode($fields[0]);
$cli_ip = rawurldecode($fields[1]);
$protocol = rawurldecode($fields[2]);
$uri = rawurldecode($fields[3]);
$q = "INSERT INTO logs (id, user, cli_ip, protocol, url) VALUES ('', '".$user."', '".$cli_ip."', '".$protocol."', '".$uri."');";
mysql_query($q) or die (mysql_error());
if ($fault) {
fwrite(STDOUT, "ERR\n");
};
fwrite(STDOUT, "OK\n");
}
The configuration I have right now looks like this:
## Authentication Handler
auth_param ntlm program /usr/bin/ntlm_auth --helper-protocol=squid-2.5-ntlmssp
auth_param ntlm children 30
auth_param negotiate program /usr/bin/ntlm_auth --helper-protocol=squid-2.5-basic
auth_param negotiate children 5
# Allow squid to update log
external_acl_type mysql_log %LOGIN %SRC %PROTO %URI php /etc/squid3/custom/mysql_lg.php
acl ex_log external mysql_log
http_access allow ex_log
acl localnet src 172.16.45.0/24
acl AuthorizedUsers proxy_auth REQUIRED
acl SSL_ports port 443
acl Safe_ports port 80 # http
acl Safe_ports port 21 # ftp
acl Safe_ports port 443 # https
acl CONNECT method CONNECT
acl blockeddomain url_regex "/etc/squid3/bl.acl"
http_access deny blockeddomain
deny_info ERR_BAD_GENERAL blockeddomain
# Deny requests to certain unsafe ports
http_access deny !Safe_ports
# Deny CONNECT to other than secure SSL ports
http_access deny CONNECT !SSL_ports
# Allow the internal network access to this proxy
http_access allow localnet
# Allow authorized users access to this proxy
http_access allow AuthorizedUsers
# FINAL RULE - Deny all other access to this proxy
http_access deny all
From testing, the closer to the bottom I place the logging lines the less it logs. Oftentimes, it even places empty rows in to the MySQL table. The file-based logs in /var/log/squid3/access.log are correct but many of the rows in the access logs are missing from the MySQL logs. I can't help but think it's down to the order I'm putting lines in because I want to log everything to MySQL, unauthenticated requests, blocked requests, which category blocked a specific request.
The reason I want this in MySQL is because I'm trying to have everything managed via a custom web-based frontend and want to avoid using any shell commands and access to system log files if I can help it. The end result is to make it as easy as possible to maintain without keeping staff waiting on the phone whilst I add a new rule and reload the server!
Hopefully someone can help me out here because this is very much a learning experience for me and I'm pretty stumped.
Many thanks in advance for any help!
