<?php
/**
 * Database connection library
 *
 * @author		Roland Haeder <webmaster@shipsimu.org>
 * @version		3.0.0
 * @copyright	Copyright (c) 2009 - 2011 Cracker Tracker Team
 * @license		GNU GPL 3.0 or any newer version
 * @link		http://www.shipsimu.org
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program. If not, see <http://www.gnu.org/licenses/>.
 */

// Function to aquire a database link
function aquireCrackerTrackerDatabaseLink () {
	// Is the link up?
	if ((!isCrackerTrackerDatabaseLinkUp()) && (!empty($GLOBALS['ctracker_host'])) && (!empty($GLOBALS['ctracker_dbname'])) && (!empty($GLOBALS['ctracker_user']))) {
		// Then connect to the database
		$GLOBALS['ctracker_link'] = mysqli_connect($GLOBALS['ctracker_host'], $GLOBALS['ctracker_user'], $GLOBALS['ctracker_password'], $GLOBALS['ctracker_dbname']) or crackerTrackerDatabaseError(__FUNCTION__, __LINE__);

		//  Check on connection and config table
		if (!isCrackerTrackerDatabaseLinkUp()) {
			// Connect didn't work
			crackerTrackerDatabaseError(__FUNCTION__, __LINE__);
		} elseif (isCrackerTrackerTableCreated('ctracker_config')) {
			// Load the config
			crackerTrackerLoadConfig();
		} // END - if
	} else {
		// Init fake config
		crackerTrackerInitFakeConfig();
	}
}

// Inits a fake configurtation
function crackerTrackerInitFakeConfig () {
	// Set the array
	$GLOBALS['ctracker_config'] = array(
		'ctracker_alert_user' => 'Y',
	);
}

// Checks if the link is up
function isCrackerTrackerDatabaseLinkUp () {
	// Is the instance at least set?
	if (isset($GLOBALS['ctracker_link'])) {
		// Debug message
		//* DEBUG: */ error_log('isset='.intval(isset($GLOBALS['ctracker_link'])) . ',is_object=' . intval(is_object($GLOBALS['ctracker_link'])) . ',mysqli_connect_errno=' . mysqli_connect_errno());
	} else {
		// Not set!
		//* DEBUG: */ error_log('ctracker_link not set.');
	}

	return ((isset($GLOBALS['ctracker_link'])) && (is_object($GLOBALS['ctracker_link'])) && (mysqli_connect_errno() == 0));
}

// Database error detected
function crackerTrackerDatabaseError ($F, $L) {
	// Should we debug?
	if (isCrackerTrackerDebug()) {
		// Output error
		print 'Function    : ' . $F . '<br />';
		print 'Line        : ' . $L . '<br />';
		if (isset($GLOBALS['ctracker_link'])) {
			print 'MySQL error : ' . mysqli_error($GLOBALS['ctracker_link']) . '<br />';
		} else {
			print 'No MySQLi available.<br />';
		}
		print 'Last SQL    : '. $GLOBALS['ctracker_last_sql'] . '<br />';
	} // END - if

	// Currently only die here
	crackerTrackerDie();
}

// Closes a maybe open database link
function crackerTrackerCloseDatabaseLink () {
	// Is the link up?
	if (isCrackerTrackerDatabaseLinkUp()) {
		// Did it work?
		if (!mysqli_close($GLOBALS['ctracker_link'])) {
			// Attempt has failed
			crackerTrackerDatabaseError(__FUNCTION__, __LINE__);
		} // END - if
	} // END - if
}

// Inserts given array, if IP/check_get combination was not found
function crackerTrackerInsertArray ($table, $rowData) {
	// Is there a link up?
	if (!isCrackerTrackerDatabaseLinkUp()) {
		// Abort silently here
		return FALSE;
	} // END - if

	// Is it found?
	if (!isCrackerTrackerEntryFound($rowData)) {
		// Prepare SQL
		$sqlString = 'INSERT INTO `' . $table . '` (`' . implode('`,`', array_keys($rowData)) . '`) VALUES(' . implode_secure($rowData) . ')';

		// Reset insert id
		$GLOBALS['ctracker_last_insert_id'] = FALSE;

		// Run it
		runCrackerTrackerSql($sqlString, __FUNCTION__, __LINE__);

		// Remember the last insert id
		$GLOBALS['ctracker_last_insert_id'] = mysqli_insert_id($GLOBALS['ctracker_link']) or crackerTrackerDatabaseError(__FUNCTION__, __LINE__);
	} else {
		// Only update the entry
		updateCrackerTrackerEntry($rowData);
	}
}

