Unknown SQL syntax error for ScalikeJDBC with SQL interpolation

To avoid DRY, I'm attempting to create an sql INSERT statement with variable column names and the data to fill those columns via ScalikeJDBC's sql interpolation:

case class MySQLInsertMessage(tableName:String, columns:List[String], values:List[String])
def depositMessage(msg: MySQLInsertMessage): Unit = {
      NamedDB('MySQLMsgDepositor) localTx { implicit session =>
        val sqlStmt = sql"INSERT INTO ${msg.tableName} (${msg.columns}) VALUES (${msg.values})"
        println("The sql statement is: " + sqlStmt.statement)
        println("The parameters are: " + sqlStmt.parameters)
        sqlStmt.update().apply()
      }
    }

And when I call this with:

depositMessage(MySQLInsertMessage("My_Table", List("key", "email"), List("42", "user@email.com")))

the resulting console printout is:

The sql statement is: INSERT INTO ? (?, ?) VALUES (?, ?)

The parameters are: List(My_Table, key, email, 42, user@email.com)

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 ''My_Table' ('key', 'email') VALUES ('42', 'user@emai' at line 1 java.sql.SQLSyntaxErrorException: 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 ''My_Table' ('key', 'email') VALUES ('42', 'user@emai' at line 1

I've tried wrapping the sql"..." as such instead:sql"""...""", but that doesn't seem to make a difference. I can execute the expected statement just fine in my MySQL workbench GUI. Any idea what my syntax error is?

Solution

Stemming from the hint from @scaisEdge, it seems ScalikeJDBC, when using its syntax, will always place single quotes around any parameterized values. And judging from here - https://github.com/scalikejdbc/scalikejdbc/issues/320 - this is a known issue.

With a MySQL INSERT statement (or others), your table name or column values may not have single quotes around them, though they are allowed to have backticks.

You can use their SQLSyntax.createUnsafely(str:String) method, or, if I wanted to do this as I was doing above, instead of using sql"...", I could use the old way of SQL(s"INSERT INTO ${msg.tableName} (${msg.columns.mkString(",")})")

Note - I believe both of these leave you open to injection attacks. Since, for me, this is a local API and you'd have to have the DB's username and password regardless to use it, I'm going with the createUnsafely way of doing things, with a little regex "cleaner" for a little inelegant piece of mind:

def depositMessage(msg: MySQLInsertMessage): Unit = {
      NamedDB('MySQLMsgDepositor) localTx { implicit session =>
        val unsafeSQLRegex = "[`'\"]".r
        val table = SQLSyntax.createUnsafely(s"`${unsafeSQLRegex.replaceAllIn(msg.tableName, "")}`")
        val columns = SQLSyntax.createUnsafely(msg.columns.map(value => unsafeSQLRegex.replaceAllIn(value, "")).mkString("`", "`, `", "`"))
        val sqlStmt = sql"INSERT INTO $table ($columns) VALUES (${msg.values})".update().apply()
      }
    }
  }