Escape characters before saving in DB

[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&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]

Hi Chris,
I believe the escape char is '

Simon

[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

[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

[charms]

Hi Pete,

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

Kind regards,
Chris

[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

[[-hh]]

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

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

[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

[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:

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.