bind Parameters for sqlite-prepared statements.

Posted by Myonara on Thu 08 Feb 2018 06:33 AM — 3 posts, 15,070 views.

Myonara Thu 08 Feb 2018 06:33 AM #0

It seems, that the prepared statements in the MushClient
interface doesn't support the binding of parameters,
however the DatabasePrepare understands the ? or ?1 syntax.

Here is what I've tried:


        DatabaseExec("db",[[
        CREATE TABLE IF NOT EXISTS soundcache (
        filename TEXT PRIMARY KEY,  -- access path
        ISOtime TEXT, -- time format for If-Modified-Since
        wav BLOB )    -- the content.
        ]] )

        rc = DatabasePrepare ("db", 
            "INSERT OR REPLACE INTO soundcache (filename,ISOtime,wav) "..
            "VALUES (?,?,?)")  --> returns 0 (sqlite3.OK)
        Note ("1="..rc)
        rc = DatabaseStep ("db",filename,isotime,body)   -- returns 101 (sqlite3.DONE)
        Note ("2="..rc)
        rc = DatabaseFinalize ("db")  -- returns 0 (sqlite3.OK)
        Note ("3="..rc)

What I've expected from other sqlite interfaces, that DatabaseStep binds the extra parameters to ?-list of parameters.
If a dynamic parameter list is not possible to all supported script engine, I propose a new function DatabaseBind("db",1,data) to assign the first ?-occurrence the field-Data and so on prior to the DatabaseStep.

The big advantage of prepared statements with bound parameters they are type safe and the measure against sql injection.

The above example is working fine, but it just inserts an empty line instead of the real data.

Nick Gammon Australia Forum Administrator Thu 08 Feb 2018 06:48 AM #1

Yes, I don't think that was every implemented. However you can use the Lua sqlite3 interface to do it:

http://www.gammon.com.au/scripts/doc.php?lua=stmt:bind

http://www.gammon.com.au/scripts/doc.php?general=lua_sqlite3

Myonara Thu 08 Feb 2018 06:42 PM #2

I followed your advice, and after fixing some issues with the http headers here is the corrected insertion code:


stat = localDB:prepare(
"INSERT OR REPLACE INTO soundcache (filename,ISOtime,wav) " 
.. "VALUES (?,?,?)")
        stat:bind(1,filename)
        stat:bind(2,isotime)
        stat:bind_blob(3,body)
        rc = stat:step()
        if rc == sqlite3.DONE then
            stat:finalize()
            localDB:close()  -- close it
        else
           --error handling and others
        end -- if

Once I have this sound caching mechanism up and running, I will publish it on github.