Building insert string to sql has problems from CSV/Json

Question

I'm trying to build a string from a .CSV file. I convert it to Json and want to build an insert statement to my SQL Server.

I'm using ChoETL nuget to convert from csv to json.

When I build my string I get this:

Insert into dbo.JsonMeta (Json) values('{
  "ID": "xxxxx",
  "FLYTTEDATO": "01/02/2020",
  "FLYTTE_TIDSPUNKT": "1000'"
}')

As you can see after the 1000 digit. There is a ' character. And this makes my string cause an error, because it ends the string.

How can I remove this ' character or do something smart?

Answer 1

Once you have a JSON string, don't paste it into a SQL statement. Use a parameter instead.

This solves your problem, and protects you from SQL injection attacks.

Insert into dbo.JsonMeta (Json) values(@json)