C# MySql.Data.MySqlClient Create database failed

Question

I wanted to create a new database using the MySql.Data.MySqlClient with prepared statements. Unfortunately it shows me an error message "You have an error with your SQL syntax". Using "test" for the iv_name import value of the method.

When executing "CREATE DATABASE IF NOT EXISTS test;" directly in mysql server console, it works without any problems. When adding "'" to left and right of the @dbname a database will be created called @dbname in my mysql server.

public void CreateDatabase(string iv_name)
    {
        MySqlCommand lo_cmd = new MySqlCommand("CREATE DATABASE IF NOT EXISTS @dbname;", this._conn);
        lo_cmd.Prepare();
        lo_cmd.Parameters.AddWithValue("@dbname", iv_name);
        lo_cmd.ExecuteNonQuery();
    }

Here you can find a screen shot from the debuggin process

error while creating a new database - debugger result

O. Jones · Accepted Answer · 2019-10-31 09:12:59Z

1

You cannot use parameters in data definition SQL statements like CREATE TABLE. Use string concatenation to create statements like that.

Be very careful with user-provided table names. Avoid punctuation and avoid reserved words like ‘select‘ and ‘table'. Your best bet is to reject any user input containing punctuation other than _, and to prefix user input with something like t_. So you reject table;droptable and turn mytable into t_mytable.

edited Oct 31, 2019 at 9:12

answered Oct 31, 2019 at 8:31

O. Jones

109k17 gold badges134 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

vuenoob Over a year ago

When using string concatenation, is there a method to check or convert the iv_name value for avoiding mysql injections? Or do i have manually check it by iv_name.Contains(" ' "); for example?

Collectives™ on Stack Overflow

C# MySql.Data.MySqlClient Create database failed

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related