Correct way to use different databases in a web platform? [closed]

Question

Correct way to use different databases in a web platform? [closed]

Navigation

#1 by (6 votes)
#2 by (3 votes)

3

I am creating a web platform, which can be accessed by different workers' organizations.

When a worker wants to enter the platform, he must select which organization he belongs to, then put his mail and password, as shown in the following entry form:

The field "Organization:" sends by POST the name of the database to my file validar.php, which connects to that database to then start the session and the session variables.

The following code does what has just been described, but to guide you in the process, just look at the last session variable I am starting:

validar.php

<?php 

try {
    $ndb = $_POST['dbname'];
    $params = 'mysql:host=localhost;dbname='.$ndb.';charset=utf8';
    $db = new PDO($params, 'root', '');
} catch (Exception $e) {
        die('Error: ' . $e->getMessage());
}

if (!isset($_SESSION)) {
  session_start();
}

$email= $_POST['email'];
$contrasena= $_POST['contrasena'];
$select = $db->prepare("SELECT * FROM 'empleados' WHERE email = :email AND clave = :contrasena");
$select->bindValue(':contrasena',$contrasena,PDO::PARAM_STR);       //PDO::PARAM_INT para enteros
$select->bindValue(':email',$email,PDO::PARAM_STR);
$select->execute();

    if($select->rowCount()>0){
        $fila = $select->fetch(PDO::FETCH_ASSOC);
        $_SESSION['rut'] = htmlentities($fila['rut']);                  
        $_SESSION['nombre1'] = htmlentities($fila['nombre1']);
        $_SESSION['nombre2'] = htmlentities($fila['nombre2']);
        $_SESSION['nombre3'] = htmlentities($fila['nombre3']);
        $_SESSION['apellido1'] = htmlentities($fila['apellido1']);
        $_SESSION['apellido2'] = htmlentities($fila['apellido2']);
        $_SESSION['edad'] = htmlentities($fila['edad']);
        $_SESSION['email'] = htmlentities($fila['email']);
        $_SESSION['rango'] = htmlentities($fila['rango']);
        $_SESSION['clave'] = htmlentities($fila['clave']);
        $_SESSION['database'] = $ndb;
        header("Location: principal.php");
    }
    else{
        echo '<script language = javascript>
        alert("email y/o Password incorrecta.");
        self.location = "index.html";
        </script>';
    }
?>

$_SESSION['database'] contains the name of the database, so for all my .php files that contain sql queries, I am using the following file to connect to the correct database:

conex.php

<?php
try {
    $ndb = $_SESSION['database'];
    $params = 'mysql:host=localhost;dbname='.$ndb.';charset=utf8';
    $db = new PDO($params, 'root', '');
} catch (PDOException $e) {
        die('Error: ' . $e->getMessage());
}
?>

In all those files that contain sql queries, I start / verify session and then I include this file "conex.php".

This works perfectly, without errors, but my question is:

Is this an appropriate / correct / efficient way to create this type of multi-user "multi-organization" platforms? Why?
Is it insecure? Why?

php

asked by Roberto Sepúlveda Bravo 16.11.2016 в 14:25

source

2 answers

3

What I would do to not expose the name of the databases (right click the form, inspect element, check the name of the inputs, you will see the names of the databases of each organization) is:

Instead of the name of the database, let it be the name of the organization.

Upon receiving the Post, first validate the organization to decide in which database the user belongs.

This way "info" is already "hidden". The less info about the internal functioning of your platform, the better.

To apply this you already decide if you want to do it in code (with a function or a method of a class) or by means of a general configuration table, where you can place the names of the institutions, the name of the DB that they correspond, and other validations that you want to do, like the Ip from which they can access (from one country to another, because the Ip can be differentiated by the country) and thus avoid that an external user (Alaska for example), try to access; and what you want to add later so that it is scalable.

Greetings.

answered by 17.11.2016 в 18:19

How is the usign (MySqlConnection) C # used? Place text in html table cell vertically?

score 6 · Accepted Answer

I want to explain some advantages that I see in using multiple databases for the same application.

With a single database, you would have to replicate the id of the organization throughout the database. Think of the Master, configuration, or specific tables for each organization. All of them would have to have their ID (for example those that fill the combos can be generic options for each organization)

Customers will be reassured that their database is isolated from those of other organizations . If there is sensitive information that they want to protect, they would not like others to see it.

Allows you to manage backups independently . But how would you do? With multiple databases, an organization can have its own backup.

Allow your application to be scalable . You could have several databases on one server, and others, with higher traffic, on another.

Allow your application to be more secure . A code error will never expose the data between organizations. And if a database stops working for some reason, it will only affect the specific client.

The truth is that I could continue with more reasons.

If you are interested in investigating a little more, what StackOverflow's CEO explains in this link is interesting link from minute 50:45 onwards.