Unable to send String value from Arduino to MySQL

Question

I'm trying to send a String value to MySQL, but everything I've tried failed and I don't understand why. Sending integers and floats is never a problem, but sending String or char never works.

I also haven't found any documentation on the error - as if I need a specific MySQL version to handle String or char, while Integer and float works fine?

//--------wifi-------------
#include "defines.h"
#define WIFI_FIRMWARE_LATEST_VERSION        "1.4.8"
#include <SPI.h>
#include <WiFiNINA_Generic.h>
byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
IPAddress server_addr(*, *, *, *);
char user[] = ******;
char password[] = *****;
WiFiClient client;
char ssid[] = ****;
char pass[] = ****;
int status_wifi = WL_IDLE_STATUS;

// -------- MySQL -----------
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
MySQL_Connection conn((Client *)&client);
int status_mysql;
String hostName = ****;
char query[255];

void setup() {
  Serial.begin(9600); //initialise serial monitor

  //--- wifi
  while (!Serial && millis() < 5000);
  Serial.print(F("\n(Setup) Start WiFiPing on ")); //Serial.println(BOARD_NAME);
  Serial.println(WIFININA_GENERIC_VERSION);
  if (WiFi.status() == WL_NO_MODULE) {
    Serial.println(F("Communication with WiFi module failed!"));
    while (true);
  }
  String fv = WiFi.firmwareVersion();
  if (fv < WIFI_FIRMWARE_LATEST_VERSION) {
    Serial.print(F("(Setup) Your current firmware NINA FW v"));
    Serial.println(fv);
    Serial.print(F("(Setup) Please upgrade the firmware to NINA FW v"));
    Serial.println(WIFI_FIRMWARE_LATEST_VERSION);
  }
  while (status_wifi != WL_CONNECTED) {
    Serial.print(F("(Setup) Attempting to connect to open SSID: "));
    Serial.println(ssid);
    status_wifi = WiFi.begin(ssid, pass);
  }
  Serial.println(F("(Setup) You're connected to the network"));

  //-- mysql
  Serial.println("(Setup) Connecting to MySQL...");
  status_mysql = conn.connect(server_addr, 3306, user, password);
  if (status_mysql == true) {
    delay(1000);
  }

}

void loop() {

  Serial.println("(Looping) Sketch: MySQL_insert_string");

  //--- MySQL
  Serial.println("(Looping) Connecting to MySQL...");
  status_mysql = conn.connect(server_addr, 3306, user, password);

  Serial.println("(Looping) Trying to send data");
  String s1 = "Hello World!";
  char buf[30];
  s1.toCharArray(buf, s1.length());
  Serial.println(buf);

  sprintf(query, "INSERT INTO pyntiqlv_webhooks.new_table VALUES (%s)", buf);
  MySQL_Cursor *cur_mem2 = new MySQL_Cursor(&conn);
  cur_mem2->execute(query);

  delete cur_mem2;
  Serial.println("(Looping) Done sending");

  Serial.println(" ");
  delay(2000);

}

Result:

(Looping) Sketch: MySQL_insert_string
(Looping) Connecting to MySQL...
...trying...
Connected to server version 8.0.29-cll-lve
(Looping) Trying to send data
Hello World
Error: 161 = 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 'World)' at line 1.
(Looping) Done sending

Answer 1

You are missing the character data delimiters: single quotes around Hello World, the SQL command must be:

INSERT INTO pyntiqlv_webhooks.new_table VALUES ('Hello World!')
but yours:
INSERT INTO pyntiqlv_webhooks.new_table VALUES (Hello World!)

You could use the code:

sprintf(query, "INSERT INTO pyntiqlv_webhooks.new_table VALUES ('%s')", buf);

Also, you should be able to use s1, no need to first transfer to buf, because sprintf will not copy the terminating null, anyway.