SqlDriverProvider::checkTableExists() - Code Metrics - terrylinooo/shieldon - Measure and Improve Code Quality continuously with Scrutinizer

SqlDriverProvider::checkTableExists() A
last analyzed 2023-06-17 08:58 UTC

↳ Parent: SqlDriverProvider

Complexity

Conditions	3
Paths	3

Size

Total Lines	11
Code Lines	5

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	3
CRAP Score	3

Importance

Changes

Metric	Value
eloc	5
c	0
b	0
f	0
dl	0
loc	11
ccs	3
cts	3
cp	1
rs	10
cc	3
nc	3
nop	0
crap	3

<?php
/**
 * This file is part of the Shieldon package.
 *
 * (c) Terry L. <[email protected]>
 *
 * For the full copyright and license information, please view the LICENSE
 * file that was distributed with this source code.
 *
 * php version 7.1.0
 *
 * @category  Web-security
 * @package   Shieldon
 * @author    Terry Lin <[email protected]>
 * @copyright 2019 terrylinooo
 * @license   https://github.com/terrylinooo/shieldon/blob/2.x/LICENSE MIT
 * @link      https://github.com/terrylinooo/shieldon
 * @see       https://shieldon.io
 */

declare(strict_types=1);

namespace Shieldon\Firewall\Driver;

use Shieldon\Firewall\Driver\DriverProvider;
use Shieldon\Firewall\Driver\SqlDriverTrait;
use Exception;
use PDO;
use function array_merge;
use function gettype;
use function implode;

/**
 * SQL Driver provider.
 */
class SqlDriverProvider extends DriverProvider
{
    use SqlDriverTrait;

    /**
     * Data engine will be used on.
     *
     * @var string
     */
    protected $tableDbEngine = 'innodb';

    /**
     * PDO instance.
     *
     * @var object
     */
    protected $db;

    /**
     * Constructor.
     *
     * @param PDO  $pdo   The PDO instance.
     * @param bool $debug The option to enable debugging or not.
     *
     * @return void
     */
    public function __construct(PDO $pdo, bool $debug = false)
    {
        $this->db = $pdo;

        if ($debug) {
            $this->db->setAttribute($this->db::ATTR_ERRMODE, $this->db::ERRMODE_EXCEPTION);
        }
    }

    /**
     * Initialize data tables.
     *
     * @param bool $dbCheck This is for creating data tables automatically
     *                      Turn it off, if you don't want to check data tables every pageview.
     *
     * @return void
     */
    protected function doInitialize(bool $dbCheck = true): void
    {
        if (!$this->isInitialized) {
            if (!empty($this->channel)) {
                $this->setChannel($this->channel);
            }

            if ($dbCheck && !$this->checkTableExists()) {
                $this->installSql();
            }
        }

        $this->isInitialized = true;
    }

    /**
     * {@inheritDoc}
     *
     * @param string $ip   The data id of the entry to fetch.
     * @param string $type The type of the data table.
     *
     * @return array
     */
    protected function doFetch(string $ip, string $type = 'filter'): array
    {
        $tables = [
            'rule' => 'doFetchFromRuleTable',
            'filter' => 'doFetchFromFilterTable',
            'session' => 'doFetchFromSessionTable',
        ];

        $method = $tables[$type];

        // Fetch from SqlDriverTrait.
        return $this->{$method}($ip);
    }

    /**
     * {@inheritDoc}
     *
     * @param string $type The type of the data table.
     *
     * @return bool
     */
    protected function doFetchAll(string $type = 'filter'): array
    {
        $this->assertInvalidDataTable($type);

        $tables = [
            'rule' => 'doFetchAllFromRuleTable',
            'filter' => 'doFetchAllFromFilterTable',
            'session' => 'doFetchAllFromSessionTable',
        ];
 
        $method = $tables[$type];

        // Fetch from SqlDriverTrait.
        return $this->{$method}();
    }

