Error: Code: -131 Msg: Syntax error near '?' on line 1 at Error (native)

[finedays]

Hi guys,

I have stumbled on the error in the title, while trying to make the driver work with this query:

'select Menu.Kod as ?, 
(if Stoki.HaveSerNo <> 2 or Stoki.Miarka = 0 then Stoki.Kod 
else (select Kod from DBA.Stoki as ST where N = Stoki.GS) 
endif) as ItemCode, 
Sklad as Store, 
Nalichnosti.Nalichnost as Qnt, 
Merki.MIme as Measure from Nalichnosti, 
Menu, Merki, Stoki where Nalichnosti.Stoka=Menu.SN and Merki.MN=Menu.Miarka and Menu.Miarka=Nalichnosti.MiarkaBroj and Nalichnosti.Stoka=Stoki.N and Menu.Kod = ? and Sklad=1;'

This query, with value of: 2500000022234 instead of the question marks works, when I connect directly to the database with dbisqlc and works in my code if I replace the question marks with '2500000022234'.

Only doesn't work .exec or .prepare methods of the connection when I try with the question mark.

I have tried to pass parameters directly to .exec as ['2500000022234', '2500000022234'] also as ['2500000022234'] only, as well as [request.query.Barcode] and [request.query.Barcode, request.query.Barcode](I am using Hapi) and I have the same error all the time.

Please help!

[efarrar]

What you are seeing is the expected behavior. The important thing to note is the the "?" placeholder is not a simple text substitution. Instead it designates a piece of the SQL statement that is going to be parameterized right in the database server. The database server parses the statement, builds an execution plan, and notes the location where parameters will be passed. After the statement has been prepared, the parameters are supplied and the query is executed.

This has a couple of advantages

1. If you plan to use the statement multiple times with different parameters you can explicitly prepare it, and then execute it multiple times with different parameters. This gives a performance boost because the SQL string is only parsed and prepared once.
2. There is no chance of SQL injection because the SQL string is parsed before the parameters are supplied.

However, the problem you are running into is that not all parts of a query may be parameterized. For example, the alias of the table (which you are trying to set) must be known when the statement is prepared. Because the "?" cannot be interpreted as a parameter, it is being pass on to the SQL parser which is correctly reporting a syntax error.

For parts of the query that cannot be parameterized, the solution is that you have to build the SQL statement yourself using concatenation. It would be something like this:

'select Menu.Kod as ' + sanitize( request.query.Barcode ) + ', (if Stoki.HaveSerNo <> 2 or Stoki.Miarka = 0 then Stoki.Kod else (select Kod from DBA.Stoki as ST where N = Stoki.GS) endif) as ItemCode, Sklad as Store, Nalichnosti.Nalichnost as Qnt, Merki.MIme as Measure from Nalichnosti, Menu, Merki, Stoki where Nalichnosti.Stoka=Menu.SN and Merki.MN=Menu.Miarka and Menu.Miarka=Nalichnosti.MiarkaBroj and Nalichnosti.Stoka=Stoki.N and Menu.Kod = ? and Sklad=1;'

The risk here is that it introduces the chance for SQL injection. You will need to write a sanitation function that verifies that request.query.Barcode is in the format you expect (it looks like it is supposed to be all numbers, so that should be a pretty easy one to test for).

[finedays]

Thanks for the prompt and detailed reply! Actually it was another problem, but that is a great approach also and with ES6 placeholders it is even nicer :)

[rafriki]

@finedays If you're using a ES6 template literals you are not protecting against SQL injection if they're coming directly from input, NB.