Support the ongoing development of Laravel.io →
Database Laravel
Last updated 1 year ago.
0
$sNames = filter_var($sName, FILTER_SANITIZE_STRING);

sanitize before use?

0

Are you sanitize input? if yes and still has problem then this problem because of MySQL Naming Rules.It restricate some name may be they are reserved keyword or any other problem.

these are simple rules

Cannot contain / \ or . characters
Cannot contain characters that are not permitted in file names.
Cannot end with space characters

see here for more sql naming Rules.
http://www.informit.com/articles/article.aspx?p=30875
0

I've attempted multiple replacements. I'm pretty sure it has nothing to do with reserved keywords. If I use the same name it works in this line:

DB::connection()->statement("CREATE DATABASE {$sName}")

but not in this:

DB::connection()->statement("CREATE DATABASE :sName", ['sName' => $sName]);

So I doubt it has to do with reserved keywords.

I've considered using a sanitize filter as a stop gap but wanted to see if there was something I could do to get the PDO replacement working.

0

To my knowledge, DDL [1] statements usually don't support bound parameters, only DML [2].

Although under the hood Laravel is using PDO, the decription from mysqli_stmt_prepare seems to fit:

In general, parameters are legal only in Data Manipulation Language (DML) statements, and not in Data Definition Language (DDL) statements.

Now, I know that PDO supports emulated and native prepared statements. But a) I don't know which driver supports what exactly and b) if these restriction still apply. According to your experience, it does.

My TL;DR would be: performing sanitization on your own is the way to go.

[1] Data Definition Language

[2] Data Manipulation Language

0

Thanks guys. I've solved the problem by creating a stored procedure in my master DB that takes bound parameters to create the tenant DBs. This circumvents the need for custom sanitization.

CREATE DEFINER=`user`@`localhost` PROCEDURE `tenantCreator`(IN dbname VARCHAR(64), IN dbpass VARCHAR(64))
BEGIN
    SET @db = dbname;
    SET @dbuser = dbname;
    SET @dbpass = dbpass;
    SET @statement = CONCAT('CREATE DATABASE ',@db);
    PREPARE prepared_statement FROM @statement;
    EXECUTE prepared_statement;

    SET @usercreate = CONCAT(CREATE USER ', @dbuser, '@'localhost IDENTIFIED BY ', @dbpass);
    PREPARE create_stmnt FROM @usercreate;
    EXECUTE create_stmnt;
    SET @permissions = CONCAT('GRANT ALL ON ' , @db, '.* TO ', @dbuser, '@'localhost');
    PREPARE permission_stmnt FROM @permissions;
    EXECUTE permission_stmnt;
END               

Then I call it:

$sql = "CALL tenantCreator(:sName, :pass)";
$params = ['sName' => $sName, 'pass' => $pass];

return DB::connection()->statement($sql, $params);
Last updated 6 years ago.
0

Sign in to participate in this thread!

Eventy

Your banner here too?

Todd Roper Todd Roper toddroper Joined 26 Jun 2017

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.