Creating new databases from potentially unsafe input via PDO and mysql

Question

I'm working on automating the deployment of a piece of software I've written and need to be able to generate new mysql databases for new accounts. However, I'm having concerns when it comes to sanitizing the input.

I'm using PDO; however, apparently you can't use prepared statements with 'CREATE DATABASE'. So, I also tried using PDO::quote; however, then my newly created databases names are surrounded by single quotes (not the end of the world, but I'd still like to avoid that).

Is there any way to get this to work with prepared statements? If not, what can I do to protect myself as much as possible from sql injection attacks? My only other idea is to have a small whitelist of characters allowed.

Thanks!

Answer 1

you'll need to write a stored procedure to which you can then pass the sanitized input (to use my example you'll need to change some variables like the database name and the correct user and pass and such, to make it run on your database of course)

example:

<?php
$dsn = 'mysql:dbname=scratch;host=127.0.0.1';
$user = 'root';
$password = '';

try {
    $dbh = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

$dbname = 'brand_new_db';

$statement = $dbh->prepare("CALL dbcreator(:db)");
$statement->bindParam(':db',$dbname);

if(!$statement->execute()){
    print_r($statement->errorInfo());
}
else {
    foreach( $dbh->query('SHOW DATABASES')->fetchAll() as $row){
        print "$row[0]" . PHP_EOL;
    }
}

stored procedure:

DELIMITER $$

DROP PROCEDURE IF EXISTS `scratch`.`dbcreator` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `dbcreator`(IN dbname VARCHAR(64))
BEGIN

SET @db = dbname;
SET @statement = CONCAT('CREATE DATABASE ',@db);
PREPARE prepared_statement FROM @statement;
EXECUTE prepared_statement;

END $$

DELIMITER ;

You create an SQL statement to PREPARE, in our case CREATE DATABASE <our database>; since the CREATE DATABASE statement will not work with a variable, and then you just EXECUTE it. Finally you do a CALL dbcreator('<dbname>') to execute the stored procedure. It's that CALL dbcreator(:dbname), that you can use and bind params to.

As you can see, this way you can still bind your params safely through pdo while creating the database. Still it might not be a bad idea to prefix all database you create with some short fixed string for easy lookup and discrimination. In the stored procedure dbname is limited to 64 characters since that's mysql's current limit