How To Build A Parameterized Query Or User Escaping In This Sql Statement In C#?

command.CommandText = String.Format('CREATE LOGIN {0} WITH password='{1}'', loginName, password); loginName and password are based on some user input I realize that it's bad pract

Solution 1:

Call sp_addlogin instead - it's already parameterized

Solution 2:

Here's how to parameterize your SQL. You may also want to check out this article on writing a DAO that handles this type of thing. I'm not sure if you can parameterize the LoginName. You're probably best off calling sp_addlogin like the previous poster said.

       command.CommandText=  @"CREATE LOGIN @LoginName WITH password=@Password";
        command.CommandType = CommandType.Text;
        command.Parameters.Add(new SqlParameter()
            {
                ParameterName = "@LoginName",
                Value = "MyLoginNameValue",
                SqlDbType = SqlDbType.NVarChar,
                Size = 50
            });
        command.Parameters.Add(new SqlParameter()
            {
                ParameterName = "@Password",
                Value = "MyPasswordValue",
                SqlDbType = SqlDbType.NVarChar,
                Size = 50
            });

Solution 3:

It seems like the most right way to do it is to use SQL Server SMO SDK. I don't care of any other SQL engines since we'll never move from SQL Server for sure.

Solution 4:

You can parameterize such queries by wrapping your DDL query in an exec as follows:

command.CommandText = "exec ('CREATE DATABASE ' + @DB)"

If you then add the parameter for @DB as usual this should work (in t-sql at least).

It should be possible to use CREATE LOGIN in the same fashion.

Solution 5:

Can try something like this:

SqlCommandcmd=newSqlCommand(
                "CREATE LOGIN @login WITH password=@pwd", conn);
SqlParameterparam=newSqlParameter();
param.ParameterName = "@login ";
param.Value         = usertextforlogin; 
cmd.Parameters.Add(param);

param  = newSqlParameter();
param.ParameterName = "@pwd";
param.Value         = usertextforpwd;   

cmd.Parameters.Add(param);