0

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?

NateH06
  • 3,154
  • 7
  • 32
  • 56
  • 1
    `Any idea what my syntax error is?` [When to use single quotes, double quotes, and back ticks in MySQL](http://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks#11321508) – Lukasz Szozda Mar 28 '18 at 18:38
  • you can't use quote (single or double) for object name ... – ScaisEdge Mar 28 '18 at 18:48

1 Answers1

0

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()
      }
    }
  }
NateH06
  • 3,154
  • 7
  • 32
  • 56