Store JSON as string in Azure SQL Database

Question

I'm trying to insert JSON as a string into a table of Azure SQL database. The column 'Info' was created as nvarchar(max). But I get an error in both ways I tried:

Attempt 1: send JSON as object

INSERT INTO productsTable (Id, Name, Url, Info) VALUES (123456, 'Name of product', 'https://www.example.com/product/123456', {'test':'here','nested':{'itest':'ivalue','itest2':100}})

Results in an error:

"error": {
  "code": "EREQUEST",
  "originalError": {
     "info": {
        "name": "ERROR",
        "event": "errorMessage",
        "number": 102,
        "state": 1,
        "class": 15,
        "message": "Incorrect syntax near 'test'.",
        "serverName": "myapp",
        "procName": "",
        "lineNumber": 1
     }
  }

Attempt 2: JSON.stringify the json before sending for write.

INSERT INTO CampaignDetailsTable (Id, Name, Url, Info) VALUES (123456, 'Name of produc', 'https://www.example.com/product/123456', '{\'test\':\'here\',\'nested\':{\'itest\':\'ivalue\',\'itest2\':100}}')

Results in the same error:

"code": "EREQUEST",
"originalError": {
  "info": {
     "name": "ERROR",
     "event": "errorMessage",
     "number": 102,
     "state": 1,
     "class": 15,
     "message": "Incorrect syntax near 'test'.",
     "serverName": "myapp",
     "procName": "",
     "lineNumber": 1
  }

}

I don't care about what format it is stored in SQL, as long as I can revert to JSON object in my code after I read it back.

Its just a string so when you are doing a literal insert it follows all the rules for escaping quotes. https://stackoverflow.com/questions/1586560/how-do-i-escape-a-single-quote-in-sql-server. Also since it is a string it must be surrounded by quotes. — Nick.Mc, Mar 31 '20 at 22:22
You can post it in here: https://www.freeformatter.com/sql-escape.html. Copy the result, and put another set of qutoes around it. — Nick.Mc, Mar 31 '20 at 22:24

Nick.Mc · Accepted Answer · 2020-03-31T22:32:37.093

2

This is just a string like any other so at the very least it needs to be surrounded by single quotes:

'My String'

Your string happens to have single quotes in it so you need to escape those:

'you''re doing it wrong'

I pasted your example in here

https://www.freeformatter.com/sql-escape.html

It gave me this:

{''test'':''here'',''nested'':{''itest'':''ivalue'',''itest2'':100}}

You just need to surround that in quotes, because it's a string literal. So this is what should be in your insert.

'{''test'':''here'',''nested'':{''itest'':''ivalue'',''itest2'':100}}'

Run this in management studio to test:

SELECT '{''test'':''here'',''nested'':{''itest'':''ivalue'',''itest2'':100}}'

edited Mar 31 '20 at 22:32

answered Mar 31 '20 at 22:27

Nick.Mc

18,304
6
61
91

Thanks @Nick.McDermaid . I had also tried making it a string literal which failed, now I see I should have escaped single quotes with another single quote. I"m all set. – Curious101 Mar 31 '20 at 23:10
Wouldn't it be better to parameterize the query instead of escaping quotes? Improperly escaping strings and concatenating them into queries is one way that SQL injection attacks happen. – alroc Mar 31 '20 at 23:44
Yes it would be better. – Nick.Mc Apr 01 '20 at 00:08
@alroc , I'm able to parameterize for Azure Storage table. But I have not been able to find any way to do that for Azure SQL inserts. I'm using node.js and mssql and the command I'm using is: ---------------------------------------- sql.connect(dbConfig) .then(pool => { return pool.request() .query(jsonQueryPacket.query); }) ------------------- I'll ask a separate question on how to parametrize it. But if you have any quick suggestions then I'd love to try it. – Curious101 Apr 01 '20 at 00:39
@Curious101 have a look at https://stackoverflow.com/a/48289995/1324345 for parameterizing queries with node.js – alroc Apr 01 '20 at 01:36
Thanks @alroc. This looks promising, appreciate you sharing the link. – Curious101 Apr 01 '20 at 01:53

Store JSON as string in Azure SQL Database

1 Answers1