1###############################################################################
2## Copyright 2005-2016 OCSInventory-NG/OCSInventory-Server contributors.
3## See the Contributors file for more details about them.
4##
5## This file is part of OCSInventory-NG/OCSInventory-ocsreports.
6##
7## OCSInventory-NG/OCSInventory-Server is free software: you can redistribute
8## it and/or modify it under the terms of the GNU General Public License as
9## published by the Free Software Foundation, either version 2 of the License,
10## or (at your option) any later version.
11##
12## OCSInventory-NG/OCSInventory-Server is distributed in the hope that it
13## will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty
14## of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
15## GNU General Public License for more details.
16##
17## You should have received a copy of the GNU General Public License
18## along with OCSInventory-NG/OCSInventory-ocsreports. if not, write to the
19## Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,
20## MA 02110-1301, USA.
21################################################################################
22package Apache::Ocsinventory::Interface::Database;
23
24use strict;
25
26require Exporter;
27
28our @ISA = qw /Exporter/;
29
30our @EXPORT = qw /
31  database_connect
32  get_sth
33  get_dbh_write
34  get_dbh_read
35  do_sql
36  get_hardware_table_pk
37  get_snmp_table_pk
38  get_type_name
39  untaint_dbstring
40  untaint_dbstring_lst
41/;
42
43# Database connection
44sub database_connect{
45  my $dbHost;
46  my $dbName;
47  my $dbPort;
48  my $dbUser;
49  my $dbPwd;
50  my %params;
51
52  my $mode = shift;
53
54  my $env_mode_prefix = 'OCS_DB';
55
56  if( $mode eq 'read' && $ENV{'OCS_DB_SL_HOST'} ){
57    $dbHost = $ENV{'OCS_DB_SL_HOST'};
58    $dbName = $ENV{'OCS_DB_SL_NAME'}||'ocsweb';
59    $dbPort = $ENV{'OCS_DB_SL_PORT'}||'3306';
60    $dbUser = $ENV{'OCS_DB_SL_USER'};
61    $dbPwd  = $Apache::Ocsinventory::SOAP::apache_req->dir_config('OCS_DB_SL_PWD');
62    $env_mode_prefix .= '_SL';
63  }
64  else{
65    $dbHost = $ENV{'OCS_DB_HOST'};
66    $dbName = $ENV{'OCS_DB_NAME'}||'ocsweb';
67    $dbPort = $ENV{'OCS_DB_PORT'}||'3306';
68    $dbUser = $ENV{'OCS_DB_USER'};
69    $dbPwd  = $Apache::Ocsinventory::SOAP::apache_req->dir_config('OCS_DB_PWD');
70  }
71
72  # Optionnaly a mysql socket different than the client's built in
73  $params{'mysql_socket'} = $ENV{'OCS_OPT_DBI_MYSQL_SOCKET'} if $ENV{'OCS_OPT_DBI_MYSQL_SOCKET'};
74
75  my $mysql_ssl_mode = '';
76  if( defined($ENV{$env_mode_prefix.'_SSL_ENABLED'}) and $ENV{$env_mode_prefix.'_SSL_ENABLED'} == 1 )
77  {
78    if( defined($ENV{$env_mode_prefix.'_SSL_MODE'}) and $ENV{$env_mode_prefix.'_SSL_MODE'} eq 'SSL_MODE_PREFERRED' )
79    {
80        $mysql_ssl_mode = ';mysql_ssl=1;mysql_ssl_optional=1';
81    }
82    elsif( defined($ENV{$env_mode_prefix.'_SSL_MODE'}) and $ENV{$env_mode_prefix.'_SSL_MODE'} eq 'SSL_MODE_REQUIRED' )
83    {
84        $mysql_ssl_mode = ';mysql_ssl=1;mysql_ssl_verify_server_cert=0';
85    }
86    elsif( defined($ENV{$env_mode_prefix.'_SSL_MODE'}) and $ENV{$env_mode_prefix.'_SSL_MODE'} eq 'SSL_MODE_STRICT' )
87    {
88        $mysql_ssl_mode = ';mysql_ssl=1;mysql_ssl_verify_server_cert=1';
89    }
90    else
91    {
92        # SSL Is enabled but mode hasn't been provided. Let's put PREFERRED mode by default
93        $mysql_ssl_mode = ';mysql_ssl=1;mysql_ssl_optional=1';
94    }
95
96    if( defined( $ENV{$env_mode_prefix.'_SSL_CLIENT_KEY'} ) and defined( $ENV{$env_mode_prefix.'_SSL_CLIENT_CERT'} ) and defined( $ENV{$env_mode_prefix.'_SSL_CA_CERT'} ) )
97    {
98        $mysql_ssl_mode .= ';mysql_ssl_client_key='.$ENV{$env_mode_prefix.'_SSL_CLIENT_KEY'}.';mysql_ssl_client_cert='.$ENV{$env_mode_prefix.'_SSL_CLIENT_CERT'}.';mysql_ssl_ca_file='.$ENV{$env_mode_prefix.'_SSL_CA_CERT'};
99    }
100  }
101
102  # Connection...
103  my $dbh = DBI->connect( "DBI:mysql:database=$dbName;host=$dbHost;port=$dbPort".$mysql_ssl_mode, $dbUser, $dbPwd, \%params);
104  unless($dbh) {
105    die DBI->errstr;
106  }
107  $dbh->do("SET NAMES 'utf8'") if($dbh && $ENV{'OCS_OPT_UNICODE_SUPPORT'});
108  $dbh->do("SET sql_mode='NO_ENGINE_SUBSTITUTION'");
109  return $dbh;
110}
111
112# Process the sql requests (prepare)
113sub get_sth {
114  my ($sql, @values) = @_;
115  my $dbh = database_connect( get_db_mode( $sql ) );
116  my $request = $dbh->prepare( $sql );
117  $request->execute( @values ) or die("==Bad request==\nSQL:$sql\nDATAS:".join "> <", @values, "\n");
118  return $request;
119}
120
121# Return dbi handles for particular use
122sub get_dbh_write {
123  return database_connect('write') ;
124}
125
126sub get_dbh_read {
127  return database_connect('read') ;
128}
129
130# Process the sql requests (do)
131sub do_sql {
132  my ($sql, @values) = @_;
133  my $dbh = database_connect( get_db_mode($sql) );
134  return $dbh->do( $sql, {}, @values );
135}
136
137# Return the id field of an inventory section
138sub get_hardware_table_pk{
139  my $section = shift;
140  return ($section eq 'hardware')?'ID':'HARDWARE_ID';
141}
142
143sub get_snmp_table_pk{
144  my $section = shift;
145  return ($section eq 'snmp')?'ID':'SNMP_ID';
146}
147
148sub get_type_name{
149  my ($section, $field, $value) = @_ ;
150
151  my $table_name = 'type_'.lc $section.'_'.lc $field ;
152  my $name ;
153
154  my $existsSql = "SELECT NAME FROM $table_name WHERE ID=?" ;
155  my $existsReq = get_sth($existsSql, $value) ;
156  my $row = $existsReq->fetchrow_hashref() ;
157  $name = $row->{NAME} ;
158  $existsReq->finish ;
159  return $name ;
160}
161
162sub untaint_dbstring{
163  my $string = shift;
164  $string =~ s/"/\\"/g;
165  $string =~ s/'/\\'/g;
166  return $string;
167}
168
169sub untaint_dbstring_lst{
170  my @list = @_;
171  my @quoted;
172  for (@list){
173    push @quoted, untaint_dbstring($_);
174  }
175  return @quoted;
176}
177
178sub get_db_mode {
179  my $sql = shift;
180  if( $sql =~ /select|show/i ){
181    return 'read';
182  }
183  else{
184    return 'write';
185  }
186}
187
1881;
189