How do I securely create a mysql database in C#

Question

I have a C# program that I want to dynamically create databases with. Although only privileged users will be using this application, I want to follow best practices security wise. How do I go about doing this? I don't think I can use a parameterized query in this case since I'm not wanting to pass in a string, I want to pass in an identifier.

Here's the insecure way I've got in there now as a placeholder:

using (MySqlConnection connection = new MySqlConnection(connectionString))
        using (MySqlCommand command = connection.CreateCommand())
        {
            connection.Open();
            command.CommandText = "DROP SCHEMA IF EXISTS " + schema;
            command.ExecuteNonQuery();
            command.CommandText = "CREATE SCHEMA " + schema;
            command.ExecuteNonQuery();
        }

Any ideas on this?

Answer 1

simply run a regular expression against schema to verify that it is a valid name? (contains no spaces, colons etc). I'm not sure if you can or can't use parameterised queries in this case, but if you do, it will fail creating a database called TRANCATE TABLE users; anyway :)

Answer 2

I don't know why do you want to create databases dynamically, but I think the correct way of doing this is not to generate databases dynamically.

The only exception I can think of is if you were creating your own database management system. If that's the case, maybe you could look at the source code of some open source MySQL database manager, like phpMyAdmin. (I don't know any for .Net.)

Answer 3

You can use the backtick delimiter to ensure the strings are correctly quoted and ensure that whatever is entered by the user is used as a literal identifier.

See: http://dev.mysql.com/doc/refman/5.0/en/identifiers.html and http://stackoverflow.com/questions/261455/using-backticks-around-field-names

That way the command that is passed to the server would look like: "DROP SCHEMA IF EXISTS foo" which will tolerate using reserved words and other incorrect string values as identifiers.