insert a json array in a mysql table

Question

Using mysql2 I am parsing a json response from an endpoint and inserting some of its values in a mysql database. What I now want to do is to log this response in a table of my database. The json I receive is in a variable called data and I properly access it's values like data.object.account_country and so on.

data is a valid json like this (trimmed for privacy):

{
    "object": {
      "id": "in_1IYxloLuHwfLSa62Zjqz5jX5",
      "object": "invoice",
      "account_country": "IT",
    }
}

Now I want to store the entire json in a log table in mysql so I created a json column in my table and I am trying to insert the json.

My query looks like this:

con1.query("INSERT INTO webhook_log SET tipo='"+eventType+"', risposta='"+data+"', created_on=now()",function(err,result){
                        if(err) throw err;
                        console.log(`ðŸ””  Webhook logged`);
                    });

This throws the following error:

{ Error: Invalid JSON text: "Invalid value." at position 1 in value for column 'webhook_log.risposta'.

I also tried removing the single quotes around data but in this case I get:

{ Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[object Object], created_on=now()' at line 1

There is obviously something I am doing wrong here but I cannot spot the light on the error.

I'm not a node.js developer, but it sounds like you're trying to stick the object into your SQL string. You need to marshal the object into a JSON string first using `JSON.stringify` if I recall. Also please learn to use query parameters instead of using `+` string concatenation. You're creating an SQL injection vulnerability. — Bill Karwin, Apr 07 '21 at 14:44
On the parametrized query issue, yes. It is the following step in the learning curve for node: I well know the point. On the json.stringify part will do a try. I thought that since the field type is JSON I could stick there directly the response. — Lelio Faieta, Apr 07 '21 at 14:47
The field type is JSON, but you're just concatenating a string at the point where the object is interpolated into your SQL query. It has no idea that string will be used for a query at all. Expressions don't know how they will be used. — Bill Karwin, Apr 07 '21 at 14:49

score 2 · Accepted Answer · answered Apr 07 '21 at 15:07

Per the comments above, the answer is to use JSON.stringify to convert a Javascript object into a JSON string.

Also use SQL query parameters. Once you get the hang of it, it's actually easier than doing string-concatenation and getting eyestrain trying to balance all the quotes.

con1.query("INSERT INTO webhook_log SET tipo=?, riposta=?, created_on=now()",
  [eventType, JSON.stringify(data)],
  function(err,result){ if(err) throw err; console.log(ðŸ””  Webhook logged); });

insert a json array in a mysql table

1 Answers1