Page 1 of 1
MySQL Special Characters
Posted: Tue Feb 21, 2017 10:10 am
by ittarter
Hi everyone,
So I recently learned about
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?
Re: MySQL Special Characters
Posted: Tue Feb 21, 2017 12:48 pm
by Klaus
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
Re: MySQL Special Characters
Posted: Tue Feb 21, 2017 1:36 pm
by ittarter
Will do, Klaus. Thanks
Re: MySQL Special Characters
Posted: Tue Feb 21, 2017 7:13 pm
by jacque
Just a heads up that md5digest was cracked some time ago and is no longer considered secure. You're better off using encrypt.
Re: MySQL Special Characters
Posted: Tue Feb 21, 2017 10:30 pm
by FourthWorld
@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:
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.
Re: MySQL Special Characters
Posted: Tue Feb 21, 2017 11:41 pm
by jacque
@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.
Re: MySQL Special Characters
Posted: Thu Mar 09, 2017 1:45 am
by MaxV
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.