SqlDriverProvider::insert() - Code Metrics - Inspection of "Improve code quality." - terrylinooo/shieldon - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — 2.x ( df5dec...d1e533 )

by Terry

created 2020-08-04 06:27 UTC

SqlDriverProvider::insert() A

↳ Parent: SqlDriverProvider

Complexity

Conditions	5
Paths	26

Size

Total Lines	48
Code Lines	24

Duplication

Lines	0
Ratio	0 %

Importance

Changes	3
Bugs	0	Features	0

Metric	Value
cc	5
eloc	24
c	3
b	0
f	0
nc	26
nop	2
dl	0
loc	48
rs	9.2248

<?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 gettype;

/**
 * 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);

        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'];

                $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) {

                // Default.
            //    $pdoParam = $paramTypeCheck['string'];

                $type = gettype($v);

             //   if (isset($paramTypeCheck[$type])) {
                    $pdoParam = $paramTypeCheck[$type];
             //   }

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

                $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) {

                // Default.
                //$pdoParam = $paramTypeCheck['string'];

                $type = gettype($v);

              //  if (isset($paramTypeCheck[$type])) {
                    $pdoParam = $paramTypeCheck[$type];
              //  }

                // 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) {

                // Default.
                $pdoParam = $paramTypeCheck['string'];

                $type = gettype($v);

                if (isset($paramTypeCheck[$type])) {
                    $pdoParam = $paramTypeCheck[$type];
                }

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

terrylinooo / shieldon

Push — 2.x ( df5dec...d1e533 )

SqlDriverProvider::insert() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like