3 * Database connection library
5 * @author Roland Haeder <webmaster@ship-simu.org>
7 * @copyright Copyright (c) 2009 - 2011 Cracker Tracker Team
8 * @license GNU GPL 3.0 or any newer version
9 * @link http://www.ship-simu.org
11 * This program is free software: you can redistribute it and/or modify
12 * it under the terms of the GNU General Public License as published by
13 * the Free Software Foundation, either version 3 of the License, or
14 * (at your option) any later version.
16 * This program is distributed in the hope that it will be useful,
17 * but WITHOUT ANY WARRANTY; without even the implied warranty of
18 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.See the
19 * GNU General Public License for more details.
21 * You should have received a copy of the GNU General Public License
22 * along with this program. If not, see <http://www.gnu.org/licenses/>.
25 // Function to aquire a database link
26 function aquireCrackerTrackerDatabaseLink () {
28 if ((!isCrackerTrackerDatabaseLinkUp()) && (!empty($GLOBALS['ctracker_host'])) && (!empty($GLOBALS['ctracker_dbname'])) && (!empty($GLOBALS['ctracker_user']))) {
29 // Then connect to the database
30 $GLOBALS['ctracker_link'] = mysql_connect($GLOBALS['ctracker_host'], $GLOBALS['ctracker_user'], $GLOBALS['ctracker_password']) or crackerTrackerDatabaseError(__FUNCTION__, __LINE__);
32 // Select the database
33 if (!mysql_select_db($GLOBALS['ctracker_dbname'], $GLOBALS['ctracker_link'])) {
35 crackerTrackerDatabaseError(__FUNCTION__, __LINE__);
36 } elseif (isCrackerTrackerTableCreated('ctracker_config')) {
38 crackerTrackerLoadConfig();
42 crackerTrackerInitFakeConfig();
46 // Inits a fake configurtation
47 function crackerTrackerInitFakeConfig () {
49 $GLOBALS['ctracker_config'] = array(
50 'ctracker_alert_user' => 'Y',
54 // Checks if the link is up
55 function isCrackerTrackerDatabaseLinkUp () {
56 return ((isset($GLOBALS['ctracker_link'])) && (is_resource($GLOBALS['ctracker_link'])));
59 // Database error detected
60 function crackerTrackerDatabaseError ($F, $L) {
62 if (isCrackerTrackerDebug()) {
64 print 'Function : ' . $F . '<br />';
65 print 'Line : ' . $L . '<br />';
66 print 'MySQL error : ' . mysql_error() . '<br />';
67 print 'Last SQL : '. $GLOBALS['ctracker_last_sql'] . '<br />';
70 // Currently only die here
74 // Closes a maybe open database link
75 function crackerTrackerCloseDatabaseLink () {
77 if (isCrackerTrackerDatabaseLinkUp()) {
79 if (!mysql_close($GLOBALS['ctracker_link'])) {
80 // Remove the link from global array
81 unset($GLOBALS['ctracker_link']);
84 crackerTrackerDatabaseError(__FUNCTION__, __LINE__);
88 // Remove the link from global array
89 unset($GLOBALS['ctracker_link']);
92 // Inserts given array, if IP/check_worm combination was not found
93 function crackerTrackerInsertArray ($table, $rowData) {
94 // Is there a link up?
95 if (!isCrackerTrackerDatabaseLinkUp()) {
96 // Abort silently here
101 if (!isCrackerTrackerEntryFound($rowData)) {
103 $SQL = 'INSERT INTO `' . $table . '` (`' . implode('`,`', array_keys($rowData)) . '`) VALUES(' . implode_secure($rowData) . ')';
106 $GLOBALS['ctracker_last_insert_id'] = false;
109 runCrackerTrackerSql($SQL, __FUNCTION__, __LINE__);
111 // Remember the last insert id
112 $GLOBALS['ctracker_last_insert_id'] = mysql_insert_id($GLOBALS['ctracker_link']) or crackerTrackerDatabaseError(__FUNCTION__, __LINE__);
114 // Only update the entry
115 updateCrackerTrackerEntry($rowData);
119 // Updates a given entry by just counting it up
120 function updateCrackerTrackerEntry ($rowData) {
121 // Construct the SELECT query
122 $SQL = 'UPDATE `ctracker_data` SET `count`=`count`+1 WHERE `remote_addr`="' . crackerTrackerEscapeString($rowData['remote_addr']) . '" AND `check_worm` = "' . crackerTrackerEscapeString($rowData['check_worm']) . '" LIMIT 1';
124 // Run the SQL and check if we have one line
125 runCrackerTrackerSql($SQL, __FUNCTION__, __LINE__);
128 // Checks if an entry with IP/check_worm/domain combination is there
129 function isCrackerTrackerEntryFound ($rowData) {
130 // Construct the SELECT query
131 $SQL = 'SELECT `id` FROM `ctracker_data` WHERE `remote_addr`="' . crackerTrackerEscapeString($rowData['remote_addr']) . '" AND `check_worm` = "' . crackerTrackerEscapeString($rowData['check_worm']) . '" AND `server_name`="' . crackerTrackerEscapeString($rowData['server_name']) . '" LIMIT 1';
133 // Run the SQL and check if we have one line
134 return ((isCrackerTrackerDatabaseLinkUp()) && (mysql_num_rows(runCrackerTrackerSql($SQL, __FUNCTION__, __LINE__)) == 1));
137 // Escapes the string
138 function crackerTrackerEscapeString ($string) {
140 if (!isCrackerTrackerDatabaseLinkUp()) {
141 // Then we cant use mysql_real_escape_string!
142 $string = addslashes($string);
143 } elseif (function_exists('mysql_real_escape_string')) {
144 // Use mysql_real_escape_string()
145 $string = mysql_real_escape_string($string, $GLOBALS['ctracker_link']);
146 } elseif (function_exists('mysql_escape_string')) {
147 // Use deprecated function
148 $string = mysql_escape_string($string, $GLOBALS['ctracker_link']);
150 // Use fall-back (bad!)
151 $string = addslashes($string);
154 // Return the secured string
158 // Runs an SQL query and checks for errors
159 function runCrackerTrackerSql ($SQL, $F, $L) {
161 if (!isCrackerTrackerDatabaseLinkUp()) {
167 $GLOBALS['ctracker_last_sql'] = $SQL;
170 $GLOBALS['ctracker_last_result'] = mysql_query($SQL, $GLOBALS['ctracker_link']) or crackerTrackerDatabaseError(__FUNCTION__, __LINE__);
173 return $GLOBALS['ctracker_last_result'];
176 // Checks wether a table was found
177 function isCrackerTrackerTableCreated ($table) {
178 // Default is not found
182 $result = runCrackerTrackerSql('SHOW TABLES', __FUNCTION__, __LINE__);
184 // Is our table there?
185 while (list($tab) = mysql_fetch_row($result)) {
186 // Is the table there?
187 if ($tab == $table) {
188 // Okay, found. So abort
195 mysql_free_result($result) or crackerTrackerDatabaseError(__FUNCTION__, __LINE__);
201 // Creates the given table with columns
202 function crackerTrackerCreateTable ($table, array $columns, array $keys) {
204 $SQL = 'CREATE TABLE IF NOT EXISTS `' . $table . '` (';
206 // Add table name as first column
207 $SQL .= '`' . $table . '` BIGINT ( 20 ) UNSIGNED NOT NULL AUTO_INCREMENT, ';
210 foreach ($columns as $column=>$type) {
212 $SQL .= '`' . $column . '` ' . $type . ', ';
215 // Add table name as primary key
216 $SQL .= 'PRIMARY KEY (`' . $table . '`), ';
219 foreach ($keys as $key=>$type) {
221 $SQL .= '' . $type . ' (`' . $key . '`), ';
225 $SQL = substr($SQL, 0, -2) . ') TYPE=InnoDB';
228 runCrackerTrackerSql($SQL);
231 // Inits a table by inserting
232 function crackerTrackerInitTable ($table) {
233 // Prepare SQL and run it
234 runCrackerTrackerSql('INSERT INTO `' . $table . '` (`' . $table . '`) VALUES (NULL)');
237 // Updates the database scheme automatically
238 function crackerTrackerUpdateDatabaseScheme () {
240 if (!isCrackerTrackerDatabaseLinkUp()) {
241 // Abort here silently
245 // Is the main config table there?
246 if (!isCrackerTrackerTableCreated('ctracker_config')) {
248 crackerTrackerCreateTable('ctracker_config', array(
249 'ctracker_db_version' => 'BIGINT ( 20 ) UNSIGNED NOT NULL DEFAULT 0',
250 'ctracker_min_sleep' => 'SMALLINT ( 5 ) UNSIGNED NOT NULL DEFAULT 10',
251 'ctracker_max_sleep' => 'SMALLINT ( 5 ) UNSIGNED NOT NULL DEFAULT 30',
252 'ctracker_alert_user' => "ENUM('Y','N') NOT NULL DEFAULT 'Y'",
253 'ctracker_language' => "CHAR ( 2) NOT NULL DEFAULT 'en'"
257 crackerTrackerInitTable('ctracker_config');
260 // Init update array here
261 crackerTrackerInitUpdates();
263 // Run any SQL updates recursively
264 while (isset($GLOBALS['ctracker_updates'][getCrackerTrackerConfig('ctracker_db_version')])) {
266 runCrackerTrackerUpdates(getCrackerTrackerConfig('ctracker_db_version'));
269 runCrackerTrackerSql('UPDATE `ctracker_config` SET `ctracker_db_version`=`ctracker_db_version`+1 WHERE `ctracker_config`=1 LIMIT 1', __FUNCTION__, __LINE__);
271 // And count it up in the config array
272 $GLOBALS['ctracker_config']['ctracker_db_version']++;
276 // Load the configuration
277 function crackerTrackerLoadConfig () {
278 // Construct SQL command and run it
279 $result = runCrackerTrackerSql('SELECT * FROM `ctracker_config` WHERE `ctracker_config`=1 LIMIT 1', __FUNCTION__, __LINE__);
282 $GLOBALS['ctracker_config'] = mysql_fetch_array($result);
285 mysql_free_result($result) or crackerTrackerDatabaseError(__FUNCTION__, __LINE__);
289 function getCrackerTrackerConfig ($entry) {
290 // Is the config entry there?
291 if (!isset($GLOBALS['ctracker_config'][$entry])) {
292 // Then better die here, else we may have an endless loop
293 if (isCrackerTrackerDebug()) {
294 // Nicer message in debug mode
295 die('Configuration entry ' . $entry . ' missing!');
297 // die() on production systems
303 return $GLOBALS['ctracker_config'][$entry];
306 // Did the current IP already generated blocked attempts?
307 function isCrackerTrackerIpSuspicious () {
308 // Skip this silently if we have not config
309 if (!isCrackerTrackerDatabaseLinkUp()) {
310 // Skip this step silently, all is not suspicious
314 // We only need the very last attempt to get!
315 $result = runCrackerTrackerSql("SELECT * FROM `ctracker_data` WHERE `remote_addr`='" . determineCrackerTrackerRealRemoteAddress() . "' ORDER BY `last_attempt` DESC LIMIT 1", __FUNCTION__, __LINE__);
317 // Do we have entries?
318 $found = (mysql_num_rows($result) == 1);
321 if ($found === true) {
323 $GLOBALS['ctracker_last_suspicious_entry'] = mysql_fetch_array($result);
327 mysql_free_result($result) or crackerTrackerDatabaseError(__FUNCTION__, __LINE__);
333 // Does the current IP have a ticket?
334 function ifCrackerTrackerIpHasTicket () {
335 // We only give one ticket per IP!
336 $result = runCrackerTrackerSql("SELECT * FROM `ctracker_ticket` WHERE `ctracker_ticket_remote_addr`='" . determineCrackerTrackerRealRemoteAddress() . "' LIMIT 1", __FUNCTION__, __LINE__);
338 // Do we have a ticket?
339 $found = (mysql_num_rows($result) == 1);
342 if ($found === true) {
343 // Cache the ticket data
344 $GLOBALS['ctracker_last_ticket'] = mysql_fetch_array($result);
348 mysql_free_result($result) or crackerTrackerDatabaseError(__FUNCTION__, __LINE__);
354 // Adds a ticket based on given (mostly $_POST) data
355 function addCrackerTrackerTicket (array $data) {
357 $GLOBALS['ctracker_last_ticket'] = array(
358 'ctracker_ticket_remote_addr' => determineCrackerTrackerRealRemoteAddress(),
359 'ctracker_ticket_user_agent' => crackerTrackerUserAgent(),
360 'ctracker_ticket_name' => crackerTrackerSecureString($data['name']),
361 'ctracker_ticket_email' => crackerTrackerSecureString($data['email']),
362 'ctracker_ticket_comment' => crackerTrackerSecureString($data['comment'])
366 crackerTrackerInsertArray('ctracker_ticket', $GLOBALS['ctracker_last_ticket']);
368 // Is there an entry?
369 if ((isset($GLOBALS['ctracker_last_insert_id'])) && ($GLOBALS['ctracker_last_insert_id'] > 0)) {
370 // All fine, so prepare the link between ticket<->data
372 'ctracker_ticket_id' => $GLOBALS['ctracker_last_insert_id'],
373 'ctracker_data_id' => $GLOBALS['ctracker_last_suspicious_entry']['id']
376 // And insert it as well
377 crackerTrackerInsertArray('ctracker_ticket_data', $data);
379 // Add ticket id again
380 $GLOBALS['ctracker_ticket'] = $data['ctracker_ticket_id'];
382 // Merge all data for emails
383 $GLOBALS['ctracker_last_ticket'] = array_merge($GLOBALS['ctracker_last_ticket'], $data);
385 // Is this also there?
386 if ((isset($GLOBALS['ctracker_last_insert_id'])) && ($GLOBALS['ctracker_last_insert_id'] > 0)) {
387 // All fine, so display "thank you page"
388 crackerTrackerLoadTemplate('add_ticket_thanks');