Re: [squid-users] access-lists from mysql ?

I didn't find that anyone has created a flexible solution for use with
MySQL, so I wrote a small C++ program that will execute an specified
query with token replacement. You will need the MySQL development
libraries installed to compile it, but otherwise nothing special. If
no result set is found ERR is returned, if a result set is found OK is
returned.

GitHub: https://github.com/Kline-/tools/tree/master/c++/mysquid

Example usage with only one token passed, %DST=test.com:
external_acl_type mysquid1 %DST /path/to/mysquid "SELECT `url` FROM
`blocked_domains` WHERE INSTR('##TOK##',url);"

Which would result in MySQL executing the following:
SELECT `url` FROM `blocked_domains` WHERE INSTR('test.com',url);

##TOK## will be updated in each query with whatever Squid passes along
as %DST. Any number of tokens are supported and you can name them
whatever you want as long as they are ##enclosed##.

Example usage with two tokens passed, %SRC=192.168.1.8, %DST=test.com:
external_acl_type mysquid2 %SRC %DST /path/to/mysquid "SELECT * FROM
`blocked_src_dst` WHERE `ip` LIKE '##source##%' AND
INSTR('##destination##',url);"

Which would result in MySQL executing the following:
SELECT * FROM `blocked_src_dst` WHERE `ip` LIKE '192.168.1.8%' AND
INSTR('test.com',url);

I only use this on my home LAN, so I have no data on how well it may
or may not scale. With a low ttl I can now update the ACLs I use for
blocking websites in my home via any number of different SQL tools
rather than having to login to my proxy box, su, update acl files, and
reload Squid. Comments or improvements are welcome, I hope some others
will find this useful.

On Thu, Jan 24, 2013 at 5:52 PM, Amos Jeffries <squid3_at_treenet.co.nz> wrote:
> On 25/01/2013 2:44 a.m., Ali Jawad wrote:
>>
>> Thanks for that I did change the script to
>>
>> <?php
>> $f = fopen( 'php://stdin', 'r' );
>>
>> while( $line = fgets( $f ) ) {
>> echo "OK";
>> }
>>
>>
>> ?>
>>
>> while testing in command line using :
>>
>> /usr/bin/php myaclhelper.php
>>
>> Each time I press enter it returns OK, it keeps running and does not
>> exit. However in squid log I get :
>>
>> The MyAclHelper helpers are crashing too rapidly, need help!
>>
>> What I think is wrong is that I need to identify what needs to handle
>> the script, I.e. how does squid know this is a php script and not a
>> perl script ?
>
>
> Squid doesn't know. Squid passes the command line you configured in
> external_acl_type to the OS.
>
> The external_ac_type directive definition is:
> external_acl_type <options> <%FORMAT codes> <command line>
>
> Looking at your mail, I see you are testing with a different command line
> syntax than the squid.conf contains.
> You will get the same crash/abort that Squid sees if you ran the command
> line:
> /etc/squid/myaclhelper.php
>
> NOTE 1: To run a script without specifying the interpreter on the command
> line you need to add "#!/usr/bin/php" as the first line.
>
>
> NOTE 2: also be aware that PHP is not a good language for Squid helpers. PHP
> has a number of built-in timeouts (on std I/O sockets, on script run-time,
> and on Zend engine run-time) which automatically shut down the helper at
> various times from 30 seconds after starting to every hour. These sudden
> shutdowns can also cause Squid problems.
>
> Amos
Received on Sun Feb 03 2013 - 05:27:19 MST