// Updates a given entry by just counting it up
function updateCrackerTrackerEntry (array $rowData) {
	// Construct the SELECT query
	$sqlString = 'UPDATE `ctracker_data` SET `count`=`count`+1 WHERE (`remote_addr`="' . crackerTrackerEscapeString($rowData['remote_addr']) . '" OR `proxy_addr`="' . crackerTrackerEscapeString($rowData['proxy_addr']) . '") AND `` = "' . crackerTrackerEscapeString($rowData['']) . '" LIMIT 1';

	// Run the SQL and check if we have one line
	runCrackerTrackerSql($sqlString, __FUNCTION__, __LINE__);
}

// Checks if an entry with IP/check_get/domain combination is there
function isCrackerTrackerEntryFound (array $rowData) {
	// Construct the SELECT query
	$sqlString = 'SELECT `id` FROM `ctracker_data` WHERE (`remote_addr`="' . crackerTrackerEscapeString($rowData['remote_addr']) . '" OR `proxy_addr`="' . crackerTrackerEscapeString($rowData['proxy_addr']) . '") AND `check_get` = "' . crackerTrackerEscapeString($rowData['check_get']) . '" AND `server_name`="' . crackerTrackerEscapeString($rowData['server_name']) . '" LIMIT 1';

	// Run the SQL and check if we have one line
	return ((isCrackerTrackerDatabaseLinkUp()) && (mysqli_num_rows(runCrackerTrackerSql($sqlString, __FUNCTION__, __LINE__)) == 1));
}

// Escapes the string
function crackerTrackerEscapeString ($string) {
	// Is the link up?
	if (!isCrackerTrackerDatabaseLinkUp()) {
		// Then we cant use mysqli_real_escape_string!
		$string = addslashes($string);
	} elseif (function_exists('mysqli_real_escape_string')) {
		// Use mysqli_real_escape_string()
		$string = mysqli_real_escape_string($GLOBALS['ctracker_link'], $string);
	} else {
		// Use fall-back (bad!)
		$string = addslashes($string);
	}

	// Return the secured string
	return $string;
} // END - if

// Runs an SQL query and checks for errors
function runCrackerTrackerSql ($sqlString, $function, $line) {
	// Is the link up?
	if (!isCrackerTrackerDatabaseLinkUp()) {
		// Abort here
		crackerTrackerDie();
	} // END - if

	// Remember last SQL
	$GLOBALS['ctracker_last_sql'] = $sqlString;

	// Run the query
	$GLOBALS['ctracker_last_result'] = mysqli_query($GLOBALS['ctracker_link'], $sqlString) or crackerTrackerDatabaseError($function, $line);

	// And return it
	return $GLOBALS['ctracker_last_result'];
}

// Checks wether a table was found
function isCrackerTrackerTableCreated ($table) {
	// Default is not found
	$found = FALSE;

	// Run the query
	$result = runCrackerTrackerSql('SHOW TABLES', __FUNCTION__, __LINE__);

	// Is our table there?
	while (list($tab) = mysqli_fetch_row($result)) {
		// Debug message
		//* NOISY-DEBUG: */ error_log('tab=' . $tab);

		// Is the table there?
		if ($tab == $table) {
			// Okay, found. So abort
			$found = TRUE;
			break;
		} // END - if
	} // END - if

	// Free result
	freeCrackerTrackerResult($result);

	// Return result
	return $found;
}

