1

I would like to run a script like:

CREATE LOGIN [me] WITH PASSWORD = @0

and run it like:

var createUserCommand = conn.CreateCommand();
createUserCommand.CommandText = script;
createUserCommand.Parameters.AddWithValue("@0", passwordDecrypted);

However, this throws:

A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll
Additional information: Incorrect syntax near '@0'.

From what I read online (nowhere official, just from SO answers/comments) it is not possible to use SQL parameters with DDL statements. Link to official docs for this are welcome!

OK. I do need this parametrized. As I see it, there are 2 options:

  • I manually sanitize (.Replace("'", "''") => how can I do this best?
  • I call into .NET to sanitize for me. However I assume this is not sanitized within ADO.NET, but just past to SQL Server, and sanitized there...

What would be the best approach?

Bertvan
  • 4,943
  • 5
  • 40
  • 61

1 Answers1

0

All you need to escape is the single ' to 2 x '

commandText = string.Format("CREATE LOGIN [me] WITH PASSWORD = '{0}'", pass.Replace("'", "''"));

An alternative would be to create a stored procedure with a password parameter and use it to build a CREATE LOGIN string which you then sp_executeSQL.

Alex K.
  • 171,639
  • 30
  • 264
  • 288
  • Thanks, that's easy! Can you point to more documentation that this is enough? – Bertvan Oct 22 '14 at 07:14
  • I can't find any definitive documentation but its mentioned here; http://msdn.microsoft.com/en-us/magazine/cc163523.aspx / http://stackoverflow.com/questions/10476252/characters-that-must-be-escaped-in-tsql – Alex K. Oct 22 '14 at 10:51
  • According to the MSDN link, wouldn't using QUOTENAME (SQL) be a better option than using String.Replace() (.NET) – Bertvan Oct 22 '14 at 11:17
  • 1
    It wont wont with you DML (it wont like `PASSWORD = function(..)` and you have the password as a string literal so you would end up trying to do `QUOTENAME('XXX'YYY', '''')` which is invalid – Alex K. Oct 22 '14 at 11:25