Does a SQL CLR stored procedure prevent injection?

Question

I have written a CLR stored procedure in C# like this

[Microsoft.SqlServer.Server.SqlProcedure]
public static void IsUserNameExists(string strUserName, out SqlBoolean returnValue)
{      
    using (SqlConnection connection = new SqlConnection("context connection=true"))
    {
        connection.Open();
        SqlCommand command = new SqlCommand("Select count(UserName) from [User] where UserName='" + strUserName + "'", connection);

        int nHowMany = int.Parse(command.ExecuteScalar().ToString());

        if (nHowMany > 0)
            returnValue = true;
        else
            returnValue = false;
    }
}

Is it vulnerable to SQL injection? I am using SqlParameter. Any best practises?

Answer 1

CLR Stored procedure doesn’t prevent this by default. You need to do this yourself since CLR doesn’t do this automatically (I guess this was the actual questions you wanted to know)

Just update your code like this and you should be all good.

 [Microsoft.SqlServer.Server.SqlProcedure]
    public static void IsUserNameExists(string strUserName, out SqlBoolean returnValue)
    {
        using (SqlConnection connection = new SqlConnection("context connection=true"))
        {
            connection.Open();
            SqlCommand command = new SqlCommand("Select count(UserName) from [User] where UserName=@UserName", connection);
            command.Parameters.Add(new SqlParameter("@UserName", strUserName));

            int nHowMany = int.Parse(command.ExecuteScalar().ToString());

            if (nHowMany > 0)
                returnValue = true;
            else
                returnValue = false;
        }
    }