3 * Database connection library
5 * @author Roland Haeder <webmaster@shipsimu.org>
7 * @copyright Copyright (c) 2009 - 2017 Cracker Tracker Team
8 * @license GNU GPL 3.0 or any newer version
9 * @link http://www.shipsimu.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'] = mysqli_connect($GLOBALS['ctracker_host'], $GLOBALS['ctracker_user'], $GLOBALS['ctracker_password'], $GLOBALS['ctracker_dbname']) or crackerTrackerDatabaseError(__FUNCTION__, __LINE__);
32 // Check on connection and config table
33 if (!isCrackerTrackerDatabaseLinkUp()) {
34 // Connect didn't work
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'] = [
50 'ctracker_alert_user' => 'Y',
54 // Checks if the link is up
55 function isCrackerTrackerDatabaseLinkUp () {
56 // Is the instance at least set?
57 if (isset($GLOBALS['ctracker_link'])) {
59 //* DEBUG: */ error_log('isset='.intval(isset($GLOBALS['ctracker_link'])) . ',is_object=' . intval(is_object($GLOBALS['ctracker_link'])) . ',mysqli_connect_errno=' . mysqli_connect_errno());
62 //* DEBUG: */ error_log('ctracker_link not set.');
65 return ((isset($GLOBALS['ctracker_link'])) && (is_object($GLOBALS['ctracker_link'])) && (mysqli_connect_errno() == 0));
68 // Database error detected
69 function crackerTrackerDatabaseError (string $file, int $line) {
71 if (isCrackerTrackerDebug()) {
73 print 'Function : ' . $file . '<br />' . PHP_EOL;
74 print 'Line : ' . $line . '<br />' . PHP_EOL;
75 if (isset($GLOBALS['ctracker_link']) && $GLOBALS['ctracker_link'] !== false) {
76 print 'MySQL error : ' . mysqli_error($GLOBALS['ctracker_link']) . '<br />' . PHP_EOL;
78 print 'No MySQLi available.<br />' . PHP_EOL;
80 if (isset($GLOBALS['ctracker_last_sql'])) {
81 print 'Last SQL : '. $GLOBALS['ctracker_last_sql'] . '<br />' . PHP_EOL;
83 print 'No last SQL command available.<br />' . PHP_EOL;
87 // Currently only die here
91 // Closes a maybe open database link
92 function crackerTrackerCloseDatabaseLink () {
93 // The link should be up here
94 if (!isCrackerTrackerDatabaseLinkUp()) {
96 throw new BadFunctionCallException('Link is not up.');
100 if (!mysqli_close($GLOBALS['ctracker_link'])) {
101 // Attempt has failed
102 crackerTrackerDatabaseError(__FUNCTION__, __LINE__);
106 // Inserts given array, if IP/check_get combination was not found
107 function crackerTrackerInsertArray (string $table, array $rowData) {
109 if (!isCrackerTrackerEntryFound($rowData)) {
111 $GLOBALS['ctracker_last_insert_id'] = false;
114 runCrackerTrackerSql(sprintf("INSERT INTO `%s` (`%s`) VALUES(%s)",
116 implode('`,`', array_keys($rowData)),
117 implode_secure($rowData)
118 ), __FUNCTION__, __LINE__);
120 // Remember the last insert id
121 $GLOBALS['ctracker_last_insert_id'] = mysqli_insert_id($GLOBALS['ctracker_link']) or crackerTrackerDatabaseError(__FUNCTION__, __LINE__);
123 // Only update the entry
124 updateCrackerTrackerEntry($rowData);
128 // Updates a given entry by just counting it up
129 function updateCrackerTrackerEntry (array $rowData, string $countColumn = 'count') {
130 // The link should be up here
131 if (!isCrackerTrackerDatabaseLinkUp()) {
133 throw new BadFunctionCallException('Link is not up.');
136 // Run the SQL and check if we have one line
137 runCrackerTrackerSql(sprintf("UPDATE `ctracker_data` SET `%s`=`%s`+1 WHERE (`remote_addr`='%s' AND `proxy_addr`='%s') LIMIT 1",
140 crackerTrackerEscapeString($rowData['remote_addr']),
141 crackerTrackerEscapeString($rowData['proxy_addr'])
142 ), __FUNCTION__, __LINE__);
145 // Checks if an entry with IP/check_get/domain combination is there
146 function isCrackerTrackerEntryFound (array $rowData) {
147 // The link should be up here
148 if (!isCrackerTrackerDatabaseLinkUp()) {
150 throw new BadFunctionCallException('Link is not up.');
153 // Run the SQL and check if we have one line
154 $result = runCrackerTrackerSql(sprintf("SELECT `id` FROM `ctracker_data` WHERE (`remote_addr`='%s' OR `proxy_addr`='%s') AND `check_get` = '%s' AND `server_name`='%s' LIMIT 1'",
155 crackerTrackerEscapeString($rowData['remote_addr']),
156 crackerTrackerEscapeString($rowData['proxy_addr']),
157 crackerTrackerEscapeString($rowData['check_get']),
158 crackerTrackerEscapeString($rowData['server_name'])
159 ), __FUNCTION__, __LINE__);
161 // Check count of rows
162 return (mysqli_num_rows($result) == 1);
165 // Escapes the string
166 function crackerTrackerEscapeString (string $string) {
168 if (!isCrackerTrackerDatabaseLinkUp()) {
169 // Then we cant use mysqli_real_escape_string!
170 $string = htmlentities($string, ENT_QUOTES);
171 } elseif (function_exists('mysqli_real_escape_string')) {
172 // Use mysqli_real_escape_string()
173 $string = mysqli_real_escape_string($GLOBALS['ctracker_link'], $string);
175 // Use fall-back (bad!)
176 $string = htmlentities($string, ENT_QUOTES);
179 // Return the secured string
183 // Runs an SQL query and checks for errors
184 function runCrackerTrackerSql (string $sqlString, string $function, int $line) {
186 if (!isCrackerTrackerDatabaseLinkUp()) {
192 $GLOBALS['ctracker_last_sql'] = $sqlString;
195 $GLOBALS['ctracker_last_result'] = mysqli_query($GLOBALS['ctracker_link'], $sqlString) or crackerTrackerDatabaseError($function, $line);
198 return $GLOBALS['ctracker_last_result'];
201 // Checks wether a table was found
202 function isCrackerTrackerTableCreated (string $table) {
203 // Default is not found
207 $result = runCrackerTrackerSql('SHOW TABLES', __FUNCTION__, __LINE__);
209 // Is our table there?
210 while (list($tab) = mysqli_fetch_row($result)) {
212 //* NOISY-DEBUG: */ error_log('tab=' . $tab);
214 // Is the table there?
215 if ($tab == $table) {
216 // Okay, found. So abort
223 freeCrackerTrackerResult($result);
229 // Creates the given table with columns
230 function crackerTrackerCreateTable (string $table, array $columns, array $keys) {
232 $sqlString = 'CREATE TABLE IF NOT EXISTS `' . $table . '` (';
234 // Add table name as first column
235 $sqlString .= '`' . $table . '` BIGINT ( 20 ) UNSIGNED NOT NULL AUTO_INCREMENT, ';
238 foreach ($columns as $column=>$type) {
240 $sqlString .= '`' . $column . '` ' . $type . ', ';
243 // Add table name as primary key
244 $sqlString .= 'PRIMARY KEY (`' . $table . '`), ';
247 foreach ($keys as $key => $type) {
249 $sqlString .= '' . $type . ' (`' . $key . '`), ';
253 $sqlString = substr($sqlString, 0, -2) . ') TYPE=InnoDB';
256 runCrackerTrackerSql($sqlString);
259 // Inits a table by inserting
260 function crackerTrackerInitTable (string $table) {
261 // Prepare SQL and run it
262 runCrackerTrackerSql('INSERT INTO `' . $table . '` (`' . $table . '`) VALUES (NULL)');
265 // Updates the database scheme automatically
266 function crackerTrackerUpdateDatabaseScheme () {
268 if (!isCrackerTrackerDatabaseLinkUp()) {
269 // Abort here silently
273 // Is the main config table there?
274 if (!isCrackerTrackerTableCreated('ctracker_config')) {
276 crackerTrackerCreateTable('ctracker_config', array(
277 'ctracker_db_version' => 'BIGINT ( 20 ) UNSIGNED NOT NULL DEFAULT 0',
278 'ctracker_min_sleep' => 'SMALLINT ( 5 ) UNSIGNED NOT NULL DEFAULT 10',
279 'ctracker_max_sleep' => 'SMALLINT ( 5 ) UNSIGNED NOT NULL DEFAULT 30',
280 'ctracker_alert_user' => "ENUM('Y','N') NOT NULL DEFAULT 'Y'",
281 'ctracker_language' => "CHAR ( 2) NOT NULL DEFAULT 'en'"
285 crackerTrackerInitTable('ctracker_config');
288 // Init update array here
289 crackerTrackerInitUpdates();
291 // Run any SQL updates recursively
292 while (isset($GLOBALS['ctracker_updates'][getCrackerTrackerConfig('ctracker_db_version')])) {
294 runCrackerTrackerUpdates(getCrackerTrackerConfig('ctracker_db_version'));
297 runCrackerTrackerSql('UPDATE `ctracker_config` SET `ctracker_db_version`=`ctracker_db_version`+1 WHERE `ctracker_config`=1 LIMIT 1', __FUNCTION__, __LINE__);
299 // And count it up in the config array
300 $GLOBALS['ctracker_config']['ctracker_db_version']++;
304 // Load the configuration
305 function crackerTrackerLoadConfig () {
306 // Construct SQL command and run it
307 $result = runCrackerTrackerSql('SELECT * FROM `ctracker_config` WHERE `ctracker_config`=1 LIMIT 1', __FUNCTION__, __LINE__);
310 $GLOBALS['ctracker_config'] = mysqli_fetch_array($result);
313 freeCrackerTrackerResult($result);
317 function getCrackerTrackerConfig (string $entry) {
318 // Is the config entry there?
319 if (!isset($GLOBALS['ctracker_config'][$entry])) {
320 // Then better die here, else we may have an endless loop
321 if (isCrackerTrackerDebug()) {
322 // Nicer message in debug mode
323 die('Configuration entry ' . $entry . ' missing!');
325 // die() on production systems
331 return $GLOBALS['ctracker_config'][$entry];
334 // Did the current IP already generated blocked attempts?
335 function isCrackerTrackerIpSuspicious () {
336 // Skip this silently if we have not config
337 if (!isCrackerTrackerDatabaseLinkUp()) {
338 // Skip this step silently, all is not suspicious
342 // Check if an entry is there
343 $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__);
346 list($rows) = mysqli_fetch_row($result);
348 // Is there one entry?
349 $found = ($rows > 0);
352 if ($found === true) {
353 // Yes, one is found, then load it
354 $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__);
357 $GLOBALS['ctracker_last_suspicious_entry'] = mysqli_fetch_array($result);
361 freeCrackerTrackerResult($result);
367 // Does the current IP have a ticket?
368 function ifCrackerTrackerIpHasTicket () {
369 // We only give one ticket per IP!
370 $result = runCrackerTrackerSql("SELECT * FROM `ctracker_ticket` WHERE `ctracker_ticket_remote_addr`='" . determineCrackerTrackerRealRemoteAddress() . "' OR `ctracker_ticket_proxy_addr`='" . getenv('REMOTE_ADDR') . "' LIMIT 1", __FUNCTION__, __LINE__);
372 // Do we have a ticket?
373 $found = (mysqli_num_rows($result) == 1);
376 if ($found === true) {
377 // Cache the ticket data
378 $GLOBALS['ctracker_last_ticket'] = mysqli_fetch_array($result);
382 freeCrackerTrackerResult($result);
388 // Adds a ticket based on given (mostly $_POST) data
389 function addCrackerTrackerTicket (array $data) {
391 $GLOBALS['ctracker_last_ticket'] = [
392 'ctracker_ticket_remote_addr' => determineCrackerTrackerRealRemoteAddress(),
393 'ctracker_ticket_proxy_addr' => getenv('REMOTE_ADDR'),
394 'ctracker_ticket_user_agent' => crackerTrackerUserAgent(),
395 'ctracker_ticket_name' => crackerTrackerSecureString($data['name']),
396 'ctracker_ticket_email' => crackerTrackerSecureString($data['email']),
397 'ctracker_ticket_comment' => crackerTrackerSecureString($data['comment'])
401 crackerTrackerInsertArray('ctracker_ticket', $GLOBALS['ctracker_last_ticket']);
403 // Is there an entry?
404 if ((isset($GLOBALS['ctracker_last_insert_id'])) && ($GLOBALS['ctracker_last_insert_id'] > 0)) {
405 // All fine, so prepare the link between ticket<->data
407 'ctracker_ticket_id' => $GLOBALS['ctracker_last_insert_id'],
408 'ctracker_data_id' => $GLOBALS['ctracker_last_suspicious_entry']['id']
411 // And insert it as well
412 crackerTrackerInsertArray('ctracker_ticket_data', $data);
414 // Add ticket id again
415 $GLOBALS['ctracker_ticket'] = $data['ctracker_ticket_id'];
417 // Merge all data for emails
418 $GLOBALS['ctracker_last_ticket'] = array_merge($GLOBALS['ctracker_last_ticket'], $data);
420 // Is this also there?
421 if ((isset($GLOBALS['ctracker_last_insert_id'])) && ($GLOBALS['ctracker_last_insert_id'] > 0)) {
422 // All fine, so display "thank you page"
423 crackerTrackerLoadTemplate('add_ticket_thanks');
434 // Frees given result instance
435 function freeCrackerTrackerResult (mysqli_result $result) {