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?

Where does this single quote comes from in the first place? The simplest approach would be to fix your JSON generator. Or if the single quote is for real, then you should use a parameterized query so it does not clash with the outer quotes. — GMB, Jun 13 '20 at 01:07
it comes from the data. Its already in the CSV file. It cannot be altered. It i system delivered from another platform. — SqlKindaGuy, Jun 13 '20 at 06:40

score 1 · Accepted Answer · answered Jun 13 '20 at 17:39

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)

answered Jun 13 '20 at 17:39

score 0 · Answer 2 · answered Jun 13 '20 at 17:31

Just escape it with another single quote, right next to it. You would still retain that single quote in the data.

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

2 Answers2