I’m using a single ascii character to replace single quotes in strings before inserting them into a SQLite database. This works fine. When I encrypt the string before inserting it into the database upon decryption the ascii character(depending on the one I’ve tried) is either gone or junk when I try to restore the single quote. When I use the exact same string replace/encrypt, decrypt/restore code but skip the database insert the problem is gone. I’m using UTF8 encodings on everything. Why would the ascii character only be changed after the SQLite database insert? Thanks for any advice.
use prepared statements
if your encrypted string is wyz’abc
and you escape the ’ and insert wyz’'abc
the SQL database only gets the wyz’abc (that is the purpose of escapes)
so that ‘’ won’t be there when you retrieve the data, and it shouldn’t be
but prepared statement removes the need to worry about any of that
and you realize the Xojo supports built in encryption for SQLite…
Thanks Dave, I think I found a solution by base 64 encoding the strings. No need to fight the ascii character encoding issues at all.
Ok… trust me it will lead to headaches and/or incompatibity in the future… now would be the time to start doing things the correct way (not to mention that Base64 nearly doubles the size of the data)…
What will you do in the future if you have to deal with databases that come from an external source? just saying…
[quote=338857:@Dave S]use prepared statements
if your encrypted string is wyz’abc
and you escape the ’ and insert wyz’'abc
the SQL database only gets the wyz’abc (that is the purpose of escapes)
so that ‘’ won’t be there when you retrieve the data, and it shouldn’t be
but prepared statement removes the need to worry about any of that
and you realize the Xojo supports built in encryption for SQLite…[/quote]
Sorry Dave I didn’t see your edit at first.
Yes I am using built in SQLite encryption as well but I’m encrypting the strings for tcp/ip communication.
I’ve used prepared statements before but wasn’t presenting a form to the user so I was doing the inserts myself.
You’re also right about the data size of base 64 strings.
Thanks for your insights my logic was off on this one for sure. Time for some redesign. 
I’ll be honest… at one time that was my exact thought process as well…
But with the design work I have done on my Tadpole Manager, I have found that using PS has simplified so many things … once you “crack the nut”, it becomes much easier…
I was able to add BLOB control to TADPOLE very easily, and that is a situation where attempting to “escape” the contents of a binary file (image or whatever) would be problematic, and doubling the footprint would be out of the question 
@Dave S Thanks for all the help.