Re: when I insert table, value is a string contains apostrophe - it throws an error there - how to fix

[Vasanta <vtanna1@...>]

Thanks Florian for the script, actually I am calling db.insert below, all the bottom db.execute where it executes the query string, where it fails, do I have to mege the script you gave me below with my db.insert script?.  this is my db.insert script.

 

function db.insert (tablename, tableInput, notFullKey)

      local queryString = ""

      local insertPart = "INSERT INTO " .. tablename .. " ("

      local valuesPart = ") VALUES ("

    -- get col names

     local allKeys = db.getColNames(tablename)

     -- get defaults

    local defaults = db.getDefaults(false, tablename)

   -- make strings

   for k,v in pairs(allKeys) do

   -- skip rowid column

    if (v ~= "_ROWID_") then

        insertPart = insertPart .. "\'" .. v .. "\', "

       -- if post contained field already

       if notFullKey and util.tableKeyExists(tableInput, v) then

          valuesPart = valuesPart .. "\'" .. tableInput[v] .. "\', "

      elseif util.tableKeyExists(tableInput, tablename .. "." .. v) then

          valuesPart = valuesPart .. "\'" .. tableInput[tablename .. "." .. v] .. "\', "

        -- else if default exists, use it

      elseif defaults[tablename .. "." .. v] then

          valuesPart = valuesPart .. "\'" .. defaults[tablename .. "." .. v] .. "\', "

        -- else use empty string

       else

          valuesPart = valuesPart .. "NULL, "

       end

    end

  end

  insertPart = insertPart:sub(1, insertPart:len()-2) --shave last comma

  valuesPart = valuesPart:sub(1, valuesPart:len()-2) --shave last comma

  valuesPart = valuesPart .. ")"

  -- execute it

  queryString = insertPart .. valuesPart

  local cur, errstr = db.execute(queryString)

  util.appendDebugOut(queryString .. "<br>\n")

   return cur, errstr

end

 

 

 

 

 

On Wed, Feb 3, 2010 at 9:27 AM, Florian Weimer <fw@deneb.enyo.de> wrote:

* Vasanta:

> Thanks a lot for your help. I am using sqlite3.

Here's a complete example.  It turns out that LuaSQL doesn't support
parametrized queries (or prepared statements), so you have to do your
escaping manually.

It's key to encode all externally supplied data with conn:escape(str)
and wrap its result in ''.

require "luasql.sqlite3"

local env = luasql.sqlite3()
local conn = env:connect(":memory:")
assert(conn:execute("CREATE TABLE foo(a, b)"))
local data = "">assert(conn:execute("INSERT INTO foo VALUES ('"
                   .. conn:escape(data)
                .. "', 'data')"))
local c = conn:execute("SELECT a, b FROM foo")
while true do
  local a, b = c:fetch()
  if a then
     print(a, b)
  else
     break
  end
end