Incomplete SQL statement - SQLITE (CubeSQL)

Databases
1

A customer complained that he was trying to process something with my app, and every try, for some minutes the DB showed an error “Incomplete SQL statement”… and suddenly after a while the error went away and he could finish what he was doing…

Never happened that before, so I looked at the CubeSQL log and found it… but looking at my code, I could not reproduce the problem…

Here’s the CubeSQL log…

2017-11-18 15:05:01 BEGIN TRANSACTION EXECUTE
2017-11-18 15:05:01 SELECT NdeDocumento FROM PASAJEROS WHERE NdeDocumento LIKE ‘//%’;
2017-11-18 15:05:01 INSERT INTO PASAJEROS (Nombre, Apellido, NdeDocumento, nomape) VALUES (‘Carlos’, ‘Elosegui’’, ‘//2415’, ‘carloselosegui’);
2017-11-18 15:05:01 Incomplete SQL statement. ERROR 192.168.1.174

The code…

[code] app.HotelDB.sqlExecute “BEGIN TRANSACTION”

dim NdeDoc as string = SetPAXDummyDOC

app.HotelDB.SQLExecute (“INSERT INTO PASAJEROS (Nombre, Apellido, NdeDocumento, nomape, email, telefono ) VALUES (’”+nombre.text.Trim.Titlecase+"’, ‘"+apellido.text.Trim.Titlecase+"’, ‘"+NdeDoc+"’, ‘"+LimpiarString(nombre.text.Trim.Lowercase + apellido.text.Trim.Lowercase)+"’, ‘"+email.Text+"’, ‘"+TF_Telefono.Text+"’)")

if App.HotelDB.Error then
App.DisplayDatabaseError true
return
end if[/code]

Any ideas would be appreciated…
Thanks
Roman

2

2017-11-18 15:05:01 INSERT INTO PASAJEROS (Nombre, Apellido, NdeDocumento, nomape) VALUES (‘Carlos’, ‘Elosegui’’, ‘//2415’, ‘carloselosegui’);

Double ‘’ in ‘Elosegui’’ cutting off your SQL statement.

Highly recommend you use SQLdeLite. Drop in the module and use a SQLdeLite.Record object to fill in the fields. Uses prepared statements behind the scenes so this does not happen. https://github.com/1701software/SQLdeLite

Alternatively you need to escape out extra ‘’ characters.

3

geeez… I’ve overlooked it… thank you so much, Philip…

I usually use my EscapeSQLData method to insert strings into the DB but obviously this one ‘escaped’ me…

:slight_smile:

4

Happy to help.

5

or perhaps “PREPARED STATEMENTS”…

6

Hey Dave, I understand the importance (I’ve read it many times) of prepared statements, but if it’s just a couple of employees in the local network of the company with very basic interaction with the SQL database… is it still considered unsafe ?

Yeah… I know… security is never enough… well… I’ll lift it up in the priorities list…

7

[quote=360340:@Roman Varas]Hey Dave, I understand the importance (I’ve read it many times) of prepared statements, but if it’s just a couple of employees in the local network of the company with very basic interaction with the SQL database… is it still considered unsafe ?

Yeah… I know… security is never enough… well… I’ll lift it up in the priorities list…[/quote]

Its a good habit to get into, and it makes all this with quotes etc so much easier to deal with.