DB::connection()->statement Fails when using bindings.

replied 7 years ago

$sNames = filter_var($sName, FILTER_SANITIZE_STRING);

sanitize before use?

0

elsnertechnologie

replied 7 years ago

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

toddroper

replied 7 years ago

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

max_well

replied 7 years ago

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

toddroper

replied 7 years ago

Solution

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 7 years ago.

0

Moderators