    /**
     * {@inheritDoc}
     *
     * @param string $ip   The data id of the entry to check for.
     * @param string $type The type of the data table.
     *
     * @return bool
     */
    protected function checkExist(string $ip, string $type = 'filter'): bool
    {
        $tables = [
            'rule' => [
                'table' => $this->tableRuleList,
                'field' => 'log_ip',
            ],
            'filter' => [
                'table' => $this->tableFilterLogs,
                'field' => 'log_ip',
            ],
            'session' => [
                'table' => $this->tableSessions,
                'field' => 'id',
            ],
        ];

        $tableName = $tables[$type]['table'];
        $field = $tables[$type]['field'];

        $sql = 'SELECT ' . $field . ' FROM ' . $tableName . '
            WHERE ' . $field . ' = :' . $field . '
            LIMIT 1';

        $query = $this->db->prepare($sql);
        $query->bindValue(':' . $field, $ip);

        $query->execute();
        $result = $query->fetch();

        if (!empty($result[$field])) {
            return true;
        }

        return false;
    }

    /**
     * {@inheritDoc}
     *
     * @param string $ip     The data id.
     * @param array  $data   The data.
     * @param string $type   The type of the data table.
     * @param int    $expire The data will be deleted after expiring.
     *
     * @return bool
     */
    protected function doSave(string $ip, array $data, string $type = 'filter', $expire = 0): bool
    {
        $this->assertInvalidDataTable($type);

        $tableName = '';
        $logData = [];
        $logWhere = [];

        switch ($type) {
            case 'rule':
                $tableName = $this->tableRuleList;

                $logWhere = [];
                $logWhere['log_ip'] = $ip;

                $logData = $data;
                $logData['log_ip'] = $ip;
                break;

            case 'filter':
                $tableName = $this->tableFilterLogs;

                $logWhere = [];
                $logWhere['log_ip'] = $ip;

                $logData = [];
                $logData['log_ip'] = $ip;
                $logData['log_data'] = json_encode($data);
                break;

            case 'session':
                $tableName = $this->tableSessions;

                $logWhere = [];
                $logWhere['id'] = $data['id'];
                unset($data['parsed_data']);
                $logData = $data;
                break;
        }

        if ($this->checkExist($ip, $type)) {
            return $this->update($tableName, $logData, $logWhere);
        }

        return (bool) $this->insert($tableName, $logData);
    }

    /**
     * {@inheritDoc}
     *
     * @param string $ip   The key name of a redis entry.
     * @param string $type The type of the data table.
     *
     * @return bool
     */
    protected function doDelete(string $ip, string $type = 'filter'): bool
    {
        $this->assertInvalidDataTable($type);

        $tables = [
            'rule' => [
                'table' => $this->tableRuleList,
                'field' => 'log_ip',
                'value' => $ip,
            ],
            'filter' => [
                'table' => $this->tableFilterLogs,
                'field' => 'log_ip',
                'value' => $ip,
            ],
            'session' => [
                'table' => $this->tableSessions,
                'field' => 'id',
                'value' => $ip,
            ],
        ];

        $tableName = $tables[$type]['table'];
        $field = $tables[$type]['field'];
        $value = $tables[$type]['value'];

        return $this->remove($tableName, [$field => $value]);
    }

    /**
     * {@inheritDoc}
     *
     * Rebuild data tables.
     *
     * @return bool
     */
    protected function doRebuild(): bool
    {
        return $this->rebuildSql();
    }

