-- create connections once when channel starts

local conn_SQLite = db.connect{
   api=db.SQLITE, 
   name='Test', 
   user='root',         -- not required - ignored by SQLite
   password='password', -- not required - ignored by SQLite
   live = true
}

local conn_MySQL = db.connect{
   api=db.SQL_SERVER, 
   name='Test', 
   user='root', 
   password='password', 
   live = true
}

function main()
   local V1, V2
   V1=conn_SQLite:quote("Data with ' character")
   V1=conn_SQLite:quote('Data with " character')

   V2=conn_MySQL:quote("Data with ' character")
   V2=conn_MySQL:quote('Data with " character')

   local surname = "Smith"   
   -- CORRECT way to create the query string
   conn_SQLite:query([[SELECT * FROM Patient WHERE LastName = ]]..conn_SQLite:quote(surname))
   -- INCORRECT no escaping works for "Smith" but not for "O'Toole" (below)
   conn_SQLite:query([[SELECT * FROM Patient WHERE LastName = ']]..surname.."'")
   -- INCORRECT way to create the query string
   conn_SQLite:query([[SELECT * FROM Patient WHERE LastName = ']]..conn_SQLite:quote(surname).."'")
   
   local surname = "O'Toole"   
   -- "O'Toole" query "manually" escaped for reference
   conn_SQLite:query([[SELECT * FROM Patient WHERE LastName = 'O''Toole']]) -- SQLite
   conn_MySQL:query([[SELECT * FROM Patient WHERE LastName = 'O\'Toole']])  -- MySQL
   -- CORRECT
   conn_SQLite:query([[SELECT * FROM Patient WHERE LastName = ]]..conn_SQLite:quote(surname))
   -- INCORRECT no escaping worked for "Smith" but not for "O'Toole" (try it)
   conn_SQLite:query([[SELECT * FROM Patient WHERE LastName = ']]..surname.."'")
end