Page 1 of 1
Escape characters before saving in DB
Posted: Tue Oct 28, 2014 3:43 am
by charms
Hi there,
I need to save values in SQLite that contain quotes. So I've been searching through the forums and somebody explained to do it like described below:
Code: Select all
function dbEscapeChars pString
replace quote with quote"e in pString
return pString
end dbEscapeChars
The saving does work then. However I then end up with tons of quotes in the value.
Original that I want to save in the database:
Code: Select all
--: put qbSQLiteConnect("MyDatabase.sqlite") into tConID
After the dbEscapeChars function
Code: Select all
put qbSQLiteConnect(""""MyDatabase.sqlite"""") into tConID
Now when I read the entry from the database I have 4 quotes or even more in some results.
Can someone tell me what the best way is to escape quotes or other characters? And unescape them again?
Thanks,
Chris
Re: Escape characters before saving in DB
Posted: Tue Oct 28, 2014 4:19 am
by Simon
Hi Chris,
I believe the escape char is '
Simon
Re: Escape characters before saving in DB
Posted: Tue Oct 28, 2014 3:28 pm
by charms
Hi Simon,
Do you mean like this?
Code: Select all
function dbEscapeChars pString
replace quote with "'" & quote in pString
return pString
end dbEscapeChars
When I use this then I get an SQL error again. It must be trivial but I don't get it and there is no real documentation I've found.
I've followed following thread:
http://forums.livecode.com/viewtopic.php?f=8&t=1336
The only thing I want to do is like in all other languages escape the characters so that they are stored correctly in MySQL or SQLite: \" or \' or \[.
When using a backslash surprisingly it's running through, but it contains the \ in the text. So when reading the text I would have to replace it again. I'm sure there is a smarter way.
Kind regards,
Chris
Re: Escape characters before saving in DB
Posted: Tue Oct 28, 2014 9:54 pm
by phaworth
The best way to deal with this is to use the "variableslist | array name" parameters of revExecuteSQL. See the dictionary for more info. Using this method, you don;t have to do any escaping of any characters.
Pete
Re: Escape characters before saving in DB
Posted: Wed Oct 29, 2014 2:39 am
by charms
Hi Pete,
Thanks for your input. I'll give it a try.
Kind regards,
Chris
Re: Escape characters before saving in DB
Posted: Wed Oct 29, 2014 4:34 am
by Simon
Hi Chris,
Ok I'm looking at some code of mine dealing with quotes and sqlite and I let quotes through without trouble.
Now there is trouble with curly quotes and with that I do
Code: Select all
replace "“" with quote in tText
replace "”" with quote in tText
Maybe you check for curly quotes?
Simon
Re: Escape characters before saving in DB
Posted: Wed Oct 29, 2014 8:16 am
by [-hh]
Yet another way is to use the built-in quote(X) function.
http://www.sqlite.org/lang_corefunc.html#quote
Re: Escape characters before saving in DB
Posted: Wed Oct 29, 2014 2:16 pm
by zaxos
Code: Select all
put "I'm Zak" in theString
replace "'" with "''" in theString
put "UPDATE theDB SET comment='" & theString & "'" && "WHERE name='zaxos'" into theQuery
databaseConnect theQuery
Not sure why you made a function there, in this code your string will always replace single quotes with double quotes, i think you problem is that you use "quote" word in your code so every time the function runs it replaces quote with quote"e, but what if thers a double quote? it will be replaced with quote"e"e"e....
Code: Select all
put "I'm Zak" in theString
replace quote with quote"e in theString -- will replace single quote with 2 single quotes
replace quote with quote"e in theString -- if for any reason i run this again it will replace every single quote(wich are 2 of them now) with 2 quote, that would result in 4 quotes
put "UPDATE theDB SET comment='" & theString & "'" && "WHERE name='zaxos'" into theQuery
databaseConnect theQuery
Re: Escape characters before saving in DB
Posted: Wed Oct 29, 2014 3:58 pm
by charms
Hi all,
Thanks for your input.
@Simon: I'm using straight quotes according to what I can tell.
@hh: I have built an SQLQueryBuilder library Stack and want to support all databases in it. This is why I only want to use generic database commands.I've seen that MySQL also supports quote but Oracle doesn't. At least I didn't find the reference to it. So this would be the option I'd aim for if I only require SQLite. Thanks, I wasn't aware about this function.
@zaxos: Thanks for your detailed explanation. The quote constant should be equivalent to double quote (ASCII 34) according to the dictionary. As I only have double quotes that need to be escaped i thought that there shouldn't be any other quotes that are replaced. But if you say that you have the behaviour that also single quotes are replaced then I'll try to repeat your tests. This would indeed be a reason for this behaviour.
Kind regards,
Chris
Re: Escape characters before saving in DB
Posted: Fri Oct 31, 2014 5:37 pm
by MaxV
Just for complete clarity using SQLite.
SQLite take an input with '' (two apostrophes, one near another one) and transform automatically it in '.
So if you need to insert a string like:
Hello, it's me.
You need this query:
Code: Select all
INSERT INTO table_name (field1) VALUES ('Hello, it''s me.');
This is always good and works with any programming language: livecode, C++, Java, etc.
When you retrieve data with this query:
you'll obtain:
Hello, it's me.
No replace or transformation needed.
If you need to enter a string with quote, for example:
She said: "I love you!"
You can use it without need of transformation, since the correct query is:
Code: Select all
INSERT INTO table_name (field1) VALUES ('She said: "I love you!"');
The problem is that you can't write " (one character
quote) in livecode, because it's the string delimiter; so you need something like that:
########CODE#######
put "INSERT INTO table_name (field1) VALUES ('She said: " & quote & "I love you!" & quote & "');" into tSQL
revExecuteSQL connID, tSQL
#####END OF CODE#####
When you retrieve data with this query:
you'll obtain:
She said: "I love you!"
No replace or transformation needed.