Running User Sql Commands On Dynamic Databases

Question

I am trying to do something with dynamic databases. My requirement is as follows:

-- Need to generate and dynamic databases on the fly.

=> These databases are for users to do whatever they want. However, every database is created with restricted permissions and users cannot go beyond their own database.

-- Need to execute any random user query (or a set of user queries) on that database

=> Given that the users can write any query, I do not know whether the query is going to be a "select" query or a "non-select" query. affected". This is equivalent to "running a mysql script file"

Now, I have written a DatabaseManager class, which does "1" for me, but I am still facing issues in implementing "2". The key problem lies in the following things:

1. I do not know the query executed by the user.

2. I need to handle the query and revert back with appropriate result to the user (this is very important), i.e. -- for a select query, it should return the data requested by the user -- for a non-select query, it should return "no. of rows affected" -- for a composite query (which contains both select and non-select queries), it can return the "no. of rows affected"

3. The user can change the delimiters and write complex stuff such as procedures etc., so I cannot do command level split using semicolon ";" and execute them one by one.

What I tried with Yii is following:

1. I created a transaction for every query (or bunch of queries that I get).

2. Not knowing what the query is, I always execute the "queryAll" on that. If it is a "select based query", then I hope to get the correct result from it. If it succeeds, then I commit the transaction

3. If it is a "non-select" query, I will get an exception here, and I will roll back the transaction. In this case, I will next create a new transaction and then call the "execute" command. Given that this is a non-sql or composite query, I expect the command to run. It should only throw an exception in case of a syntax error or in case of some constraint violation

4. In case "execute" also throws an exception, I roll back this transaction as well, and show the result to the user.

I was expecting my approach 2 to work in this case. However, I see that the transactions are not working properly in case of "create table command". Here, the "queryAll" command gives and exception, but still goes ahead and creates the user table. Now, when I try to run the same command again with "execute", it again gives the exception, because the table already exists. I am surprised that the transaction rollback did not make any difference.

Now, my above approach may be grossly incorrect, and I'll be happy to hear some feedback on what is the right way to approach this problem. For now, here are some key questions that I have:

1. Is it possible to execute a bunch of random sql queries in Yii? More importantly, is it possible to break the queries into individual queries? (I know this is tough problem and a big topic of discussion in stackoverflow itself, but asking the same question none the less)

2. Why is transaction rollback not working?

3. What are the holes in my approach and how can anyone screw up this system?

My apologies for such a long post. However, it was a little difficult to explain the problem in brief. I am attaching the source code for my DatabaseManager class with this post for a detailed reference.

