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