diff options
author | tilghman <tilghman@f38db490-d61c-443f-a65b-d21fe96a405b> | 2008-03-11 20:58:42 +0000 |
---|---|---|
committer | tilghman <tilghman@f38db490-d61c-443f-a65b-d21fe96a405b> | 2008-03-11 20:58:42 +0000 |
commit | 793a4505093a2d12fed3e1a5f3e0d6c2315214b4 (patch) | |
tree | 2bcebe05fd48c9f4cadc57fba2f977941795b603 | |
parent | 0f6d097b16684433020b4c46e3839d56c44a5cda (diff) |
Add contributed script for separation of database access from Asterisk
git-svn-id: http://svn.digium.com/svn/asterisk/trunk@107721 f38db490-d61c-443f-a65b-d21fe96a405b
-rw-r--r-- | configs/dbsep.conf.sample | 35 | ||||
-rwxr-xr-x | contrib/scripts/dbsep.cgi | 209 |
2 files changed, 244 insertions, 0 deletions
diff --git a/configs/dbsep.conf.sample b/configs/dbsep.conf.sample new file mode 100644 index 000000000..00d187d14 --- /dev/null +++ b/configs/dbsep.conf.sample @@ -0,0 +1,35 @@ +# +# Configuration file for dbsep.cgi +# +# The purpose of this file is to provide realtime access to a database, +# possibly through ODBC, without needing to load the ODBC drivers into +# Asterisk, since there are several backend drivers which are rather +# buggy. +# +# We accomplish this separation by using the res_config_curl realtime +# driver to connect to a server running dbsep.cgi (or another, which +# implements the same protocol). +# +# This file contains the information necessary to configure dbsep.cgi. +# +# +# Once installed to a web server, you'll need to preload func_curl.so +# and res_config_curl.so in modules.conf and configure extconfig.conf: +# +# voicemail => curl,http://server/path/to/dbsep.cgi/voicemail +# sippeers => curl,http://server/path/to/dbsep.cgi/sipfriends +# sipusers => curl,http://server/path/to/dbsep.cgi/sipfriends +# + +# The Data Source Name, as specified by the Perl DBI module. +dsn=somedsn + +# Connected database user +dbuser=someuser + +# And its password +dbpass=password + +# For most databases, this is fine. Set to 'no' for Sybase or MS SQL Server. +backslash_is_escape=yes + diff --git a/contrib/scripts/dbsep.cgi b/contrib/scripts/dbsep.cgi new file mode 100755 index 000000000..8d23d3d03 --- /dev/null +++ b/contrib/scripts/dbsep.cgi @@ -0,0 +1,209 @@ +#!/usr/bin/perl +# +# Copyright (c) 2008 Digium, Inc. +# +# Tilghman Lesher <dbsep.cgi@the-tilghman.com> +# +# See http://www.asterisk.org for more information about +# the Asterisk project. Please do not directly contact +# any of the maintainers of this project for assistance; +# the project provides a web site, mailing lists and IRC +# channels for your use. +# +# This program is free software, distributed under the terms of +# the GNU General Public License Version 2. See the LICENSE file +# at the top of the source tree. +# +# $Id$ +# + +use CGI; +use DBI; +use strict; + +my ($cgi, $dbh, %cfg, $table, $mode); + +# The following settings are expected: +# +# dsn=<some valid dsn> +# dbuser=<user> +# dbpass=<passwd> +# backslash_is_escape={yes|no} +# +open CFG, "</etc/asterisk/dbsep.conf"; +while (<CFG>) { + chomp; + next if (m/^[#;]/); + next if (m/^\s*$/); + my ($name,$value) = split '='; + $cfg{lc($name)} = $value; +} +close CFG; + +$cgi = new CGI; + +$ENV{PATH_INFO} =~ m/\/([^\/]*)\/([^\/]*)$/; +($table, $mode) = ($1, lc($2)); + +print STDERR "PATH_INFO=$ENV{PATH_INFO}, table=$table, mode=$mode\n"; + +if ($mode eq 'single') { + # All parameters as POST + my ($sql, $sth, $row, @answer); + $sql = "SELECT * FROM $table WHERE " . join(" AND ", cgi_to_where_clause($cgi, \%cfg)); + $dbh = DBI->connect($cfg{dsn}, $cfg{dbuser}, $cfg{dbpass}); + $sth = $dbh->prepare($sql) || throw_error("Invalid query: $sql"); + $sth->execute() || throw_error("Invalid query: $sql"); + $row = $sth->fetchrow_hashref(); + foreach (keys %$row) { + push @answer, encode($_) . "=" . encode($row->{$_}); + } + $sth->finish(); + $dbh->disconnect(); + print "Content-type: text/plain\n\n"; + print join("&", @answer) . "\n"; +} elsif ($ENV{PATH_INFO} =~ m/multi$/) { + # All parameters as POST + my ($sql, $sth, @answer); + $sql = "SELECT * FROM $table WHERE " . join(" AND ", cgi_to_where_clause($cgi, \%cfg)); + $dbh = DBI->connect($cfg{dsn}, $cfg{dbuser}, $cfg{dbpass}); + $sth = $dbh->prepare($sql) || throw_error("Invalid query: $sql"); + $sth->execute() || throw_error("Invalid query: $sql"); + print "Content-type: text/plain\n\n"; + while (my $row = $sth->fetchrow_hashref()) { + @answer = (); + foreach (keys %$row) { + push @answer, encode($_) . "=" . encode($row->{$_}); + } + print join("&", @answer) . "\n"; + } + $sth->finish(); + $dbh->disconnect(); +} elsif ($ENV{PATH_INFO} =~ m/update$/) { + # where clause in GET, update parameters in POST + my (%get, @get, $sql, $name, $value, $affected); + foreach (split '&', $ENV{QUERY_STRING}) { + ($name, $value) = split '='; + $name = decode($name); + next if (!isname($name)); + $value = escape_value(decode($value)); + if ($name =~ m/ /) { + push @get, "$name '$value'"; + } else { + push @get, "$name='$value'"; + } + $get{$name}++; + } + $sql = "SELECT " . join(",", cgi_to_where_clause($cgi, \%cfg, \%get)) . " FROM $table WHERE " . join(" AND ", @get); + $dbh = DBI->connect($cfg{dsn}, $cfg{dbuser}, $cfg{dbpass}); + $affected = $dbh->do($sql); + $dbh->disconnect(); + print "Content-type: text/html\n\n$affected\n"; +} elsif ($ENV{PATH_INFO} =~ m/store$/) { + # All parameters as POST + my (@param, $sql, @fields, @values, $affected); + foreach my $param (cgi_to_where_clause($cgi, \%cfg)) { + my ($name, $value) = split /=/, $param; + push @fields, $name; + push @values, $value; + } + $sql = "INSERT INTO $table (" . join(",", @fields) . ") VALUES (" . join(",", @values) . ")"; + $dbh = DBI->connect($cfg{dsn}, $cfg{dbuser}, $cfg{dbpass}); + $affected = $dbh->do($sql); + $dbh->disconnect(); + print "Content-type: text/html\n\n$affected\n"; +} elsif ($ENV{PATH_INFO} =~ m/destroy$/) { + # All parameters as POST + my ($sql, $affected); + $sql = "DELETE FROM $table WHERE " . join(" AND ", cgi_to_where_clause($cgi, \%cfg)); + $dbh = DBI->connect($cfg{dsn}, $cfg{dbuser}, $cfg{dbpass}); + $affected = $dbh->do($sql); + $dbh->disconnect(); + print "Content-type: text/html\n\n$affected\n"; +} elsif ($ENV{PATH_INFO} =~ m/static$/) { + # file parameter in GET, no POST + my (@get, $filename, $sql, $sth); + @get = split '&', $ENV{QUERY_STRING}; + foreach (@get) { + my ($name, $value) = split '='; + if (decode($name) eq 'file') { + $filename = decode($value); + last; + } + } + $sql = "SELECT cat_metric, category, var_name, var_val FROM $table WHERE filename=" . escape_value($filename) . " AND commented=0 ORDER BY cat_metric DESC, var_metric ASC, category, var_name"; + $dbh = DBI->connect($cfg{dsn}, $cfg{dbuser}, $cfg{dbpass}); + $sth = $dbh->prepare($sql) || throw_error("Invalid query: $sql"); + $sth->execute() || throw_error("Invalid query: $sql"); + print "Content-type: text/plain\n\n"; + while (my $row = $sth->fetchrow_hashref()) { + my @answer = (); + foreach (keys %$row) { + push @answer, encode($_) . "=" . encode($row->{$_}); + } + print join("&", @answer) . "\n"; + } + $sth->finish(); + $dbh->disconnect(); +} else { + print "Content-type: text/plain\n\nUnknown query\n"; +} + +sub encode { + my ($stuff) = @_; + $stuff =~ s/([^a-zA-Z0-9_\.])/uc sprintf("%%%02x",ord($1))/eg; + return $stuff; +} + +sub decode { + my ($stuff) = @_; + $stuff =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg; + return $stuff; +} + +sub isname { + my ($name) = @_; + if ($name =~ m#[^A-Za-z0-9_ ]#) { + return 0; + } else { + return 1; + } +} + +sub escape_value { + my ($value, $cfg) = @_; + if ($cfg->{backslash_is_escape} =~ m/^(no|0|false)$/i) { + $value =~ s#'#''#g; + } else { + $value =~ s#(['\\])#$1$1#g; + } + return $value; +} + +sub cgi_to_where_clause { + my ($cgi, $cfg, $get) = @_; + my @param = (); + + foreach my $name ($cgi->param()) { + my $value = escape_value($cgi->param($name), $cfg); + + # Ensure name isn't funny-like + next if (!isname($name)); + next if ($get->{$name}); + + if ($name =~ m# #) { + push @param, "$name '$value'"; + } else { + push @param, "$name='$value'"; + } + } + return join(" AND ", @param); +} + +sub throw_error { + my ($msg) = @_; + print "Content-type: text/plain\n\n$msg\n"; + print STDERR $msg . "\n"; + exit; +} + |