    /**
     * Update database table.
     *
     * @param string $table The data table name.
     * @param array  $data  The SELECT statement.
     * @param array  $where The array creates WHERE clause.
     *
     * @return bool
     */
    protected function update(string $table, array $data, array $where)
    {
        $placeholder = [];
        foreach ($data as $k => $v) {
            $placeholder[] = "$k = :$k";
        }

        $dataPlaceholder = implode(', ', $placeholder);

        $placeholder = [];
        foreach ($where as $k => $v) {
            $placeholder[] = "$k = :$k";
        }

        $wherePlaceholder = implode(' AND ', $placeholder);

        try {
            $paramTypeCheck = [
                'integer' => $this->db::PARAM_INT,
                'boolean' => $this->db::PARAM_BOOL,
                'NULL'    => $this->db::PARAM_NULL,
                'string'  => $this->db::PARAM_STR,
            ];


            $sql = 'UPDATE ' . $table . ' SET ' . $dataPlaceholder . ' WHERE ' . $wherePlaceholder;
            $query = $this->db->prepare($sql);

            $bind = array_merge($data, $where);

            foreach ($bind as $k => $v) {
                $pdoParam = $paramTypeCheck[gettype($v)];
  
                // Solve problem with bigint.
                if ($v >= 2147483647) {
                    // @codeCoverageIgnoreStart
                    $pdoParam = $this->db::PARAM_STR;
                    // @codeCoverageIgnoreEnd
                }

                $query->bindValue(":$k", $bind[$k], $pdoParam);
            }

            return $query->execute();

            // @codeCoverageIgnoreStart
        } catch (Exception $e) {
            return false;
        }
        // @codeCoverageIgnoreEnd
    }

    /**
     * Insert database table.
     *
     * @param string $table The data table name.
     * @param array  $data  The data want to insert to the table.
     *
     * @return bool
     */
    protected function insert(string $table, array $data)
    {
        $placeholderField = [];
        $placeholderValue = [];
        foreach ($data as $k => $v) {
            $placeholderField[] = "`$k`";
            $placeholderValue[] = ":$k";
        }

        $dataPlaceholderField = implode(', ', $placeholderField);
        $dataPlaceholderValue = implode(', ', $placeholderValue);

        try {
            $paramTypeCheck = [
                'integer' => $this->db::PARAM_INT,
                'boolean' => $this->db::PARAM_BOOL,
                'NULL'    => $this->db::PARAM_NULL,
                'string'  => $this->db::PARAM_STR,
            ];

            $sql = 'INSERT INTO ' . $table . ' (' . $dataPlaceholderField . ') VALUES (' . $dataPlaceholderValue . ')';
            $query = $this->db->prepare($sql);

            foreach ($data as $k => $v) {
                $pdoParam = $paramTypeCheck[gettype($v)];

                // Solve problem with bigint.
                if ($v >= 2147483647) {
                    $pdoParam = $this->db::PARAM_STR;
                }

                $query->bindValue(":$k", $data[$k], $pdoParam);
            }

            return $query->execute();

            // @codeCoverageIgnoreStart
        } catch (Exception $e) {
            return false;
        }
        // @codeCoverageIgnoreEnd
    }

    /**
     * Remove a row from a table.
     *
     * @param string $table The data table name.
     * @param array  $where The array creates WHERE clause.
     *
     * @return bool
     */
    protected function remove(string $table, array $where): bool
    {

        $placeholder = [];
        foreach ($where as $k => $v) {
            $placeholder[] = "`$k` = :$k";
        }

        $dataPlaceholder = implode(' AND ', $placeholder);

        try {
            $paramTypeCheck = [
                'integer' => $this->db::PARAM_INT,
                'boolean' => $this->db::PARAM_BOOL,
                'NULL'    => $this->db::PARAM_NULL,
                'string'  => $this->db::PARAM_STR,
            ];

            $sql = 'DELETE FROM ' . $table . ' WHERE ' . $dataPlaceholder;
            $query = $this->db->prepare($sql);

            foreach ($where as $k => $v) {
                $pdoParam = $paramTypeCheck[gettype($v)];

                $query->bindValue(":$k", $v, $pdoParam);
            }

            return $query->execute();
            // @codeCoverageIgnoreStart
        } catch (Exception $e) {
            return false;
        }
        // @codeCoverageIgnoreEnd
    }

