Différences
Ci-dessous, les différences entre deux révisions de la page.
Les deux révisions précédentes Révision précédente | Prochaine révision Les deux révisions suivantes | ||
tuto:ipasserelle:web:squid_log_mysql [09/10/2012 14:22] dani [Schéma MySQL] |
tuto:ipasserelle:web:squid_log_mysql [09/10/2012 14:22] dani [Script de journalisation] |
||
---|---|---|---|
Ligne 73: | Ligne 73: | ||
use File::Tail; | use File::Tail; | ||
use DBI; | use DBI; | ||
+ | use URI; | ||
use Getopt:: | use Getopt:: | ||
use threads; | use threads; | ||
Ligne 134: | Ligne 135: | ||
die " | die " | ||
return $dbh; | return $dbh; | ||
+ | } | ||
+ | |||
+ | # escape chars for MySQL queries | ||
+ | sub mysql_escape { | ||
+ | my $string = shift; | ||
+ | $string =~ s|' | ||
+ | return $string; | ||
} | } | ||
Ligne 153: | Ligne 161: | ||
my ($squid_status, | my ($squid_status, | ||
my ($squid_connect, | my ($squid_connect, | ||
+ | |||
+ | # Extract domain part | ||
+ | # Usefull to have in a separate column for statistics | ||
+ | my $uri = URI-> | ||
+ | my $domain = mysql_escape($uri-> | ||
# MySQL escape | # MySQL escape | ||
# Shouldn' | # Shouldn' | ||
- | $timestamp =~ s|' | + | $timestamp |
- | $response_time =~ s|' | + | $response_time |
- | $client_ip =~ s|' | + | $client_ip |
- | $squid_status | + | $squid_status |
- | $http_status =~ s|' | + | $http_status |
- | $reply_size =~ s|' | + | $reply_size |
- | $request_method =~ s|' | + | $request_method = mysql_escape($request_method); |
- | $url =~ s|' | + | $url = mysql_escape($url); |
- | $username =~ s|' | + | $username |
- | $squid_connect =~ s|' | + | $squid_connect |
- | $server_ip =~ s|' | + | $server_ip |
- | $mime_type =~ s|' | + | $mime_type |
printlog(" | printlog(" | ||
Ligne 177: | Ligne 190: | ||
my $q = " | my $q = " | ||
" | " | ||
- | " | + | " |
" VALUES(' | " VALUES(' | ||
- | "' | + | "' |
my $qh = $dbh-> | my $qh = $dbh-> | ||
Ligne 203: | Ligne 216: | ||
$category = $1; | $category = $1; | ||
$client_ip =~ s/\/\-$//; | $client_ip =~ s/\/\-$//; | ||
+ | |||
+ | my $uri = URI-> | ||
+ | my $domain = mysql_escape($uri-> | ||
# MySQL escape | # MySQL escape | ||
- | $date_day =~ s|' | + | $date_day |
- | $date_time =~ s|' | + | $date_time = mysql_escape($date_time); |
- | $category =~ s|' | + | $category |
- | $url =~ s|' | + | $url |
- | $client_ip =~ s|' | + | $client_ip = mysql_escape($client_ip); |
- | $username =~ s|' | + | $username |
printlog(" | printlog(" | ||
Ligne 216: | Ligne 232: | ||
my $q = " | my $q = " | ||
- | " | + | " |
- | " VALUES(' | + | " VALUES(' |
my $qh = $dbh-> | my $qh = $dbh-> |