<?php
class DatabaseManager
{
public $db_name;
public $username;
public $password;
const HOST = "localhost"; 
const ROOT = "dummy_root";
const ROOT_PASSWORD = "dummy_root_password";
const DB_PASSWORD_SALT = 'dummy_password_salt';

public function getDbComponent($db_name) {
    //if the component already exists then create it
    if(Yii::app()->hasComponent($db_name)) {
        $component = Yii::app()->getComponent($db_name);
        return $component;
    }

    //if the db component doesn't exist, then create it and return the
    //new component
    try {
        $this->db_name = $db_name;
        $this->username = "dummy_user";
        $this->password = "dummy_password";

        $component = $this->createDbComponent();
        Yii::app()->setComponent($db_name, $component);
    } catch (PDOException $e) {
        die("DB ERROR: ". $e->getMessage());
    }
    return $component;
}

private function createDbComponent() {
    $pdo = new PDO("mysql:host=".self::HOST, self::ROOT, self::ROOT_PASSWORD, array(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,));

    $pdo->exec("CREATE DATABASE IF NOT EXISTS `$this->db_name`;
                GRANT ALL PRIVILEGES ON ".$this->db_name.".* TO '".$this->username."'@'localhost' IDENTIFIED BY '".$this->password."';
                FLUSH PRIVILEGES;")
        or die(print_r($pdo->errorInfo(), true));

    $component = Yii::createComponent(array(
                'class'=>'CDbConnection',
                'connectionString'=>'mysql:host=localhost;dbname='.$this->db_name,
                'emulatePrepare' => true,
                'password'=> $this->password, 
                'username'=> $this->username,
                'charset' => 'utf8',
                'enableParamLogging' => true,
                )
            ); 
    return $component;
}

private function formatErrorMessage($msg) {
    $check_for_prefix = "CDbCommand failed to execute the SQL statement:";
    $result = str_replace($check_for_prefix, "", $msg);
    return $result;
}

private function executeSelectQuery($query, $db_name) {
    //Get the db component on which the query should be executed
    $db = $this->getDbComponent($db_name);
    $command = $db->createCommand($query); 

    $transaction = $db->beginTransaction();
    try {
        $command = $db->createCommand($query); 
        $output = $command->queryAll();
        $output = $command->queryAll();
        $transaction->commit();
    } catch (exception $e) {
        $transaction->rollback();
        //throw back this exception
        //as it should be handled by the higher level code
        throw new Exception($e->getMessage());
    }
    return $output;
}

private function executeNonSelectQuery($query, $db_name) {
    //Get the db component on which the query should be executed
    $db = $this->getDbComponent($db_name);
    $transaction = $db->beginTransaction();
    try {
        $command = $db->createCommand($query); 
        $command->prepare();
        $output = $command->execute();
        $transaction->commit();
    } catch (exception $e) {
        $transaction->rollback();
        //throw back this exception
        //as it should be handled by the higher level code
        throw new Exception($e->getMessage());
    }
    return $output;
}

public function runQuery($code, $db_name) {
    try {
        //we can have two kind of queries -- select or non-select
        //since we don't know the type of query, we should by default try to make a select query
        //if this gives an exception, then there is a possibility that this is a non-select query
        //so now we try doing an "execute" on this
        //if that goes fine, it means that the query was a non-sql query
        //else we show the exception to user
        $db = $this->getDbComponent($db_name);
        try {
            $output = $this->executeSelectQuery($code, $db_name);
            $result['status'] = "success";
            $result['output'] = $output;
        } catch (exception $e) {
            try {
                $output = $this->executeNonSelectQuery($code, $db_name);
                $result['status'] = "success";
                $result['output'] = "Query OK, ".$output." row(s) affected";
            } catch (exception $e) {
                $error = $this->formatErrorMessage($e->getMessage());
                $result['status'] = "failure";
                $result['error'] = $error;
            }
        }
    } catch (exception $e) {
        $result = $e->getMessage();
    }

    return $result;
}

//resets the db
public function resetDbComponent($db_name) {
    //in case of reset simply delete the database
    $result = $this->deleteDbComponent($db_name);
    if(!$this->getDbComponent($db_name)) {
        throw new Exception("Could not create database -- ".$db_name);
    } else {
        $result['status'] = "success";
        $result['output'] = "Database reset to inital state";
    }
}

public function deleteDbComponent($db_name) {
    $pdo = new PDO("mysql:host=".self::HOST, self::ROOT, self::ROOT_PASSWORD);
    try {
        $pdo->exec("DROP DATABASE IF EXISTS `$db_name`;");
        Yii::app()->setComponent($db_name, null);
        $result['status'] = 'failure';
        $result['output'] = "Database ".$db_name." deleted";
    } catch (exception $e) {
        $result['status'] = 'failure';
        $result['output'] = "Database ".$db_name." could not be deleted";
    }
}
}

Regards, Kapil

Answer 1

There are a couple questions here, so let me just summarize what I think you are asking:

1. Why do create table transactions not roll back?
2. Which active record function is correct to process either select or non-select queries?
3. How do I deal with multi-statement queries?

So here are my answers:

1. MySQL does not protect DDL sttatements with transactions. Those are the create, alter, grant, etc. statements. PostgreSQL does, so either you switch to PostgreSQL, or you don't try to protect your DDL with transactions. Given that you don't know, without parsing the user's SQL, it may be that switching to PostgreSQL is you best choice here, but reserve that until you read further answers.

2. It is my opinion that Active Record is not meant for doing what you are asking, and trying to pound your square peg into the round ActiveRecord hole may be very difficult. Instead I would use Yii and ActiveRecord to manage your list of users and databases, but use plain old PHP to manage the user's nteraction with those databases. That would mean that you have two connections, one to your admin database that contains user and database information, and another to the user's database. You could just use the mysql functions to connect, query, and disconnect from the user database.

3. I suggest that it is inherantly insecure to allow multiple statements to be executed in a single query. It may even be that the mysql functions will not allow it. In any case you should not allow it because that gives hackers the ability to perform some of the more insidius SQL injection attacks like changing the statement by sticking a ; delete from table into a field that is used to build an sql statement. Except for defining procedures you should not allow this. That means you need to build at least a rudimentary parser. At least enough to know what statement you are executing. Given that, you could even dynamically know whether transaction support is available, and commit or rollback based on that.

Hope this helps.

Answer 2

I understood from your question you need to build API to your database :

1- users can access it

2- can do some basic and complex sql queries

So you need Restful Api

This article will help you , and you can search more about it on google http://www.9lessons.info/2012/05/create-restful-services-api-in-php.html