SQLite:插入从SQL请求获得的带有单引号的文本 [英] SQLite : Insert text with single quote got from a SQL request

问题描述

我已经检查了所有关于此问题的论坛，并找到了第一个插入的解决方案，我使用双引号"代替了单引号，如下所示:

I have already check all the forums about this issue and I found a solution for my first insert, I used the "double quoted" instead a single quote as follow:

insertGiftShop(2,"photo02","Modern City by night photo", "item-grumdy1l", "A view of Modern City''s skytrain",1,100, "","no","items",0)

上一个功能是在游戏开始时插入我的行.当我检查数据库时，该值的存储方式如下:现代城市空中列车的视图".

The previous function is inserting my row at the beginning of the game. When I check the database, the value is stored like this : " A view of Modern City's skytrain ".

效果很好！现在，我试图获取存储的信息现代城市的空中列车的视图"，并将其插入到另一个表中，如下所示:

It works very well! Now I'm trying to get the stored information "A view of Modern City's skytrain" and insert it in another table such as following:

function insertInventory(id, code, name, src, desc, sale, qtyoninventory, price, usetxt, type)
   local sql = "insert into inventory (id, code, name, src, desc, sale, qtyoninventory, price, usetxt, type) values (" .. id.. ",'" .. code .. "','" .. name .. "', '" .. src .. "', '" .. desc .. "', '" .. sale .. "',"..qtyoninventory..","..price..",'"..usetxt.."','"..type.."')"
   db:exec(sql)
end


insertInventory(maxid+1,row_2.code, row_2.name, row_2.src, row_2.desc, "no",1,row_2.price,row_2.usetxt, row_2.type)

在这种情况下，我直接从存储的文件中获取row_2.desc(什么是等视图".).但是它不起作用，因为它需要使用单引号"！

In this situation, I'm getting the row_2.desc (what is "A view of etc..") directly from a stored filed. BUT it doesn't work because it takes the "single quote"!

当文本中包含单引号时，我该如何分辨row_2.desc的格式，以便添加所需的双引号"?

How can I tell "format" the row_2.desc in order to add a needed "double quote" when the text has single quote inside?

根据您的评论，我更改了将数据插入"表中的方式.

According to your comment, I've change the way to "insert" data in my tables.

所以，我尝试了这个:

tx.executeSql("INSERT INTO inventory(id, code, name) VALUES(?,?,?)",[2, "photo02", "Modern City's skytrain"])

"["附近出现错误.语法正确吗?

I've an error near "[". Is the syntax correct?

我用了这个，是正确的并且可以防止SQL注入吗?

I used this, is it correct and prevent SQL injection?

db:exec([[INSERT INTO items(id, code, name, src, desc, sale, qty, price, usetxt, type, onscene, area) VALUES(1,"ls01","LETP", "item-lss", "LVP","no",0,100, "It this burger?", "items", "yes", "table02")]])

推荐答案

永远不要将字符串值直接放入SQL字符串！ 这不仅给您带来格式化问题(如您所见)，而且还允许进行SQL注入攻击.

Never ever put string values directly into an SQL string! This not only gives you formatting problems (as you have seen), but also allows SQL injection attacks.

使用参数代替，则无需转义引号:

Use parameters instead, then you don't need to escape quotes:

tx.executeSql("INSERT INTO MyTable(ID, Name, Description) VALUES(?,?,?)",
              [2, "photo02", "Modern City's skytrain"]);

这篇关于SQLite:插入从SQL请求获得的带有单引号的文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！