Support the ongoing development of Laravel.io →
Security Database
Last updated 1 year ago.
0

@robjt, you are correct that PDO does not allow you to bind parameters with a create database query; this is an error in the original post. The issue you raise is with security, and the most common thing I have seen is to create a whitelist of allowed database names. But this is not always possible.

What I have done, which I mentioned in the article you linked to is to generate a database name according to some rule set. The one I have chosen, and one that has worked well for me on various projects (although I am sure there are better solutions) is to generate the database name after hashing a given value. Let's assume we are using the account ID as the basis for our database name (it can be anything, as long as it is unique for each account). For example, the md5 hash of 1 is c4ca4238a0b923820dcc509a6f75849b. Most database systems do not like numerical characters as part of the database name, so we need to remove them.

Not only do we need to remove them, we need to remove them consistently every single time that this operation is done. We can create a function like so to convert a hash to a string containing no numerical characters:

/**
 * Removes all occurrences of numerical characters
 * from an MD5 string.
 * 
 * @param  string $hash
 * @return string
 */
function convertMd5ToAlphaChars($hash) {
	return strtr($hash, range('a', 'j'));
}

At this point, we can use the function to get a database name like so:

// This would result in the string
// db_cecaecdiaabjcdicadccfajagfhfiejb
// being created and assigned to the $dbName variable.
$dbName = 'db_'.convertMd5ToAlphaChars(md5(1));

So the number 1 maps to the database name db_cecaecdiaabjcdicadccfajagfhfiejb. While it is not the most pleasing solution in terms of readability, it does save a lot of time with validation.

Using this approach, the createSchema method would now look like this:

public static function createSchema($schemaName)
{
        $dbName = 'db_'.convertMd5ToAlphaChars(md5($schemaName));

        return DB::getSchemaBuilder()
            ->getConnection()
            ->statement("CREATE DATABASE ".$dbName);
}

To see why this works, we can look at the following example:

$potentiallyBadShemaName = "test; some bad query to remove all databases //";

createSchema($potentiallyBadShemaName);

Instead of the database engine running the following query:

CREATE DATABASE test; some bad query to remove all databases //

It would run this query:

CREATE DATABASE db_gbeajabhacdfieabbbdefaagihdjhfje;

Hope this helps you accomplish your goals! Just make sure that whatever approach you use, make sure that the connection builder and schema builder are using the same methods for creating the database name.

Last updated 8 years ago.
0

Sign in to participate in this thread!

Eventy

Your banner here too?

robjt robjt Joined 7 Aug 2015

Moderators

We'd like to thank these amazing companies for supporting us

Your logo here?

Laravel.io

The Laravel portal for problem solving, knowledge sharing and community building.

© 2024 Laravel.io - All rights reserved.