1
<?php
2
/**
3
 * This file is part of the Shieldon package.
4
 *
5
 * (c) Terry L. <contact@terryl.in>
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 <contact@terryl.in>
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 3
    public function __construct(PDO $pdo, bool $debug = false)
63
    {
64 3
        $this->db = $pdo;
65

66 3
        if ($debug) {
67 3
            $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 3
    protected function doInitialize(bool $dbCheck = true): void
80
    {
81 3
        if (!$this->isInitialized) {
82 3
            if (!empty($this->channel)) {
83 3
                $this->setChannel($this->channel);
84
            }
85

86 3
            if ($dbCheck && !$this->checkTableExists()) {
87 3
                $this->installSql();
88
            }
89
        }
90

91 3
        $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 3
    protected function doFetch(string $ip, string $type = 'filter'): array
103
    {
104
        $tables = [
105 3
            'rule' => 'doFetchFromRuleTable',
106
            'filter' => 'doFetchFromFilterTable',
107
            'session' => 'doFetchFromSessionTable',
108
        ];
109

110 3
        $method = $tables[$type];
111

112
        // Fetch from SqlDriverTrait.
113 3
        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 3
    protected function doFetchAll(string $type = 'filter'): array
124
    {
125 3
        $this->assertInvalidDataTable($type);
126

127
        $tables = [
128 3
            'rule' => 'doFetchAllFromRuleTable',
129
            'filter' => 'doFetchAllFromFilterTable',
130
            'session' => 'doFetchAllFromSessionTable',
131
        ];
132
 
133 3
        $method = $tables[$type];
134

135
        // Fetch from SqlDriverTrait.
136 3
        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 3
    protected function checkExist(string $ip, string $type = 'filter'): bool
148
    {
149
        $tables = [
150
            'rule' => [
151 3
                'table' => $this->tableRuleList,
152 3
                'field' => 'log_ip',
153
            ],
154
            'filter' => [
155 3
                'table' => $this->tableFilterLogs,
156 3
                'field' => 'log_ip',
157
            ],
158
            'session' => [
159 3
                'table' => $this->tableSessions,
160 3
                'field' => 'id',
161
            ],
162
        ];
163

164 3
        $tableName = $tables[$type]['table'];
165 3
        $field = $tables[$type]['field'];
166

167 3
        $sql = 'SELECT ' . $field . ' FROM ' . $tableName . '
168 3
            WHERE ' . $field . ' = :' . $field . '
169
            LIMIT 1';
170

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

174 3
        $query->execute();
175 3
        $result = $query->fetch();
176

177 3
        if (!empty($result[$field])) {
178 3
            return true; 
179
        }
180

181 3
        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 3
    protected function doSave(string $ip, array $data, string $type = 'filter', $expire = 0): bool
195
    {
196 3
        $this->assertInvalidDataTable($type);
197

198 3
        $tableName = '';
199 3
        $logData = [];
200 3
        $logWhere = [];
201

202 2
        switch ($type) {
203

204 3
            case 'rule':
205 3
                $tableName = $this->tableRuleList;
206

207 3
                $logWhere = [];
208 3
                $logWhere['log_ip'] = $ip;
209

210 3
                $logData = $data;
211 3
                $logData['log_ip'] = $ip;
212 3
                break;
213

214 3
            case 'filter':
215 3
                $tableName = $this->tableFilterLogs;
216

217 3
                $logWhere = [];
218 3
                $logWhere['log_ip'] = $ip;
219

220 3
                $logData = [];
221 3
                $logData['log_ip'] = $ip;
222 3
                $logData['log_data'] = json_encode($data);
223 3
                break;
224

225 3
            case 'session':
226 3
                $tableName = $this->tableSessions;
227

228 3
                $logWhere = [];
229 3
                $logWhere['id'] = $data['id'];
230 3
                unset($data['parsed_data']);
231 3
                $logData = $data;
232 3
                break;
233
        }
234

235 3
        if ($this->checkExist($ip, $type)) {
236 3
            return $this->update($tableName, $logData, $logWhere);
237
        }
238

239 3
        return (bool) $this->insert($tableName, $logData);
240
    }
241

242
    /**
243
     * {@inheritDoc}
244
     * 
245
     * @param string $ip   The key name of a redis entry.
246
     * @param string $type The type of the data table.
247
     * 
248
     * @return bool
249
     */
250 3
    protected function doDelete(string $ip, string $type = 'filter'): bool