    /**
     * Create SQL tables that Shieldon needs.
     *
     * @return bool
     */
    protected function installSql(): bool
    {
        try {
            $sql = "
                CREATE TABLE IF NOT EXISTS `{$this->tableFilterLogs}` (
                    `log_ip` varchar(46) NOT NULL,
                    `log_data` blob,
                    PRIMARY KEY (`log_ip`)
                ) ENGINE={$this->tableDbEngine} DEFAULT CHARSET=latin1;
            ";

            $this->db->query($sql);

            $sql = "
                CREATE TABLE IF NOT EXISTS `{$this->tableRuleList}` (
                    `log_ip` varchar(46) NOT NULL,
                    `ip_resolve` varchar(255) NOT NULL,
                    `type` tinyint(3) UNSIGNED NOT NULL,
                    `reason` tinyint(3) UNSIGNED NOT NULL,
                    `time` int(10) UNSIGNED NOT NULL,
                    `attempts` int(10) UNSIGNED DEFAULT 0,
                    PRIMARY KEY (`log_ip`)
                ) ENGINE={$this->tableDbEngine} DEFAULT CHARSET=latin1;
            ";

            $this->db->query($sql);

            $sql = "
                CREATE TABLE `{$this->tableSessions}` (
                    `id` varchar(40) NOT NULL,
                    `ip` varchar(46) NOT NULL,
                    `time` int(10) UNSIGNED NOT NULL,
                    `microtimestamp` bigint(20) UNSIGNED NOT NULL,
                    `data` blob,
                    PRIMARY KEY (`id`)
                ) ENGINE={$this->tableDbEngine} DEFAULT CHARSET=latin1;
            ";

            $this->db->query($sql);

            // @codeCoverageIgnoreStart
        } catch (Exception $e) {
            return false;
        }
        // @codeCoverageIgnoreEnd

        return true;
    }

    /**
     * Clean all records in IP log and IP rule tables, and then rebuild new tables.
     *
     * @return bool
     */
    protected function rebuildSql(): bool
    {
        try {
            $sql = "DROP TABLE IF EXISTS `{$this->tableFilterLogs}`";
            $this->db->query($sql);
            $sql = "DROP TABLE IF EXISTS `{$this->tableRuleList}`";
            $this->db->query($sql);
            $sql = "DROP TABLE IF EXISTS `{$this->tableSessions}`";
            $this->db->query($sql);

            $this->installSql();

            // @codeCoverageIgnoreStart
        } catch (Exception $e) {
            return false;
        }
        // @codeCoverageIgnoreEnd

        return true;
    }

    /**
     * Check required tables exist or not.
     *
     * @return bool
     */
    protected function checkTableExists(): bool
    {
        $checkLogTable = $this->db->query("SHOW TABLES LIKE '{$this->tableFilterLogs}'");

        if ($checkLogTable) {
            if ($checkLogTable->rowCount() > 0) {
                return true;
            }
        }

        return false;
    }
}


