DB.runUpdate parameter parsing error?

[Tim Pigden]

Hi,

I'm executing the following code:

 val sql = """

    insert into pubs (company, site_account_code,name,address1,address2,address3,postcode,division,depot,

    customer_account_code,monday,tuesday,wednesday,thursday,friday,saturday,phone,

    classification,other_access_restrictions) values

    (1, '30021811','FIDELITY INVESTMENTS INTL 2030','PARTNERS IN PURCHASING','25 CANNON STREET',

    'LONDON','EC4M 5TA','850','Battersea','11702','0.0','28','0.0','0.0','0.0','0.0',

    '0207 9614357','U',

    'DELIVER VIA BREAD STREET L/BAY ON L.H.S. CAN WATER BE LEFT IN THE PANTRY AREA JUST INSIDE LOADING BAY???')

    """

    DB.runUpdate(sql, Nil)

It fails with the trace below. I'm pretty certain it's because it's treating the final ??? within the character string as a parameter reference and then finding no parameters.

So the two questions are: 

1. is this design or accident?

2. how can I safely execute valid sql from within a mapper environment that contains text from external uncontrollable data sources? Do I have to set up the query as a parameterised one? There's no benefit in that to me - I have to build the sql string in any case.

I am assuming is perfectly valid sql - I'm not an expert but certainly postgres has no problem with it.

[error]   ! insert this sql

[error]       NoSuchElementException: key not found: 1 (Map.scala:87)

[error] net.liftweb.db.DBLog$LoggedPreparedStatementHandler.substitute$1(LoggingStatementWrappers.scala:367)

[error] net.liftweb.db.DBLog$LoggedPreparedStatementHandler.net$liftweb$db$DBLog$LoggedPreparedStatementHandler$$paramified(LoggingStatementWrappers.scala:371)

[error] net.liftweb.db.DBLog$LoggedPreparedStatementHandler$$anonfun$invoke$69.apply(LoggingStatementWrappers.scala:414)

[error] net.liftweb.db.DBLog$LoggedPreparedStatementHandler$$anonfun$invoke$69.apply(LoggingStatementWrappers.scala:414)

[error] net.liftweb.db.DBLog$class.logStatement(LoggingStatementWrappers.scala:54)

[error] net.liftweb.db.DBLog$LoggedStatementHandler.logStatement(LoggingStatementWrappers.scala:110)

[error] net.liftweb.db.DBLog$LoggedPreparedStatementHandler.invoke(LoggingStatementWrappers.scala:414)

[error] $Proxy12.executeUpdate(Unknown Source)

[error] net.liftweb.db.DB$$anonfun$runUpdate$1$$anonfun$apply$21.apply(DB.scala:548)

[error] net.liftweb.db.DB$$anonfun$runUpdate$1$$anonfun$apply$21.apply(DB.scala:548)

[error] net.liftweb.db.DB$$anonfun$runPreparedStatement$2.apply(DB.scala:648)

[error] net.liftweb.db.DB$$anonfun$runPreparedStatement$2.apply(DB.scala:647)

[error] net.liftweb.util.TimeHelpers$class.calcTime(TimeHelpers.scala:344)

[error] net.liftweb.util.Helpers$.calcTime(Helpers.scala:34)

[error] net.liftweb.db.DB$class.runPreparedStatement(DB.scala:646)

[Tim Pigden]

Investigating source code it looks like this:

    private def paramified : String = {
      val sb = new StringBuilder(500)
      def substitute (in : String, index : Int): Unit = in.indexOf('?') match {
        case -1 => 
	  sb.append(in)

is the culprit 

OK - it's hard work to parse sql but I'd have thought blanket substitution of every question mark regardless of where it occurs is a little bit basic.

Tim

[Jeppe Nejsum Madsen]

Tim Pigden <tim.p...@optrak.com> writes:

> Investigating source code it looks like this:
>
> private def paramified : String = {
> val sb = new StringBuilder(500)
> def substitute (in : String, index : Int): Unit = in.indexOf('?') match {
> case -1 =>
> sb.append(in)
>

> *is the culprit *
> *
> *
> *OK - it's hard work to parse sql but I'd have thought blanket substitution of every question mark regardless of where it occurs is a little bit basic.*

I don't disagree that this is a bug, but fixing it correctly
probably requires quite a bit of non-trivial parsing.

Normally you would use parameters to pass data to the update statement
and ? would only be used to indicate a parameter.

But we should fix it so at least it doesn't crash. Could you open an
issue for this?

/Jeppe

[Tim Pigden]

ok, will do. 

For me a version of runUpdate that did not take parameters would do the job - there is precedent - there is a parameterless runQuery

Would that be practical?

BTW if anyone else is similarly troubled, a solution was to use

val outputString = "E'" + inString.replaceAllLiterally("?", """\077""") + "''"

which works for postgres - but I've not idea what else. The E is for "escape" and activates the \077 parsing. But it goes outside the quote to give E'What\077'  for What?