-4

I want to keep the null values (insert them to DB) while i pass them over as a string to the sql variable in my C# code.

var valString = new StringBuilder();
//somevalue is null at this point
valString.Append($"({id}, " +$"'{SomeValue}')");

The string interpolation is returning an emptyString for SomeValue => '' instead of null. I want to preserve that null in the string interpolation and pass it to the query string. Is it possible?

Vah Run
  • 11,223
  • 2
  • 11
  • 15
  • 10
    I would strongly advise that you start using parameterized SQL instead of passing the values in string format at all. The biggest advantage of that is protection against SQL injection attacks. – Jon Skeet Nov 07 '19 at 16:37
  • 3
    This looks ripe for SQL injection. You should ***never*** construct a SQL statement using string interpolation. There are tools in every library for doing parameter interpolation and you should use those. – Sumner Evans Nov 07 '19 at 16:37
  • 3
    @SumnerEvans: There are times where it's appropriate to construct SQL with string interpolation, e.g. for a variable number of columns. It's using string interpolation for the *values* that's risky. (And there are weird ways in which even those can be done safely using FormattableString, but that's a different matter.) – Jon Skeet Nov 07 '19 at 16:38
  • But this is a requirement and I have no option at this point (Sorry). Any possible solution is accepted. – Vah Run Nov 07 '19 at 16:38
  • i have written a string extension method to do something like dynamic result = null; if (input != null) { result = $"'{input}'"; } return result; but when null is returned, it still gets replaced with emptyString – Vah Run Nov 07 '19 at 16:39
  • 4
    Is whoever determined this requirement aware that they're opening up a major security vulnerability? I *strongly* urge you to push back on this. Note that even if you *did* want to do this, you wouldn't want `'NULL'` but `NULL`. It would be simplest to do that using a normal conditional operator or `if` statement rather than trying to cram the behavior into string interpolation itself. – Jon Skeet Nov 07 '19 at 16:43
  • 1
    I suggest doing this `valString.Append($"({id}, " + $"{(SomeValue==null?"null": "'"+SomeValue+ "'")})");` – Krishna Varma Nov 07 '19 at 16:44
  • @KrishnaMohanVarma- but is it possible to preserve that Null as a null object instead of setting it as a string? – Vah Run Nov 07 '19 at 16:46
  • 3
    @VahRun: You're *building a string*. That's the whole problem. Strings don't contain references etc - they're just text. That's exactly what we've been suggesting you avoid, by using parameterized SQL. – Jon Skeet Nov 07 '19 at 16:47
  • 2
    I see you saying that it's a requirement to do it this way. But are you sure about that? Are you sure whoever wouldn't be happier if you just did it the correct way (SQL parameterization)? If someone is truly *requiring* you to do this via string concatenation, you have an obligation to push back. Tell them you won't knowingly add a vulnerability to the system. If they tell you to do it anyways and won't listen to you, then you need to escalate to their boss, or to the legal team. The sheer *risk* of adding a vulnerability like this will be far more costly than just implementing this properly – mason Nov 07 '19 at 17:45

2 Answers2

0

This will use SomeValue if it isn't null, and use NULL if it is.

var valString = new StringBuilder();
//somevalue is null at this point
valString.Append($"({id}," + (SomeValue == null ? "NULL" : $"'{SomeValue}'"));
Christopher Townsend
  • 1,527
  • 1
  • 13
  • 37
  • but is it possible to preserve that Null as a null object instead of setting it as a string? – Vah Run Nov 07 '19 at 16:46
  • 4
    No. SQL's Nulls are translated as `DBNull.Value`, not C#'s `null`. Different objects. – Eric Wu Nov 07 '19 at 16:47
  • 5
    @VahRun don't use string concatenation. That's it. There are no nulls to preserve, you're asking to generate the `NULL` string (four characters) when the input value is null. Just *don't* do it. You can easily end up with bad SQL, SQL injection attacks or invalid text due to localization issues. – Panagiotis Kanavos Nov 07 '19 at 16:49
  • 1
    I agree, even though this is possible its bad practice and quite dangerous to do so. There are other options for safer queries. – Christopher Townsend Nov 07 '19 at 16:50
0

you can do this as follows:

var valString = new StringBuilder();
valString.Append($"({id}, " +$"'{SomeValue ?? "null"}')");
Muhammad Aftab
  • 1,098
  • 8
  • 19