MySQL Special Characters

Got a LiveCode personal license? Are you a beginner, hobbyist or educator that's new to LiveCode? This forum is the place to go for help getting started. Welcome!

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller

Post Reply
ittarter
Posts: 151
Joined: Sat Jun 13, 2015 2:13 pm

MySQL Special Characters

Post by ittarter » Tue Feb 21, 2017 10:10 am

Hi everyone,

So I recently learned about

Code: Select all

md5digest
which allows me to store sensitive user information (like passwords) into my SQL database without using plain text. Pretty awesome.

However, I'm often getting SQL query errors since the special characters that this creates are causing problems for the SQL query.

Previously I would replace quotes and apostrophes with uncommon but acceptable characters (like tilde) when I was updating the database, and then reverse that process when I was querying the database.

However I'm STILL getting the standard character error. I thought that maybe there were other unacceptable characters besides quotes and apostrophes that could cause problems. Or maybe there's a better way to filter a string destined for a MySQL query, which I don't know about.

Any ideas?

Klaus
Posts: 14198
Joined: Sat Apr 08, 2006 8:41 am
Contact:

Re: MySQL Special Characters

Post by Klaus » Tue Feb 21, 2017 12:48 pm

Hi ittarter,

I think md5digest returns BINARY data, so you better base64encode it before storing
in the db and base64decode back when retrieving.


Best

Klaus

ittarter
Posts: 151
Joined: Sat Jun 13, 2015 2:13 pm

Re: MySQL Special Characters

Post by ittarter » Tue Feb 21, 2017 1:36 pm

Will do, Klaus. Thanks :)

jacque
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 7393
Joined: Sat Apr 08, 2006 8:31 pm
Contact:

Re: MySQL Special Characters

Post by jacque » Tue Feb 21, 2017 7:13 pm

Just a heads up that md5digest was cracked some time ago and is no longer considered secure. You're better off using encrypt.
Jacqueline Landman Gay | jacque at hyperactivesw dot com
HyperActive Software | http://www.hyperactivesw.com

FourthWorld
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 10052
Joined: Sat Apr 08, 2006 7:05 am
Contact:

Re: MySQL Special Characters

Post by FourthWorld » Tue Feb 21, 2017 10:30 pm

@Jacque: how do we get a hash from the encrypt command?

@ittarter: Jacque's right about moving beyond md5 for hashing. It's useful for certain sorting tasks, but no longer considered a good cryptographic hash.

Slightly better is LC's sha1Digest function, but it's only slightly better. Both md5digest and sha1Digest return binary values, which you could convert to hex with something like this:

Code: Select all

function CleanHash s
   put sha1Digest(s) into tHash
   get binaryDecode("h*",tHash, tCleanHash)
   return tCleanHash
end CleanHash
But again, neither of those two algorithms are particularly strong.

You'll probably want to use sha256. I've submitted a request to have that included in the engine, but in the meantime one of our community members, the late great Mark Smith, put together a nice library for that and more here:
http://marksmith.on-rev.com/revstuff/fi ... h-Hmac.zip

Even better, although he does provide an sha25Digest function that returns a binary form as LC's hash functions do, he also provides one to get the hex form directly:

Code: Select all

get sha256.hex("something")
Of course even the best hash algo won't be very safe without good salting. There are many useful articles about current salting methods; this one seems pretty good:
https://www.codeproject.com/Articles/70 ... g-it-Right

For a quick way to generate unique salt strings of a reasonable length see the uuid function.
Richard Gaskin
LiveCode development, training, and consulting services: Fourth World Systems
LiveCode Group on Facebook
LiveCode Group on LinkedIn

jacque
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 7393
Joined: Sat Apr 08, 2006 8:31 pm
Contact:

Re: MySQL Special Characters

Post by jacque » Tue Feb 21, 2017 11:41 pm

@Jacque: how do we get a hash from the encrypt command?
If it's just something small like a password I don't bother. But I suppose you could run that through md5.
Jacqueline Landman Gay | jacque at hyperactivesw dot com
HyperActive Software | http://www.hyperactivesw.com

MaxV
Posts: 1580
Joined: Tue May 28, 2013 2:20 pm
Contact:

Re: MySQL Special Characters

Post by MaxV » Thu Mar 09, 2017 1:45 am

You can work directly with binaries with MySQL and livecode, without using bas64encode.
Read here the examples for SQLite working with binaries: http://livecode.wikia.com/wiki/SQLite
You can you the same code for MySQL.
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w

Post Reply