SQL Parameters not working? What am I doing wrong here?

[zachattack05]

I can't figure out what I'm doing wrong here.

I want to allow the user to name the database on their own. Can you use parameters in a situation where the command is simple like:

CREATE DATABASE @DatabaseName

I've been trying forever and it just keeps creating a database called @DatabaseName, it just ignores the parameter.

[darkagn]

The problem here is that the @DatabaseName in the command is not an SQL datatype field. You instead need to build your query like so:

public void CreateDatabase(string databaseName)
{
   string query = String.Format("CREATE DATABASE {0}", databaseName);
   // now execute the query using DbCommand or similar class...

}

[zachattack05]

That would work, but would that prevent injection attacks?

[hyperion]

That's indeed a good question, but I would like to ask you the same when you use @DatabaseName? Does this prevent injection attacks?
If it does, how; and can you use the same method to prevent attacks on code suggested by darkagn?

[zachattack05]

I guess the rest of the code should be present.

Yes, using SQL parameters does prevent injection attacks...at least thats what it is advertised as doing.
(MSDN: Sql Injection)
(MSDN: SqlParameter Class)

This code works:

//create the string
string sqlstring = "UPDATE accounts SET @company WHERE id = @id";

//create the command object
SqlCommand cmd = new SqlCommand(sqlstring, connection);

//set the SQL Parameters
cmd.Parameters.Add(new SqlParameter("@id", (object)id.Text));
cmd.Parameters.Add(new SqlParameter("@company", (object)company.Text));

//execute the command
connection.Open();
cmd.ExecuteNonQuery();
connection.Close();

But this does not work:

//create the string
string sqlstring = "CREATE DATABASE @DatabaseName";

//create the command object
SqlCommand cmd = new SqlCommand(sqlstring, connection);

//set the SQL Parameters
cmd.Parameters.Add(new SqlParameter("@DatabaseName", (object)dbName.Text));

//execute the command
connection.Open();
cmd.ExecuteNonQuery();
connection.Close();

[kplcjl]

First off, SQL injection is prevented in parameters because the code is formatted to match the parameter's datatype. This is designed to be passed as a parameter in a stored procedure.

So, you pass it @DBName SYSNAME in the parameter. The value can be:
"YouLose;drop database msdb;drop database master"

That can't be a sql injection because a line in the sproc that says
CREATE DATABASE @DBName
WOULD blow up.

IF you are using dynamic SQL, it is YOUR RESPONSIBILITY to prevent a SQL injection attack. The same as if you are building a dynamic SQL command like the other suggestion made.

So, keep your procedure, but in your C# code make sure the data entered is alphanumeric, spaces or known characters like "_" or "-" and the string is no longer than 254 characters. (It should work up to 256, but anyone who wants a DB that long is up to something. Maybe limit it to 60?)

In your sproc code, execute dynamic SQL:
[SQL]
DECLARE @dynCmd NVARCHAR(300) = 'CREATE DATABASE '
IF EXISTS( SELECT * FROM sys.databases WHERE NAME = @DBName)
RAISERROR('The DB name you picked already exists', 16, 1)
ELSE
EXEC (@dynCmd)
[/SQL]

Don't check in C# and my first example wouldn't cause SQL injection, but there are still ways you could end up with missing DBs if your account is allowed to do so.

[kplcjl]

Oh yea. If your connection account doesn't have authority to create databases, that would explain why the straight dynamic create command doesn't work. Horray, you at least have something set up properly. (Properly set up, your account shouldn't be allowed to do anything BUT call stored procedures and that is because the sproc specifically granted EXEC permission. Hardly anyone properly sets up the security model.)
If you create the stored procedure in an account that does have authority to create DBs, your program would be allowed to create them in the sproc. (With minor exceptions.)

[kplcjl]

I am really surprised that you said "UPDATE accounts SET @company WHERE id = @id" works. I'd believe "UPDATE accounts SET company=@company WHERE id = @id"

Now for your @company string -> "junk';truncate table accounts;--"
This used to work as an attack in the old style because the formatting was
CMD = "UPDATE accounts SET company='" + Str + "' WHERE id = " + id.ToString()

The command SQL would see in the new format is
UPDATE accounts SET company='junk'';truncate table accounts;--' WHERE id = 1234

Even though this really is a dynamic query, you are maintaining the relationship of working with a string in SQL being set to a string in your parameter list so a SQL attack can't happen. I wonder what happens if you violate that principle?

create a table accountx with a company field, add a few records where company is not "junk"
Try "UPDATE @accounts SET company=@company WHERE id = @id"

In your @accounts string "accountx' SET company='junk';--"

I'm betting every row in the table is "junk". If it is, then you proved SQL insertion is possible.
If it isn't, I'm going by memory so interactively try:
UPDATE 'accountx' SET company='junk'
If it blows up, then my memory is screwed up. If not, wow, I'm impressed and really surprised with C#.

[zachattack05]

kplcjl,

Your right. I was a bit tired and frustrated when I wrote that.

The problem was not creating a database, it was that the name of the database was incorrect. It's okay though, I figured it out and everything is working now.

Thanks for the help!

[kplcjl]

This is marked as unsolved. It is your responsibility to post it as solved.

Just wanted to make the point that C# has done a lot of good work to remove SQL insertion but it is possible when dynamic SQL is involved. (After reading what I wrote earlier, I realize that it was wrong. Because of the unbalanced quotes, it wouldn't work as an attack. There still are places where a dynamic SQL command could be attacked.