MySQL Insert - not working / quote handling?

Questions
#1

Hello -

I have a simple N8N workflow which has me stumped…

I am putting data from an API (this is working !!!) and trying to insert data from the results into a MySQL table. If I use the MySQL with operation = Insert then I get a bunch of blank rows inserted that equal the number of rows in my JSON. Frustrating but ok…

… If I use the Execute Query operation than it works BUT if a text value has a ’ or " in it than the process fields with an error like:

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 's

Thoughts?

Insert statement looks like this:

INSERT INTO `table` (`CreatedDt`, `Title`) VALUES (CURRENT_TIMESTAMP, '{{ $json["title"] }}')

Thank you!

#2

Hi @n8n_rookie, I am sorry you’re having trouble.

I am not sure if I fully understand the problem though. Seeing you have deleted the question template with the details it asks, could you please share:

  1. The exact JSON data you are passing to your MySQL node
  2. The MySQL node you are using (simply select it on your workflow canvas, press Ctrl+C to copy, then Ctrl+V here on the forum)
  3. Your table schema
  4. The version of n8n you are currently using

Thank you!

#3

Looks like you use the Execute Query operation from MySQL node.
I believe using the Insert operation with a proper Set node in front will fix the issue).
(I don’t think the built-in Insert operation has SQL Injection issue, the error you’re facing is an well-know SQL Injection when you build the query manually without escaping the input…)