routerDb
One interface for working with all databases
Simple and clear code
use Pllano\RouterDb\Router as RouterDb;
// Table (resource)
$table = "user";
// Adapter: Pdo, Apis, ZendDb, DoctrineDbal, NetteDb (Default: Pdo)
$routerDb = new RouterDb($config, 'Pdo');
// Ping the available database for the resource
$db = $routerDb->run($routerDb->ping($table));
// Or indicate the base, without ping
// $db = $routerDb->run("mysql");
// Array for the query
$query = [];
$id = 1;
// Get user data id = 1 from mysql database
$data = $db->get($table, $query, $id);
```php
// The same in one line
$data = ((new \Pllano\RouterDb\Router($config, 'Pdo'))->run("mysql"))->get("user", [], 1);, (*1)
```php
// More readable code
use Pllano\RouterDb\Router as RouterDb;
$routerDb = new RouterDb($config, 'Pdo');
$data = ($routerDb->run("mysql"))->get("user", [], 1);
```php
use Pllano\RouterDb\Router as RouterDb;
$routerDb = new RouterDb($config, 'Pdo');
// To connect to the second mysql_duo database, you need to pass in the third parameter the prefix duo
$db = $routerDb->run('mysql', [], 'duo');
$data = $db->get($table, $query, $id);, (*2)
## Types of requests
```php
$post = $db->post($table, $query, $field_id);
$get = $db->get($table, $query, $id, $field_id);
$put = $db->put($table, $query, $id, $field_id);
$del = $db->del($table, $query, $id, $field_id);
$count = $db->count($table, $query, $id, $field_id);
$last_id = $db->last_id($table);
// Exclusive method
$data = $db->pdo($sql)->fetchAll(); // $db->prepare($sql)->execute()->fetchAll();
$data = $db->pdo($sql, $params)->fetchAll(); // $db->prepare($sql)->execute($params)->fetchAll();
// In style PDO
$data = $db->prepare($sql)->execute($params)->fetch();
$data = $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
```php
use Pllano\RouterDb\Router as RouterDb;
$routerDb = new RouterDb($config, 'Pdo');
$db = $routerDb->run('mysql');
$data = $db->pdo("SELECT * FROM users WHERE user_id=?", [$user_id])->fetchAll();
// or
$data = $db->prepare($sql)->execute($params)->fetch();, (*3)
In style Slim-PDO
```php
// https://github.com/FaaPz/Slim-PDO/blob/master/docs/README.md
// SELECT * FROM users WHERE id = ?
$selectStatement = $db->select()
->from('users')
->where('id', '=', 1234);
$stmt = $selectStatement->execute();
$data = $stmt->fetch();
// INSERT INTO users ( id , usr , pwd ) VALUES ( ? , ? , ? )
$insertStatement = $db->insert(['id', 'usr', 'pwd'])
->into('users')
->values([1234, 'your_username', 'your_password']);
$insertId = $insertStatement->execute(false);
// UPDATE users SET pwd = ? WHERE id = ?
$updateStatement = $db->update(['pwd' => 'your_new_password'])
->table('users')
->where('id', '=', 1234);
$affectedRows = $updateStatement->execute();
// DELETE FROM users WHERE id = ?
$deleteStatement = $db->delete()
->from('users')
->where('id', '=', 1234);
$affectedRows = $deleteStatement->execute();
```php
public function post(string $resource = null, array $query = [], string $field_id = null): int {}
public function last_id(string $resource = null): int {}
public function get(string $resource = null, array $query = [], int $field_id = null, string $field_id = null): array {}
public function put(string $resource = null, array $query = [], int $field_id = null, string $field_id = null): int {}
public function del(string $resource = null, array $query = [], int $field_id = null, string $field_id = null): int {}
public function count(string $resource = null, array $query = [], int $field_id = null, string $field_id = null): int {}, (*4)
```php
// Configuration
$config = [
"db" => [
"master" => "mysql",
"slave" => "elasticsearch",
"mysql" => [
"host" => "localhost",
"dbname" => "",
"port" => "",
"charset" => "utf8",
"connect_timeout" => "15",
"user" => "",
"password" => ""
],
"mysql_duo" => [
"host" => "localhost",
"dbname" => "",
"port" => "",
"charset" => "utf8",
"connect_timeout" => "15",
"user" => "",
"password" => ""
]
],
"resource" => [
"user" => [
"db" => "mysql"
],
"article" => [
"db" => "elasticsearch"
],
"price" => [
"db" => "api"
]
]
];
Installation
Use Composer, (*5)
"require" {
...
- "pllano/router-db": "1.1.*",
+ "pllano/router-db": "1.2.0",
...
}
Use AutoRequire, (*6)
"require" [
{
"namespace": "Pllano\\RouterDb",
"dir": "/pllano/router-db/src",
"link": "https://github.com/pllano/router-db/archive/master.zip",
"name": "router-db",
"version": "master",
"vendor": "pllano"
}
]
Protection against SQL injections
Example injection
An SQL injection against which prepared statements won't help, (*7)
Method 1 (Can help in 99% of cases.)
Check the existence of the key in the table & Search for keywords, (*8)
We plan to embed this method in the function, (*9)
$post = $db->post($table, $query, $field_id);
```php
use Pllano\RouterDb\Utility;
use Pllano\RouterDb\Router as RouterDb;, (*10)
$utility = new Utility();
$uri = $_SERVER['REQUEST_SCHEME'] . '://' . $_SERVER['HTTP_HOST'].$_SERVER['REQUEST_URI'];
$host = $_SERVER['HTTP_HOST'];
$escaped_url = htmlspecialchars($uri, ENT_QUOTES, 'UTF-8');
$inj = 'sql_injection';, (*11)
$routerDb = new RouterDb($config, 'Pdo');
$routerDb->setLogger($this->logger);
$routerDb->setMailer($this->mailer);
$db = $routerDb->run('mysql');
$table = 'users';
// The name of the table that we want the structure of.
// Get The Structure Of A MySQL Table In PHP (PDO).
// Query MySQL with the PDO objecy.
// The SQL statement is: DESCRIBE [INSERT TABLE NAME]
// Fetch our result.
$fieldMap = $db->fieldMap($table);
// The result should be an array of arrays,
// with each array containing information about the columns
// that the table has.
// var_dump($result);
$table_schema = [];
foreach($fieldMap as $column){
$field = $column['Field'];
$field_type = $column['Type'];
$table_schema[$field] = $field_type;
}
// Or determine the list yourself
// $table_schema = array_flip(["id", "user_id", "name", "surname", "email", "phone"]);
// Or
// $table_schema = array_flip(explode(",", "id,user_id,name,surname,email,phone"));, (*12)
$params = [];
$setStr = "";
$x = 2; // If search_injections finds $x keywords from the list
foreach ($_POST as $key => $value)
{
if (array_key_exists($key, $table_schema)) {
if ($utility->search_injections($value) >= $x) {
// Write to the log. A letter to the administrator.
$db->logger->info($inj, [
"key" => $key,
"value" => $value,
"url" => $escaped_url,
"request" => [$request]
]);
$db->mailer->setFrom(['attention@'.$host => 'Attention SQL injection'])
->setTo(['admin@'.$host => 'Admin'])
->setBody('Attention SQL injection: '.$uri);
return $inj; // Stop Execution
} else {
if ($key != "id") {
$setStr .= "".str_replace("", "``", $key)."` = :".$key.",";
}
$params[$key] = filter_var($value, FILTER_SANITIZE_STRING);
}
} else {
if ($utility->search_injections($key) >= 1 || $utility->search_injections($value) >= 1) {
// Write to the log. A letter to the administrator.
$db->logger->info($inj, [
"key" => $key,
"value" => $value,
"url" => $escaped_url,
"request" => [$request]
]);
$db->mailer->setFrom(['attention@'.$host => 'Attention SQL injection'])
->setTo(['admin@'.$host => 'Admin'])
->setBody('Attention SQL injection: '.$uri);
return $inj; // Stop Execution
}
}
}, (*13)
if (isset($_POST['id']) ?? is_int($_POST['id'])) {
$params['id'] = intval($_POST['id']);
$setStr = rtrim($setStr, ",");
$db->prepare("UPDATE $table SET $setStr WHERE id = :id")->execute($params);
}, (*14)
### function search_injections()
Very simple function
``` php
public function search_injections(string $value = null, array $add_keywords = [], array $new_keywords = []): int
{
$list_keywords = [];
if (isset($value)) {
if (isset($new_keywords)) {
$list_keywords = $new_keywords;
} else {
$plus_keywords = [];
if (isset($add_keywords)) {
$plus_keywords = $add_keywords;
}
$list_keywords = [
'*',
'SELECT',
'UPDATE',
'DELETE',
'INSERT',
'INTO',
'VALUES',
'FROM',
'LEFT',
'JOIN',
'WHERE',
'LIMIT',
'ORDER BY',
'AND',
'OR ',
'DESC',
'ASC',
'ON',
'LOAD_FILE',
'GROUP',
'BY',
'foreach',
'echo',
'script',
'javascript',
'public',
'function',
'admin',
'root',
'push',
'"false"',
'"true"',
'return',
'onclick'
];
$keywords = array_replace_recursive($list_keywords, $plus_keywords);
}
$value = str_ireplace($keywords, "👌", $value, $i);
return $i;
} else {
return 0;
}
}
, (*15)
Support, feedback, news
Contact: open.source@pllano.com, (*16)
License
The MIT License (MIT). Please see LICENSE for more information., (*17)
Wallogit.com
