tiki-report_string_in_db.php 3.33 KB
Newer Older
arildb's avatar
arildb committed
1
<?php
2 3 4
/**
 * @package tikiwiki
 */
5
// (c) Copyright 2002-2015 by authors of the Tiki Wiki CMS Groupware Project
6 7 8 9 10
//
// All Rights Reserved. See copyright.txt for details and a complete list of authors.
// Licensed under the GNU LESSER GENERAL PUBLIC LICENSE. See license.txt for details.
// $Id$

11 12 13 14 15 16 17 18
$inputConfiguration = array(
	array( 'staticKeyFilters' => array(
			'table' => 'word',
			'column' => 'word',
		)
	)
);

19
require_once ('tiki-setup.php');
20
$access->check_permission(array('tiki_p_admin'));
21 22

global $tikilib;
23
try {
24 25
	if (!empty($_POST['string_in_db_search'])) {
		$searchString = $_POST['string_in_db_search'];
26
		$result = searchAllDB($searchString);
arildb's avatar
arildb committed
27

28
		$smarty->assign('searchString', $searchString);
29
		$smarty->assign('searchResult', $result);
arildb's avatar
arildb committed
30

31 32 33
	} elseif (!empty($_POST['query'])) {
		$query = $_POST['query'];
		$table = $_POST['table'];
34
		sanitizeTableName($table);
35 36
		$column = $_POST['column'];
		sanitizeColumnName($column, $table);
37

38 39 40 41 42 43 44 45 46 47
		$headers = array();
		$sql2 = "SHOW COLUMNS FROM ".$table;
		$rs2 = $tikilib->fetchAll($sql2);
		foreach ($rs2 as $key2 => $val2) {
			$vals2 = array_values($val2);
			$colum = $vals2[0];
			$type = $vals2[1];
			$headers[] = $colum;
		}
		$smarty->assign('tableHeaders', $headers);
arildb's avatar
arildb committed
48

49 50 51 52 53 54 55 56 57
		$tableData = array();
		$qrySearch = '%'.$query.'%';
		$args = array($qrySearch);
		$sql = "select * from `" . $table . "` where `" . $column . "` like ?";
		$rs = $tikilib->fetchAll($sql, $args);
		foreach ($rs as $row) {
			$tableData[] = $row;
		}
		$smarty->assign('tableData', $tableData);
arildb's avatar
arildb committed
58
	}
59 60
} catch (Exception $e) {
	$smarty->assign('errorMsg', $e->getMessage());
61 62
}
$smarty->assign('mid', 'tiki-report_string_in_db.tpl');
arildb's avatar
arildb committed
63 64 65 66 67
$smarty->display('tiki.tpl');

/*
*	return array (table, attribute, occurrence count)
*/
pkdille's avatar
pkdille committed
68 69
function searchAllDB($search)
{
arildb's avatar
arildb committed
70 71 72
	global $tikilib;

	$result = array();
pkdille's avatar
pkdille committed
73
	$out = '';
arildb's avatar
arildb committed
74 75 76

	$sql = "show tables";
	$rs = $tikilib->fetchAll($sql);
pkdille's avatar
pkdille committed
77
	foreach ($rs as $key => $val) {
arildb's avatar
arildb committed
78 79
		$vals = array_values($val);
		$table = $vals[0];
80
		$sql2 = "SHOW COLUMNS FROM `$table`";
arildb's avatar
arildb committed
81
		$rs2 = $tikilib->fetchAll($sql2);
pkdille's avatar
pkdille committed
82
		foreach ($rs2 as $key2 => $val2) {
arildb's avatar
arildb committed
83 84 85 86 87
			$vals2 = array_values($val2);
			$colum = $vals2[0];
			$type = $vals2[1];
			if (isTextType($type)) {
				$sql_search_fields = Array();
88 89 90
				$qrySearch = '%'.$search.'%';
				$args = array($qrySearch);
				$sql_search_fields[] = "`" . $colum . "` like ?"; // '%" . str_replace("'", "''", $search) . "%'";
91
				$sql_search = "select * from `$table` where ";
arildb's avatar
arildb committed
92
				$sql_search .= implode(" OR ", $sql_search_fields);
93
				$rs3 = $tikilib->fetchAll($sql_search, $args);
arildb's avatar
arildb committed
94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112
				if (!empty($rs3)) {
					$result[] = array('table' => $table, 'column' => $colum, 'occurrences' => count($rs3));
				}
			}
		}
	}
	return $result;
}

function isTextType($type)
{
	if (strpos($type, 'char') !== false) {
		return true;
	}
	if (strpos($type, 'text') !== false) {
		return true;
	}
	return false;
}
113 114 115

function sanitizeTableName($table)
{
116 117 118 119
	global $tikilib;
	$validTables = $tikilib->listTables();
	if (!in_array($table, $validTables)) {
		throw new Exception(tra('Invalid table name:') . ' ' . htmlentities($table));
120 121 122
	}
}

123
function sanitizeColumnName($column, $table)
124
{
125 126 127 128 129 130 131
	global $tikilib;
	$colsinfo = $tikilib->fetchAll("SHOW COLUMNS FROM $table");
	foreach ($colsinfo as $col) {
		$colnames[] = $col['Field'];
	}
	if (!in_array($column, $colnames)) {
		throw new Exception(tra('Invalid column name:') . ' ' . htmlentities($column));
132 133
	}
}