251
    {
252 3
        $this->assertInvalidDataTable($type);
253

254
        $tables = [
255
            'rule' => [
256 3
                'table' => $this->tableRuleList,
257 3
                'field' => 'log_ip',
258 3
                'value' => $ip,
259
            ],
260
            'filter' => [
261 3
                'table' => $this->tableFilterLogs,
262 3
                'field' => 'log_ip',
263 3
                'value' => $ip,
264
            ],
265
            'session' => [
266 3
                'table' => $this->tableSessions,
267 3
                'field' => 'id',
268 3
                'value' => $ip,
269
            ],
270
        ];
271

272 3
        $tableName = $tables[$type]['table'];
273 3
        $field = $tables[$type]['field'];
274 3
        $value = $tables[$type]['value'];
275

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

279
    /**
280
     * {@inheritDoc}
281
     * 
282
     * Rebuild data tables.
283
     *
284
     * @return bool
285
     */
286 3
    protected function doRebuild(): bool
287
    {
288 3
        return $this->rebuildSql();
289
    }
290

291
    /**
292
     * Update database table.
293
     *
294
     * @param string $table The data table name.
295
     * @param array  $data  The SELECT statement.
296
     * @param array  $where The array creates WHERE clause.
297
     *
298
     * @return bool
299
     */
300 3
    protected function update(string $table, array $data, array $where)
301
    {
302 3
        $placeholder = [];
303 3
        foreach ($data as $k => $v) {
304 3
            $placeholder[] = "$k = :$k";
305
        }
306

307 3
        $dataPlaceholder = implode(', ', $placeholder);
308

309 3
        $placeholder = [];
310 3
        foreach ($where as $k => $v) {
311 3
            $placeholder[] = "$k = :$k";
312
        }
313

314 3
        $wherePlaceholder = implode(' AND ', $placeholder);
315

316
        try {
317

318
            $paramTypeCheck = [
319 3
                'integer' => $this->db::PARAM_INT,
320 3
                'boolean' => $this->db::PARAM_BOOL,
321 3
                'NULL'    => $this->db::PARAM_NULL,
322 3
                'string'  => $this->db::PARAM_STR,
323
            ];
324

325

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

329 3
            $bind = array_merge($data, $where);
330

331 3
            foreach ($bind as $k => $v) {
332 3
                $pdoParam = $paramTypeCheck[gettype($v)];
333
  
334
                // Solve problem with bigint.
335 3
                if ($v >= 2147483647) {
336
                    // @codeCoverageIgnoreStart
337
                    $pdoParam = $this->db::PARAM_STR;
338
                    // @codeCoverageIgnoreEnd
339
                }
340

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

344 3
            return $query->execute();
345

346
            // @codeCoverageIgnoreStart
347
        
348
        } catch (Exception $e) {
349
            return false;
350
        }
351

352
        // @codeCoverageIgnoreEnd 
353
    }
354

355
    /**
356
     * Insert database table.
357
     *
358
     * @param string $table The data table name.
359
     * @param array  $data  The data want to insert to the table.
360
     *
361
     * @return bool
362
     */
363 3
    protected function insert(string $table, array $data)
364
    {
365 3
        $placeholderField = [];
366 3
        $placeholderValue = [];
367 3
        foreach ($data as $k => $v) {
368 3
            $placeholderField[] = "`$k`";
369 3
            $placeholderValue[] = ":$k";
370
        }
371

372 3
        $dataPlaceholderField = implode(', ', $placeholderField);
373 3
        $dataPlaceholderValue = implode(', ', $placeholderValue);
374

375
        try {
376

377
            $paramTypeCheck = [
378 3
                'integer' => $this->db::PARAM_INT,
379 3
                'boolean' => $this->db::PARAM_BOOL,
380 3
                'NULL'    => $this->db::PARAM_NULL,
381 3
                'string'  => $this->db::PARAM_STR,
382
            ];
383

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

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

390
                // Solve problem with bigint.
391 3
                if ($v >= 2147483647) {
392 3
                    $pdoParam = $this->db::PARAM_STR;
393
                }
394

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

398 3
            return $query->execute();
399

400
            // @codeCoverageIgnoreStart
401
        } catch (Exception $e) {
402
            return false;
403
        }
404
        // @codeCoverageIgnoreEnd
405
    }
406

407
    /**
408
     * Remove a row from a table.
409
     *
410
     * @param string $table The data table name.
411
     * @param array  $where The array creates WHERE clause.
412
     *
413
     * @return bool
414
     */
415 3
    protected function remove(string $table, array $where): bool
416
    {
417

418 3
        $placeholder = [];
419 3
        foreach ($where as $k => $v) {
420 3
            $placeholder[] = "`$k` = :$k";
421
        }
422

423 3
        $dataPlaceholder = implode(' AND ', $placeholder);
424

425
        try {
426

427
            $paramTypeCheck = [
428 3
                'integer' => $this->db::PARAM_INT,
429 3
                'boolean' => $this->db::PARAM_BOOL,
430 3
                'NULL'    => $this->db::PARAM_NULL,
431 3
                'string'  => $this->db::PARAM_STR,
432
            ];
433

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

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

440 3
                $query->bindValue(":$k", $v, $pdoParam);
441
            }
442

443 3
            return $query->execute();
444
            // @codeCoverageIgnoreStart
445

446
        } catch (Exception $e) {
447
            return false;
448
        }
449
        // @codeCoverageIgnoreEnd
450
    }
451

452
    /**
453
     * Create SQL tables that Shieldon needs.
454
     *
455
     * @return bool
456
     */
457 3
    protected function installSql(): bool
458
    {
459
        try {
460

461
            $sql = "
462 3
                CREATE TABLE IF NOT EXISTS `{$this->tableFilterLogs}` (
463
                    `log_ip` varchar(46) NOT NULL,
464
                    `log_data` blob,
465
                    PRIMARY KEY (`log_ip`)
466 3
                ) ENGINE={$this->tableDbEngine} DEFAULT CHARSET=latin1;
467
            ";
468

469 3
            $this->db->query($sql);
470

471
            $sql = "
472 3
                CREATE TABLE IF NOT EXISTS `{$this->tableRuleList}` (
473
                    `log_ip` varchar(46) NOT NULL,
474
                    `ip_resolve` varchar(255) NOT NULL,
475
                    `type` tinyint(3) UNSIGNED NOT NULL,
476
                    `reason` tinyint(3) UNSIGNED NOT NULL,
477
                    `time` int(10) UNSIGNED NOT NULL,
478
                    `attempts` int(10) UNSIGNED DEFAULT 0,
479
                    PRIMARY KEY (`log_ip`)
480 3
                ) ENGINE={$this->tableDbEngine} DEFAULT CHARSET=latin1;
481
            ";
482

483 3
            $this->db->query($sql);
484

485
            $sql = "
486 3
                CREATE TABLE `{$this->tableSessions}` (
487
                    `id` varchar(40) NOT NULL,
488
                    `ip` varchar(46) NOT NULL,
489
                    `time` int(10) UNSIGNED NOT NULL,
490
                    `microtimestamp` bigint(20) UNSIGNED NOT NULL,
491
                    `data` blob,
492
                    PRIMARY KEY (`id`)
493 3
                ) ENGINE={$this->tableDbEngine} DEFAULT CHARSET=latin1;
494
            ";
495

496 3
            $this->db->query($sql);
497

498
            // @codeCoverageIgnoreStart
499
        } catch (Exception $e) {
500
            return false;
501
        }
502
        // @codeCoverageIgnoreEnd
503

504 3
        return true;
505
    }
506

507
    /**
508
     * Clean all records in IP log and IP rule tables, and then rebuild new tables.
509
     *
510
     * @return bool
511
     */
512 3
    protected function rebuildSql(): bool
513
    {
514
        try {
515 3
            $sql = "DROP TABLE IF EXISTS `{$this->tableFilterLogs}`";
516 3
            $this->db->query($sql);
517 3
            $sql = "DROP TABLE IF EXISTS `{$this->tableRuleList}`";
518 3
            $this->db->query($sql);
519 3
            $sql = "DROP TABLE IF EXISTS `{$this->tableSessions}`";
520 3
            $this->db->query($sql);
521

522 3
            $this->installSql();
523

524
            // @codeCoverageIgnoreStart
525
        } catch (Exception $e) {
526
            return false;
527
        }
528
        // @codeCoverageIgnoreEnd
529

530 3
        return true;
531
    }
532

533
    /**
534
     * Check required tables exist or not.
535
     *
536
     * @return bool
537
     */
538 3
    protected function checkTableExists(): bool
539
    {
540 3
        $checkLogTable = $this->db->query("SHOW TABLES LIKE '{$this->tableFilterLogs}'");
541

542 3
        if ($checkLogTable) {
543 3
            if ($checkLogTable->rowCount() > 0) {
544 3
                return true;
545
            }
546
        }
547

548 3
        return false;
549
    }
550
}

Read our documentation on viewing source code .

Loading