1		<?php
2		/**
3		* This file is part of the Shieldon package.
4		*
5		* (c) Terry L. <[email protected]>
6		*
7		* For the full copyright and license information, please view the LICENSE
8		* file that was distributed with this source code.
9		*
10		* php version 7.1.0
11		*
12		* @category Web-security
13		* @package Shieldon
14		* @author Terry Lin <[email protected]>
15		* @copyright 2019 terrylinooo
16		* @license https://github.com/terrylinooo/shieldon/blob/2.x/LICENSE MIT
17		* @link https://github.com/terrylinooo/shieldon
18		* @see https://shieldon.io
19		*/
20
21		declare(strict_types=1);
22
23		namespace Shieldon\Firewall\Driver;
24
25		use Shieldon\Firewall\Driver\DriverProvider;
26		use Shieldon\Firewall\Driver\SqlDriverTrait;
27		use Exception;
28		use PDO;
29		use function array_merge;
30		use function gettype;
31		use function implode;
32
33		/**
34		* SQL Driver provider.
35		*/
36		class SqlDriverProvider extends DriverProvider
37		{
38		use SqlDriverTrait;
39
40		/**
41		* Data engine will be used on.
42		*
43		* @var string
44		*/
45		protected $tableDbEngine = 'innodb';
46
47		/**
48		* PDO instance.
49		*
50		* @var object
51		*/
52		protected $db;
53
54		/**
55		* Constructor.
56		*
57		* @param PDO $pdo The PDO instance.
58		* @param bool $debug The option to enable debugging or not.
59		*
60		* @return void
61		*/
62	48	public function __construct(PDO $pdo, bool $debug = false)
63		{
64	48	$this->db = $pdo;
65
66	48	if ($debug) {
67	1	$this->db->setAttribute($this->db::ATTR_ERRMODE, $this->db::ERRMODE_EXCEPTION);
68		}
69		}
70
71		/**
72		* Initialize data tables.
73		*
74		* @param bool $dbCheck This is for creating data tables automatically
75		* Turn it off, if you don't want to check data tables every pageview.
76		*
77		* @return void
78		*/
79	38	protected function doInitialize(bool $dbCheck = true): void
80		{
81	38	if (!$this->isInitialized) {
82	38	if (!empty($this->channel)) {
83	34	$this->setChannel($this->channel);
84		}
85
86	38	if ($dbCheck && !$this->checkTableExists()) {
87	4	$this->installSql();
88		}
89		}
90
91	38	$this->isInitialized = true;
92		}
93
94		/**
95		* {@inheritDoc}
96		*
97		* @param string $ip The data id of the entry to fetch.
98		* @param string $type The type of the data table.
99		*
100		* @return array
101		*/
102	33	protected function doFetch(string $ip, string $type = 'filter'): array
103		{
104	33	$tables = [
105	33	'rule' => 'doFetchFromRuleTable',
106	33	'filter' => 'doFetchFromFilterTable',
107	33	'session' => 'doFetchFromSessionTable',
108	33	];
109
110	33	$method = $tables[$type];
111
112		// Fetch from SqlDriverTrait.
113	33	return $this->{$method}($ip);
114		}
115
116		/**
117		* {@inheritDoc}
118		*
119		* @param string $type The type of the data table.
120		*
121		* @return bool
122		*/
123	11	protected function doFetchAll(string $type = 'filter'): array
124		{
125	11	$this->assertInvalidDataTable($type);
126
127	11	$tables = [
128	11	'rule' => 'doFetchAllFromRuleTable',
129	11	'filter' => 'doFetchAllFromFilterTable',
130	11	'session' => 'doFetchAllFromSessionTable',
131	11	];
132
133	11	$method = $tables[$type];
134
135		// Fetch from SqlDriverTrait.
136	11	return $this->{$method}();
137		}
138
139		/**
140		* {@inheritDoc}
141		*
142		* @param string $ip The data id of the entry to check for.
143		* @param string $type The type of the data table.
144		*
145		* @return bool
146		*/
147	36	protected function checkExist(string $ip, string $type = 'filter'): bool
148		{
149	36	$tables = [
150	36	'rule' => [
151	36	'table' => $this->tableRuleList,
152	36	'field' => 'log_ip',
153	36	],
154	36	'filter' => [
155	36	'table' => $this->tableFilterLogs,
156	36	'field' => 'log_ip',
157	36	],
158	36	'session' => [
159	36	'table' => $this->tableSessions,
160	36	'field' => 'id',
161	36	],
162	36	];
163
164	36	$tableName = $tables[$type]['table'];
165	36	$field = $tables[$type]['field'];
166
167	36	$sql = 'SELECT ' . $field . ' FROM ' . $tableName . '
168	36	WHERE ' . $field . ' = :' . $field . '
169	36	LIMIT 1';
170
171	36	$query = $this->db->prepare($sql);
172	36	$query->bindValue(':' . $field, $ip);
173
174	36	$query->execute();
175	36	$result = $query->fetch();
176
177	36	if (!empty($result[$field])) {
178	23	return true;
179		}
180
181	36	return false;
182		}
183
184		/**
185		* {@inheritDoc}
186		*
187		* @param string $ip The data id.
188		* @param array $data The data.
189		* @param string $type The type of the data table.
190		* @param int $expire The data will be deleted after expiring.
191		*
192		* @return bool
193		*/
194	36	protected function doSave(string $ip, array $data, string $type = 'filter', $expire = 0): bool
195		{
196	36	$this->assertInvalidDataTable($type);
197
198	36	$tableName = '';
199	36	$logData = [];
200	36	$logWhere = [];
201
202		switch ($type) {
203		case 'rule':
204	36	$tableName = $this->tableRuleList;
205	20
206		$logWhere = [];
207	20	$logWhere['log_ip'] = $ip;
208	20
209		$logData = $data;
210	20	$logData['log_ip'] = $ip;
211	20	break;
212	20
213		case 'filter':
214	36	$tableName = $this->tableFilterLogs;
215	24
216		$logWhere = [];
217	24	$logWhere['log_ip'] = $ip;
218	24
219		$logData = [];
220	24	$logData['log_ip'] = $ip;
221	24	$logData['log_data'] = json_encode($data);
222	24	break;
223	24
224		case 'session':
225	36	$tableName = $this->tableSessions;
226	36
227		$logWhere = [];
228	36	$logWhere['id'] = $data['id'];
229	36	unset($data['parsed_data']);
230	36	$logData = $data;
231	36	break;
232	36	}
233
234		if ($this->checkExist($ip, $type)) {
235	36	return $this->update($tableName, $logData, $logWhere);
236	23	}
237
238		return (bool) $this->insert($tableName, $logData);
239	36	}
240
241		/**
242		* {@inheritDoc}
243		*
244		* @param string $ip The key name of a redis entry.
245		* @param string $type The type of the data table.
246		*
247		* @return bool
248		*/
249		protected function doDelete(string $ip, string $type = 'filter'): bool
250	23	{
251		$this->assertInvalidDataTable($type);
252	23
253		$tables = [
254	22	'rule' => [
255	22	'table' => $this->tableRuleList,
256	22	'field' => 'log_ip',
257	22	'value' => $ip,
258	22	],
259	22	'filter' => [
260	22	'table' => $this->tableFilterLogs,
261	22	'field' => 'log_ip',
262	22	'value' => $ip,
263	22	],
264	22	'session' => [
265	22	'table' => $this->tableSessions,
266	22	'field' => 'id',
267	22	'value' => $ip,
268	22	],
269	22	];
270	22
271		$tableName = $tables[$type]['table'];
272	22	$field = $tables[$type]['field'];
273	22	$value = $tables[$type]['value'];
274	22
275		return $this->remove($tableName, [$field => $value]);
276	22	}
277
278		/**
279		* {@inheritDoc}
280		*
281		* Rebuild data tables.
282		*
283		* @return bool
284		*/
285		protected function doRebuild(): bool
286	26	{
287		return $this->rebuildSql();
288	26	}
289
290		/**
291		* Update database table.
292		*
293		* @param string $table The data table name.
294		* @param array $data The SELECT statement.
295		* @param array $where The array creates WHERE clause.
296		*
297		* @return bool
298		*/
299		protected function update(string $table, array $data, array $where)
300	23	{
301		$placeholder = [];
302	23	foreach ($data as $k => $v) {
303	23	$placeholder[] = "$k = :$k";
304	23	}
305
306		$dataPlaceholder = implode(', ', $placeholder);
307	23
308		$placeholder = [];
309	23	foreach ($where as $k => $v) {
310	23	$placeholder[] = "$k = :$k";
311	23	}
312
313		$wherePlaceholder = implode(' AND ', $placeholder);
314	23
315		try {
316		$paramTypeCheck = [
317		'integer' => $this->db::PARAM_INT,
318	23	'boolean' => $this->db::PARAM_BOOL,
319	23	'NULL' => $this->db::PARAM_NULL,
320	23	'string' => $this->db::PARAM_STR,
321	23	];
322	23
323	23
324		$sql = 'UPDATE ' . $table . ' SET ' . $dataPlaceholder . ' WHERE ' . $wherePlaceholder;
325		$query = $this->db->prepare($sql);
326	23
327	23	$bind = array_merge($data, $where);
328
329	23	foreach ($bind as $k => $v) {
330		$pdoParam = $paramTypeCheck[gettype($v)];
331	23
332	23	// Solve problem with bigint.
333		if ($v >= 2147483647) {
334		// @codeCoverageIgnoreStart
335	23	$pdoParam = $this->db::PARAM_STR;
336		// @codeCoverageIgnoreEnd
337		}
338
339		$query->bindValue(":$k", $bind[$k], $pdoParam);
340		}
341	23
342		return $query->execute();
343
344	23	// @codeCoverageIgnoreStart
345		} catch (Exception $e) {
346		return false;
347		}
348		// @codeCoverageIgnoreEnd
349		}
350
351		/**
352		* Insert database table.
353		*
354		* @param string $table The data table name.
355		* @param array $data The data want to insert to the table.
356		*
357		* @return bool
358		*/
359		protected function insert(string $table, array $data)
360		{
361		$placeholderField = [];
362		$placeholderValue = [];
363	36	foreach ($data as $k => $v) {
364		$placeholderField[] = "`$k`";
365	36	$placeholderValue[] = ":$k";
366	36	}
367	36
368	36	$dataPlaceholderField = implode(', ', $placeholderField);
369	36	$dataPlaceholderValue = implode(', ', $placeholderValue);
370
371		try {
372	36	$paramTypeCheck = [
373	36	'integer' => $this->db::PARAM_INT,
374		'boolean' => $this->db::PARAM_BOOL,
375		'NULL' => $this->db::PARAM_NULL,
376		'string' => $this->db::PARAM_STR,
377	36	];
378	36
379	36	$sql = 'INSERT INTO ' . $table . ' (' . $dataPlaceholderField . ') VALUES (' . $dataPlaceholderValue . ')';
380	36	$query = $this->db->prepare($sql);
381	36
382	36	foreach ($data as $k => $v) {
383		$pdoParam = $paramTypeCheck[gettype($v)];
384	36
385	36	// Solve problem with bigint.
386		if ($v >= 2147483647) {
387	36	$pdoParam = $this->db::PARAM_STR;
388	36	}
389
390		$query->bindValue(":$k", $data[$k], $pdoParam);
391	36	}
392	36
393		return $query->execute();
394
395	36	// @codeCoverageIgnoreStart
396		} catch (Exception $e) {
397		return false;
398	36	}
399		// @codeCoverageIgnoreEnd
400		}
401
402		/**
403		* Remove a row from a table.
404		*
405		* @param string $table The data table name.
406		* @param array $where The array creates WHERE clause.
407		*
408		* @return bool
409		*/
410		protected function remove(string $table, array $where): bool
411		{
412
413		$placeholder = [];
414		foreach ($where as $k => $v) {
415	22	$placeholder[] = "`$k` = :$k";
416		}
417
418	22	$dataPlaceholder = implode(' AND ', $placeholder);
419	22
420	22	try {
421		$paramTypeCheck = [
422		'integer' => $this->db::PARAM_INT,
423	22	'boolean' => $this->db::PARAM_BOOL,
424		'NULL' => $this->db::PARAM_NULL,
425		'string' => $this->db::PARAM_STR,
426		];
427	22
428	22	$sql = 'DELETE FROM ' . $table . ' WHERE ' . $dataPlaceholder;
429	22	$query = $this->db->prepare($sql);
430	22
431	22	foreach ($where as $k => $v) {
432	22	$pdoParam = $paramTypeCheck[gettype($v)];
433
434	22	$query->bindValue(":$k", $v, $pdoParam);
435	22	}
436
437	22	return $query->execute();
438	22	// @codeCoverageIgnoreStart
439		} catch (Exception $e) {
440	22	return false;
441		}
442		// @codeCoverageIgnoreEnd
443	22	}
444
445		/**
446		* Create SQL tables that Shieldon needs.
447		*
448		* @return bool
449		*/
450		protected function installSql(): bool
451		{
452		try {
453		$sql = "
454		CREATE TABLE IF NOT EXISTS `{$this->tableFilterLogs}` (
455		`log_ip` varchar(46) NOT NULL,
456		`log_data` blob,
457	13	PRIMARY KEY (`log_ip`)
458		) ENGINE={$this->tableDbEngine} DEFAULT CHARSET=latin1;
459		";
460
461	13	$this->db->query($sql);
462	13
463		$sql = "
464		CREATE TABLE IF NOT EXISTS `{$this->tableRuleList}` (
465		`log_ip` varchar(46) NOT NULL,
466	13	`ip_resolve` varchar(255) NOT NULL,
467	13	`type` tinyint(3) UNSIGNED NOT NULL,
468		`reason` tinyint(3) UNSIGNED NOT NULL,
469	13	`time` int(10) UNSIGNED NOT NULL,
470		`attempts` int(10) UNSIGNED DEFAULT 0,
471	13	PRIMARY KEY (`log_ip`)
472	13	) ENGINE={$this->tableDbEngine} DEFAULT CHARSET=latin1;
473		";
474
475		$this->db->query($sql);
476
477		$sql = "
478		CREATE TABLE `{$this->tableSessions}` (
479		`id` varchar(40) NOT NULL,
480	13	`ip` varchar(46) NOT NULL,
481	13	`time` int(10) UNSIGNED NOT NULL,
482		`microtimestamp` bigint(20) UNSIGNED NOT NULL,
483	13	`data` blob,
484		PRIMARY KEY (`id`)
485	13	) ENGINE={$this->tableDbEngine} DEFAULT CHARSET=latin1;
486	13	";
487
488		$this->db->query($sql);
489
490		// @codeCoverageIgnoreStart
491		} catch (Exception $e) {
492		return false;
493	13	}
494	13	// @codeCoverageIgnoreEnd
495
496	13	return true;
497		}
498
499		/**
500		* Clean all records in IP log and IP rule tables, and then rebuild new tables.
501		*
502		* @return bool
503		*/
504	13	protected function rebuildSql(): bool
505		{
506		try {
507		$sql = "DROP TABLE IF EXISTS `{$this->tableFilterLogs}`";
508		$this->db->query($sql);
509		$sql = "DROP TABLE IF EXISTS `{$this->tableRuleList}`";
510		$this->db->query($sql);
511		$sql = "DROP TABLE IF EXISTS `{$this->tableSessions}`";
512	26	$this->db->query($sql);
513
514		$this->installSql();
515	26
516	26	// @codeCoverageIgnoreStart
517	26	} catch (Exception $e) {
518	26	return false;
519	26	}
520	26	// @codeCoverageIgnoreEnd
521
522	26	return true;
523		}
524
525		/**
526		* Check required tables exist or not.
527		*
528		* @return bool
529		*/
530	26	protected function checkTableExists(): bool
531		{
532		$checkLogTable = $this->db->query("SHOW TABLES LIKE '{$this->tableFilterLogs}'");
533
534		if ($checkLogTable) {
535		if ($checkLogTable->rowCount() > 0) {
536		return true;
537		}
538	17	}
539
540	17	return false;
541		}
542		}
543

terrylinooo / shieldon

SqlDriverProvider::checkTableExists() A last analyzed 2023-06-17 08:58 UTC

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like

SqlDriverProvider::checkTableExists() A
last analyzed 2023-06-17 08:58 UTC