null and string interpolation in C# 6

Question

I am using new C# 6.0 features String Interpolation for generating SQL statements.

$@"INSERT INTO [dbo].[TableName]([Column1], [Column2]) Values({item.property1}, {item.property2})";

If properties are null then generated SQL is the following

INSERT INTO [dbo].[TableName]([Column1], [Column2]) Values(,)

And this cause an error. (Incorrect SQL).
I need Null instead of empty spaces. Can i somehow achieve this ?

Never mind the wrong syntax, using SQL concatenation is a very bad idea that exposes you to injection attacks. Use parameterized queries instead, eg Values(@param1, @param2) — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Mar 16, 2016 at 14:39
At the moment, please do care about security because it's absolutely trivial to do and it solves your problem to boot -- SqlParameter.Value = (object) object ?? DBNull.Value will do the Right Thing. — Jeroen Mostert
– Jeroen Mostert, Commented Mar 16, 2016 at 15:08
@Disappointed. It's not just about SQL injection. It's also about the appropriate handling of null values. Or dates. Or strings. Or string with apostrophes in them. Or... Switch to parameters and these problems become a lot easier to solve. — D Stanley
– D Stanley, Commented Mar 16, 2016 at 15:28
Parameterized queires are not just security. They solve the problem you're trying to solve way better than you will ever come up with in short term. — Paulo Morgado
– Paulo Morgado, Commented Mar 16, 2016 at 23:24

usr · Accepted Answer · 2016-03-16 15:25:38Z

6

{Convert.ToString(item.property1) ?? "NULL"} should do it. This is still broken, because you need very specific formatting for the SQL to come out right. You can solve the formatting yourself.

The usual SQL injection disclaimers have been given in the comments already. This approach is unsalvagable. You need to throw this away.

answered Mar 16, 2016 at 15:25

usr

172k38 gold badges251 silver badges380 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jeroen Mostert Over a year ago

Yep, as long as none of the properties are actually strings or DateTime or bool, this should work fine. Fixing all these issues while still stubbornly refusing to use SqlParameter is left as an exercise to the reader...

Collectives™ on Stack Overflow

null and string interpolation in C# 6

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related