Geek asks and answers

Creating new databases from potentially unsafe input via PDO and mysql

I am working on automating the deployment of a piece of software that I wrote, and should be able to generate new mysql databases for new accounts. However, I am having problems when it comes to disinfecting input.

I use PDO; however, apparently, you cannot use prepared statements with 'CREATE DATABASE'. So, I also tried using PDO :: quote; however, my newly created database names are surrounded by single quotes (not the end of the world, but I would like to avoid this anyway).

Is there a way to get this to work with trained operators? If not, what can I do to protect myself from SQL injection as much as possible? My only idea is to allow the use of a small whitelist.

Thank!

+5
source share
1 answer

you will need to write a stored procedure , after which you can pass the sanitized input (to use my example, you will need to change some variables, such as the database name and the correct user, and pass them so that they run in 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 ;

SQL PREPARE, CREATE DATABASE <our database>;, CREATE DATABASE , EXECUTE. , CALL dbcreator('<dbname>') . , CALL dbcreator(:dbname), params .

, pdo . , , , . dbname 64 , mysql current

+2

More articles:


All Articles