incorrect syntax in c# sql command

Question

I've got the following

SqlCommand cmd = getSQLCommand();
using (cmd.Connection)
using (cmd)
{
    try
    {
        string dbName = txt_DatabaseName.Text;
        var createDatabaseQuery = "exec ('CREATE DATABASE ' + @databaseName)";
        var sqlCommand = new SqlCommand(createDatabaseQuery, cmd.Connection);
        sqlCommand.Parameters.Add("@databaseName", SqlDbType.Text);
        sqlCommand.Parameters["@databaseName"].Value = dbName.ToString();

        cmd.Connection.Open();

        sqlCommand.ExecuteNonQuery();
    }
    catch (SqlException ex)
    {
        Console.WriteLine(ex.ToString());
        ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage", "alert('SQL Error. Record not added.')", true);
    }
    finally
    {
        cmd.Connection.Close();
    }
}

I'm fully aware that params are not supported in DDL operations, so I've got this really cool thread that I've been using to help me write the contents within the "try". How to use SqlCommand to CREATE DATABASE with parameterized db name?

That said, I'm still getting an exception error for incorrect syntax near 'Database'. This might be a user error but I've been stuck around this for an hour or so now.

Any thoughts/ improvements?

p.s. All I'm trying to do is to create a database programmatically by using a dynamic value of whatever happens to be in txt_DatabaseName.Text (and yes I will try to error handle this in case there's white spaces entered or any chars that are not supported in SQL.

p.p.s Any articles that I can have a look at against sql injection attacks or any suggestions around constructing the method I have to prevent it? This is a simple exercise that I'm doing on my local machine, not public facing but still would like to get ahead of the game if possible.

Why you use exec and not simply "CREATE DATABASE " + dbName.ToString()? — Tim Schmelter
– Tim Schmelter, Commented Jul 18, 2017 at 12:09
but if I do something simple "CREATE DATABASE TEST" it works just fine.. it's the syntax of the "CREATE DATABASE " + dbName.ToString(); that is doesn't like — MrDedupe
– MrDedupe, Commented Jul 18, 2017 at 12:13
@TimSchmelter let me introduce you to SQL Injection. (Albeit as it is being substituted into a string parametrisation will not help here: the value needs to be carefully checked, however given the number of concatenations seen around here any – however futile – attempt at parametrisation should be seen positively) — Richard
– Richard, Commented Jul 18, 2017 at 12:14

Rahul · Accepted Answer · 2017-07-18 12:21:23Z

0

You don't need the exec part at all. Again you are getting error after removing exec cause you are wrapping your query in single quote 'CREATE DATABASE ' which is getting considered as string literal. It should just be

var createDatabaseQuery = string.Format("CREATE DATABASE {0}",DBnamevariable);
var sqlCommand = new SqlCommand(createDatabaseQuery, cmd.Connection);

edited Jul 18, 2017 at 12:21

answered Jul 18, 2017 at 12:19

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

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

5 Comments

Panagiotis Kanavos Over a year ago

I'm pretty certain that you can't use a parameter with CREATE DATABASE. Tha's DDL, not DML

Rahul Over a year ago

@PanagiotisKanavos, real embarrassing ... thanks for pointing that. how I missed that !!!

Panagiotis Kanavos Over a year ago

Unless the OP performs some rigorous validation of DBnamevariable this will expose the server to SQL injection attacks

MrDedupe Over a year ago

hmm very interesting! we have a different error var createDatabaseQuery = "CREATE DATABASE @databaseName)"; so it actually recognizes the Create Database command

Panagiotis Kanavos Over a year ago

@MrDedupe what is the value of the parameter? You are still concatenating strings. Any weird values, spaces, dots, etc will result in an invalid statement. Add a ; and you will execute two statements

Jordy van Eijk · Accepted Answer · 2017-07-18 12:12:11Z

0

Take a look with the sql profiler to see what is being fired against the database. If it is not working try to execute the query in Management studio to see it that is working. It's probably some kind of special character that is not allowed.

answered Jul 18, 2017 at 12:12

Jordy van Eijk

2,7862 gold badges20 silver badges37 bronze badges

4 Comments

MrDedupe Over a year ago

No need for the profiler, If i enter something simple like "CREATE DATABASE TEST" it works, it just doesn't understand the syntax if we do it with a variable rather than a hardcoded text. The catch method gives us the following error during debug ex = {"Incorrect syntax near 'DATABASE'."}

Jordy van Eijk Over a year ago

@MrDedupe who says it works? If you take a look look at the profiler you can see what your code is generating and firing against the database. Maybe it is doing something else that you didn't expect. its clearly not CREATE DATABASE TEST

MrDedupe Over a year ago

it works because it creates the database called TEST under the instance of my SQL that the cmd had all the connection strings established.

Jordy van Eijk Over a year ago

So you are saying you execute your C# code it creates a database but it returns incorrect syntax near database, am i understanding that?

Collectives™ on Stack Overflow

incorrect syntax in c# sql command

2 Answers 2

5 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related