// Creates the given table with columns
function crackerTrackerCreateTable ($table, array $columns, array $keys) {
	// Begin the SQL
	$sqlString = 'CREATE TABLE IF NOT EXISTS `' . $table . '` (';

	// Add table name as first column
	$sqlString .= '`' . $table . '` BIGINT ( 20 ) UNSIGNED NOT NULL AUTO_INCREMENT, ';

	// Add all columns
	foreach ($columns as $column=>$type) {
		// Add this entry
		$sqlString .= '`' . $column . '` ' . $type . ', ';
	} // END - foreach

	// Add table name as primary key
	$sqlString .= 'PRIMARY KEY (`' . $table . '`), ';

	// Add keys
	foreach ($keys as $key=>$type) {
		// Add this entry
		$sqlString .= '' . $type . ' (`' . $key . '`), ';
	} // END - foreach

	// Finish SQL
	$sqlString = substr($sqlString, 0, -2) . ') TYPE=InnoDB';

	// And run it
	runCrackerTrackerSql($sqlString);
}

// Inits a table by inserting 
function crackerTrackerInitTable ($table) {
	// Prepare SQL and run it
	runCrackerTrackerSql('INSERT INTO `' . $table . '` (`' . $table . '`) VALUES (NULL)');
}

// Updates the database scheme automatically
function crackerTrackerUpdateDatabaseScheme () {
	// Is a link there?
	if (!isCrackerTrackerDatabaseLinkUp()) {
		// Abort here silently
		return;
	} // END - if

	// Is the main config table there?
	if (!isCrackerTrackerTableCreated('ctracker_config')) {
		// Then do it for us
		crackerTrackerCreateTable('ctracker_config', array(
			'ctracker_db_version' => 'BIGINT ( 20 ) UNSIGNED NOT NULL DEFAULT 0',
			'ctracker_min_sleep'  => 'SMALLINT ( 5 ) UNSIGNED NOT NULL DEFAULT 10',
			'ctracker_max_sleep'  => 'SMALLINT ( 5 ) UNSIGNED NOT NULL DEFAULT 30',
			'ctracker_alert_user' => "ENUM('Y','N') NOT NULL DEFAULT 'Y'",
			'ctracker_language'   => "CHAR ( 2) NOT NULL DEFAULT 'en'"
		), array());

		// Init that table
		crackerTrackerInitTable('ctracker_config');
	} // END - if

	// Init update array here
	crackerTrackerInitUpdates();

	// Run any SQL updates recursively
	while (isset($GLOBALS['ctracker_updates'][getCrackerTrackerConfig('ctracker_db_version')])) {
		// Run that updates
		runCrackerTrackerUpdates(getCrackerTrackerConfig('ctracker_db_version'));

		// Update config
		runCrackerTrackerSql('UPDATE `ctracker_config` SET `ctracker_db_version`=`ctracker_db_version`+1 WHERE `ctracker_config`=1 LIMIT 1', __FUNCTION__, __LINE__);

		// And count it up in the config array
		$GLOBALS['ctracker_config']['ctracker_db_version']++;
	} // END - if
}

// Load the configuration
function crackerTrackerLoadConfig () {
	// Construct SQL command and run it
	$result = runCrackerTrackerSql('SELECT * FROM `ctracker_config` WHERE `ctracker_config`=1 LIMIT 1', __FUNCTION__, __LINE__);

	// And get it
	$GLOBALS['ctracker_config'] = mysqli_fetch_array($result);

	// Free result
	freeCrackerTrackerResult($result);
}

// Getter for config
function getCrackerTrackerConfig ($entry) {
	// Is the config entry there?
	if (!isset($GLOBALS['ctracker_config'][$entry])) {
		// Then better die here, else we may have an endless loop
		if (isCrackerTrackerDebug()) {
			// Nicer message in debug mode
			die('Configuration entry ' . $entry . ' missing!');
		} else {
			// die() on production systems
			die();
		}
	} // END - if

	// Return it
	return $GLOBALS['ctracker_config'][$entry];
}

