[PB5.62] SQLite variable binding

[Yuri_D]

Hello!

Maybe it is a bug in my head (again... ) or in SQLite itself.
Is it correct that the string variable binding works only with "=?" placeholders but it doesn't with just a "?", f.e.:

Code: Select all

    If GetGadgetState( licProductFamily) = 0 ; If < All > 
      
      SetGadgetState( licProductFamily, 0) ; deselect all
    Else
      _sString = "IN("
      For _i = 1 To CountGadgetItems( licProductFamily) -1 
        If GetGadgetItemState( licProductFamily, _i)
          
          SetGadgetItemState( licProductFamily, 0, 0)
          _sString + Str( GetGadgetItemData( licProductFamily, _i)) +","
        EndIf
      Next
      
      _sString = RTrim( _sString, ",") +")"
    EndIf

    SQLite_BindString(  0, _sString)
    SQLite_BindInteger( 1, GetGadgetItemData( cmbPB_Name, GetGadgetState( cmbPB_Name)))

_sSQL = "...
 WHERE [main].[tblProductFamilies].[ID] ? And [main].[tblPriceBooks].[ID] = ?
..."

Now I fored to use:

Code: Select all

    ;SQLite_BindString(  0, _sString)
    SQLite_BindInteger( 0, GetGadgetItemData( cmbPB_Name, GetGadgetState( cmbPB_Name)))

_sSQL = "...
WHERE [main].[tblProductFamilies].[ID] "+ _sString +" And [main].[tblPriceBooks].[ID] = ?
..."

so I can't offload this query to a data block

BRG
Yury

[skywalk]

Please do not post to BUGS if you are unsure.
You are mixing PB database commands and SQLite dll commands.
Read about SetDatabaseString() and queries like:
"SELECT ?1,?2 FROM Keywords ORDER BY Word,Item"

[Marc56us]

From what I understand, it works in all cases, however, the placeholder content is "evaluated/controlled" before integrating the SQL string, so a text like "IN(" will not work in this case since the system expects a variable and not an SQL statement

Based on the example
https://www.purebasic.com/documentation ... se.pb.html

Code: Select all

         ; OK
         ; SetDatabaseString(0, 0, "pear")  
         ; If DatabaseQuery(0, "SELECT * FROM food WHERE name=?")    
         
         ; KO
         SetDatabaseString(0, 0, "name=pear")  
         If DatabaseQuery(0, "SELECT * FROM food WHERE ?")    
             While NextDatabaseRow(0)
                 Debug GetDatabaseString(0, 0)
             Wend
         EndIf

But maybe I misunderstood.

So doesn't seem to be a bug. Can a moderator move this topic to 'Coding Question' ? Thank you

[Yuri_D]

@skywalk
Ok, thanks, I'll go that way next time but I used only a PB documentation to create my code and according to PB help Example
"Bind variables with SQLite and ODBC
; SQLite and ODBC shares the same syntax for bind variables. It is indicated by the '?' character
..."

@Marc56us
Maybe, but my IMHO is: PB and SQLite shall not care about the string content and SQLite actually didn't because I can successfully enter "IN(1,2,3,4)" and any other query-like sentences into a DB with help of any database browser so I suspect a bug

[Fred]

Moved back to bug for more investigation

[Marc56us]

Small example for investigation (based on pb sample)
(hope this help)

Code: Select all

; ------------------------------------------------------------
;
;   PureBasic - Database example file
;
;    (c) Fantaisie Software
;
; ------------------------------------------------------------
;

UseSQLiteDatabase()

Procedure CheckDatabaseUpdate(Database, Query$)
    Result = DatabaseUpdate(Database, Query$)
    If Result = 0
        Debug DatabaseError()
    EndIf
    
    ProcedureReturn Result
EndProcedure

DatabaseFile$ = GetTemporaryDirectory() + "Database.sqlite"

If CreateFile(0, DatabaseFile$)
    CloseFile(0)
    
    If OpenDatabase(0, DatabaseFile$, "", "")
        
        CheckDatabaseUpdate(0, "CREATE TABLE food (name CHAR(50), weight INT)")
        
        CheckDatabaseUpdate(0, "INSERT INTO food (name, weight) VALUES ('apple', '10')")
        CheckDatabaseUpdate(0, "INSERT INTO food (name, weight) VALUES ('pear', '5')")
        CheckDatabaseUpdate(0, "INSERT INTO food (name, weight) VALUES ('banana', '20')")     
        
        Debug ~"\n--- String set by SetDatabaseString"
        SetDatabaseString(0, 0, "IN(10)")  
        If DatabaseQuery(0, "SELECT * FROM food WHERE weight ?")    
            While NextDatabaseRow(0)
                Debug "Found: " + GetDatabaseString(0, 0)
            Wend
        Else
            Debug "(Nothing)"
        EndIf
        FinishDatabaseQuery(0)
        
        Debug ~"\n--- String direct in SQL query"
        If DatabaseQuery(0, "SELECT * FROM food WHERE weight IN(10,5)")    
            While NextDatabaseRow(0)
                Debug "Found: " + GetDatabaseString(0, 0)
            Wend
        Else
            Debug "(Nothing)"
        EndIf
        FinishDatabaseQuery(0)
        
        CloseDatabase(0)
    Else
        Debug "Can't open database !"
    EndIf
Else
    Debug "Can't create the database file !"
EndIf

Code: Select all

--- String set by SetDatabaseString
(Nothing)

--- String direct in SQL query
Found: apple
Found: pear

So SetDatabaseString does not replace '?' by string 'IN(10,5)'


Would it be possible to display the string received by the system when using SetDataBase... ?
DataBaseError() displays it well, but only in case of error
I think not, because it seems that replacement is only done at the time of execution of the request

Code: Select all

        SetDatabaseString(0, 0, "IN(10)")
        SQL$ = "SELECT * FROM food WHERE weight ?"
        Debug "SQL: " + SQL$

Code: Select all

--- String set by SetDatabaseString
SQL: SELECT * FROM food WHERE weight ?

[Yuri_D]

@Marc56us
When I'm using "?" for "IN(....)", DataBaseError() returns "Database Error: near "?": syntax error"

[freak]

You cannot place SQL syntax elements (like a "=") inside a placeholder like this. This is the whole point of using variable binding: It prevents SQL injection attacks.

If you do this:

Code: Select all

        SetDatabaseString(0, 0, "IN(10)") 
        If DatabaseQuery(0, "SELECT * FROM food WHERE weight ?")   

You are actually executing this query:

Code: Select all

        If DatabaseQuery(0, "SELECT * FROM food WHERE weight 'IN(10)'")

It is interesting that SQLite accepts this as valid syntax, but that is an SQLite problem not a PB one.

[Yuri_D]

@Freak
Thank you for the explanation!
Does it mean that it is not possible to use placeholders for queries morphing?
The strings I need to insert don't contain "=" symbol. I tried:
_sString = "[main].[tblProductFamilies].[ID] IN("+ ... +") And"
_sString = "IN("+ ... +")"
but both variants don't work(