Escape characters before saving in DB

LiveCode is the premier environment for creating multi-platform solutions for all major operating systems - Windows, Mac OS X, Linux, the Web, Server environments and Mobile platforms. Brand new to LiveCode? Welcome!

Moderators: Klaus, FourthWorld, heatherlaine, robinmiller, kevinmiller

Post Reply
charms
Posts: 122
Joined: Mon Feb 10, 2014 6:21 pm
Location: Singapore
Contact:

Escape characters before saving in DB

Post by charms » Tue Oct 28, 2014 3:43 am

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&quote 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

Simon
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 3901
Joined: Sat Mar 24, 2007 2:54 am
Location: Palo Alto

Re: Escape characters before saving in DB

Post by Simon » Tue Oct 28, 2014 4:19 am

Hi Chris,
I believe the escape char is '

Simon
I used to be a newbie but then I learned how to spell teh correctly and now I'm a noob!

charms
Posts: 122
Joined: Mon Feb 10, 2014 6:21 pm
Location: Singapore
Contact:

Re: Escape characters before saving in DB

Post by charms » Tue Oct 28, 2014 3:28 pm

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

phaworth
Posts: 592
Joined: Thu Jun 11, 2009 9:51 pm

Re: Escape characters before saving in DB

Post by phaworth » Tue Oct 28, 2014 9:54 pm

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

charms
Posts: 122
Joined: Mon Feb 10, 2014 6:21 pm
Location: Singapore
Contact:

Re: Escape characters before saving in DB

Post by charms » Wed Oct 29, 2014 2:39 am

Hi Pete,

Thanks for your input. I'll give it a try.

Kind regards,
Chris

Simon
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 3901
Joined: Sat Mar 24, 2007 2:54 am
Location: Palo Alto

Re: Escape characters before saving in DB

Post by Simon » Wed Oct 29, 2014 4:34 am

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
I used to be a newbie but then I learned how to spell teh correctly and now I'm a noob!

[-hh]
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 2125
Joined: Thu Feb 28, 2013 11:52 pm
Location: Göttingen, DE

Re: Escape characters before saving in DB

Post by [-hh] » Wed Oct 29, 2014 8:16 am

Yet another way is to use the built-in quote(X) function.
http://www.sqlite.org/lang_corefunc.html#quote
shiftLock happens

zaxos
Posts: 222
Joined: Thu May 23, 2013 11:15 pm
Location: Greece

Re: Escape characters before saving in DB

Post by zaxos » Wed Oct 29, 2014 2:16 pm

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&quote, but what if thers a double quote? it will be replaced with quote&quote&quote&quote....

Code: Select all

put "I'm Zak" in theString
replace quote with quote&quote in theString -- will replace single quote with 2 single quotes
replace quote with quote&quote 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
Knowledge is meant to be shared.

charms
Posts: 122
Joined: Mon Feb 10, 2014 6:21 pm
Location: Singapore
Contact:

Re: Escape characters before saving in DB

Post by charms » Wed Oct 29, 2014 3:58 pm

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

MaxV
Posts: 1574
Joined: Tue May 28, 2013 2:20 pm
Location: Italy
Contact:

Re: Escape characters before saving in DB

Post by MaxV » Fri Oct 31, 2014 5:37 pm

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:

Code: Select all

SELECT * FROM table_name
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:

Code: Select all

SELECT * FROM table_name
you'll obtain: She said: "I love you!"
No replace or transformation needed.
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w

Post Reply

Return to “Getting Started with LiveCode - Experienced Developers”