Reading Input Parameter Values in MariaDB Stored Procedures

mariadbparameterstored-procedures

I'm using the current version of MariaDB 10.0.12 .

I created a stored procedure as specified in the accepted answer for the following question:

https://stackoverflow.com/questions/2950054/let-mysql-users-create-databases-but-allow-access-to-only-their-own-databases

In order to do that, I logged in as root using the mysql-client and typed in the following commands:

-> create user 'myUser'@'localhost';
-> create database myStoredProcedures;
-> grant execute on myStoredProcedures.* to 'myUser'@'localhost';
-> use database myStoredProcedures;
-> delimiter //
-> CREATE
->   DEFINER = CURRENT_USER
->   PROCEDURE myuser_create_db (IN dbName VARCHAR(255))
->   SQL SECURITY DEFINER
->   BEGIN
->     CREATE DATABASE dbName;
->     GRANT ALL PRIVILEGES ON dbName.* TO 'myUser'@'localhost';
->   END;
-> //
-> delimiter ;

If I log in as myUser@localhost, use the database myStoredProcedures, and then call the stored procedure:

call myuser_create_db('testit');

The database dbName is created and I can drop it as myUser. However the database' name is literally 'dbName' and not 'testit'! Why is this the case and how can I fix this?

( And now that I think about it … would it be possible to use sql injection here? Is it possible to call myuser_create_db with a parameter that contains sql commands within the new db name? That would be bad. But I cannot test this sql injection idea now, because MariaDB interprets dbName as the string 'dbName'. )

Thanks.

Best Answer

Look here for a possible answer to this question.

https://dba.stackexchange.com/questions/73931/prevent-sql-injection-inside-stored-procedure

But the answer there is also a follow-up question.