// Did the current IP already generated blocked attempts?
function isCrackerTrackerIpSuspicious () {
	// Skip this silently if we have not config
	if (!isCrackerTrackerDatabaseLinkUp()) {
		// Skip this step silently, all is not suspicious
		return FALSE;
	} // END - if

	// Check if an entry is there
	$result = runCrackerTrackerSql("SELECT COUNT(`id`) AS `cnt` FROM `ctracker_data` USE INDEX (`remote_proxy_last`) WHERE `remote_addr`='" . determineCrackerTrackerRealRemoteAddress() . "' OR `proxy_addr`='" . getenv('REMOTE_ADDR') . "' LIMIT 1", __FUNCTION__, __LINE__);

	// Get row count
	list($rows) = mysqli_fetch_row($result);

	// Is there one entry?
	$found = ($rows > 0);

	// And again?
	if ($found === TRUE) {
		// Yes, one is found, then load it
		$result = runCrackerTrackerSql("SELECT SQL_SMALL_RESULT * FROM `ctracker_data` USE INDEX (`remote_proxy_last`) WHERE `remote_addr`='" . determineCrackerTrackerRealRemoteAddress() . "' OR `proxy_addr`='" . getenv('REMOTE_ADDR') . "' ORDER BY `last_attempt` DESC LIMIT 1", __FUNCTION__, __LINE__);

		// Cache the entry
		$GLOBALS['ctracker_last_suspicious_entry'] = mysqli_fetch_array($result);
	} // END - if

	// Free result
	freeCrackerTrackerResult($result);

	// Return the result
	return $found;
}

// Does the current IP have a ticket?
function ifCrackerTrackerIpHasTicket () {
	// We only give one ticket per IP!
	$result = runCrackerTrackerSql("SELECT * FROM `ctracker_ticket` WHERE `ctracker_ticket_remote_addr`='" . determineCrackerTrackerRealRemoteAddress() . "' OR `ctracker_ticket_proxy_addr`='" . getenv('REMOTE_ADDR') . "' LIMIT 1", __FUNCTION__, __LINE__);

	// Do we have a ticket?
	$found = (mysqli_num_rows($result) == 1);

	// And again?
	if ($found === TRUE) {
		// Cache the ticket data
		$GLOBALS['ctracker_last_ticket'] = mysqli_fetch_array($result);
	} // END - if

	// Free result
	freeCrackerTrackerResult($result);

	// Return the result
	return $found;
}

// Adds a ticket based on given (mostly $_POST) data
function addCrackerTrackerTicket (array $data) {
	// Prepare the array
	$GLOBALS['ctracker_last_ticket'] = array(
		'ctracker_ticket_remote_addr' => determineCrackerTrackerRealRemoteAddress(),
		'ctracker_ticket_proxy_addr'  => getenv('REMOTE_ADDR'),
		'ctracker_ticket_user_agent'  => crackerTrackerUserAgent(),
		'ctracker_ticket_name'        => crackerTrackerSecureString($data['name']),
		'ctracker_ticket_email'       => crackerTrackerSecureString($data['email']),
		'ctracker_ticket_comment'     => crackerTrackerSecureString($data['comment'])
	);

	// Insert it
	crackerTrackerInsertArray('ctracker_ticket', $GLOBALS['ctracker_last_ticket']);

	// Is there an entry?
	if ((isset($GLOBALS['ctracker_last_insert_id'])) && ($GLOBALS['ctracker_last_insert_id'] > 0)) {
		// All fine, so prepare the link between ticket<->data
		$data = array(
			'ctracker_ticket_id' => $GLOBALS['ctracker_last_insert_id'],
			'ctracker_data_id'   => $GLOBALS['ctracker_last_suspicious_entry']['id']
		);

		// And insert it as well
		crackerTrackerInsertArray('ctracker_ticket_data', $data);

		// Add ticket id again
		$GLOBALS['ctracker_ticket'] = $data['ctracker_ticket_id'];

		// Merge all data for emails
		$GLOBALS['ctracker_last_ticket'] = array_merge($GLOBALS['ctracker_last_ticket'], $data);

		// Is this also there?
		if ((isset($GLOBALS['ctracker_last_insert_id'])) && ($GLOBALS['ctracker_last_insert_id'] > 0)) {
			// All fine, so display "thank you page"
			crackerTrackerLoadTemplate('add_ticket_thanks');
		} else {
			// Did not insert
			crackerTrackerDie();
		}
	} else {
		// Did not insert
		crackerTrackerDie();
	}
}

// Frees given result instance
function freeCrackerTrackerResult (mysqli_result $result) {
	// Free result
	$result->free();
}