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 practice to do it int this way but how to avoid sql injections here?
Add a comment
|
5 Answers
Call sp_addlogin instead - it's already parameterized
3 Comments
kenwarner
good point. are there other
sql products that use this same syntax to create logins?
Olga
Ok, I can do it this way. Now I'm facing another problem - I need to create database. CREATE DATABASE doesn't seem feasible to be parameterized either.
Jon Egerton
Its worth noting that, the MSDN page for
sp_addlogin has a warning that the proc will not be available in a future release of SQL server, and therefore shouldn't be used in new developments. See here: msdn.microsoft.com/en-us/library/ms173768.aspxHere'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
});
1 Comment
Olga
Nope, that doesn't work - it throws an exception "Incorrect syntax near '@LoginName'."
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.
Comments
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.
1 Comment
Dai
Ultimately string concatenation is still being performed, just inside the database engine, so I don't see how this mitigates against injection (e.g.
@DB = '\'1=1')Can try something like this:
SqlCommand cmd = new SqlCommand(
"CREATE LOGIN @login WITH password=@pwd", conn);
SqlParameter param = new SqlParameter();
param.ParameterName = "@login ";
param.Value = usertextforlogin;
cmd.Parameters.Add(param);
param = new SqlParameter();
param.ParameterName = "@pwd";
param.Value = usertextforpwd;
cmd.Parameters.Add(param);
1 Comment
Dai
you cannot parameterize object-names in T-SQL, and the
CREATE LOGIN statement uses an object-name, not a string/text value for its arguments.