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!