0

i've read a lot of posts concerning similair problem with utf-8 issues and tried some too but i can't find the cause.

I'm using livecode and i want to encrypt some strings to a database. So i encrypt in Livecode then base64encode then send to database via PHP/PDO. encrypt-->base64encode-->base64decode-->decrypt within livecode works ok.

Now when i send the base64encoded data to the MariaDB database it saves it, except + has become a space. The database, table and columns are all utf8mb4_unicode_ci. If i change the space to + manually via phpmyadmin in the database and read out with Livecode then it base64decodes-->decrypts correct!

This are the php files i use to connect and update the db:

<?php
// the connect.php file
$servername = "localhost";
$username = "blabla";
$password = "blabla";

try {
    //$db = new PDO("mysql:host=$servername;dbname=blabla",$username, $password);
    $db = new PDO("mysql:host=$servername;dbname=blabla;charset=utf8", $username, $password);
    //$db = new PDO("mysql:host=$servername;dbname=blabla;charset=utf8mb4", $username, $password,array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci'"));
    // set the PDO error mode to exception
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    //echo "Connected successfully";
}
catch(PDOException $e)
{
    echo "Connection failed: " . $e->getMessage();
}
?>

you can see i tried some charsets in the connect file also

<?php
//this is the file to update the DB
require_once 'connect.php';
//
try {
    $stmt = $db->prepare("UPDATE tabel_users SET user=:user,
    password=:password, email=:email, userlevel=:userlevel WHERE
    id_user=:id_user");
    $stmt->bindParam(':id_user', $_POST['id_user'], PDO::PARAM_INT);
    $stmt->bindParam(':user', $_POST['user'], PDO::PARAM_STR);
    $stmt->bindParam(':password', $_POST['password'], PDO::PARAM_STR);
    $stmt->bindParam(':email', $_POST['email'], PDO::PARAM_STR);
    $stmt->bindParam(':userlevel', $_POST['userlevel'], PDO::PARAM_STR);
    //$stmt->bindParam(':user', $_POST['user'], PDO::PARAM_LOB);
    //$stmt->bindParam(':password', $_POST['password'], PDO::PARAM_LOB);
    //$stmt->bindParam(':email', $_POST['email'], PDO::PARAM_LOB);
    //$stmt->bindParam(':userlevel', $_POST['userlevel'], PDO::PARAM_LOB);
    $affected_rows = $stmt->rowCount();

    if($stmt->execute()) { echo "Ge-update informatie verzonden naar de
    database!"; } else { echo "Failure!"; };

}
catch(PDOException $e)
{
    echo "Not updated: " . $e->getMessage();
}

//var_dump($_POST)
$db = NULL;

?>

Also tried PDO::PARAM_LOB Tried VARCHAR VARBIN BLOB but this did not change a thing.

My first guess was that Livecode does something weird while posting it to the php file. But checking the variable just before it is send has the + in the string to send. So i don't really get it where it goes wrong.

Artjom B.
  • 58,311
  • 24
  • 111
  • 196
jjsjjs
  • 11
  • 1
  • 5
  • Just a note of caution. Storing passwords encrypted in your database is always a bad idea! What you should store is a one way salted hash of the passwords. There is also a new messageDigest coming in LC9 that will allow more secure hashes as both md5 and sha1 is too weak for today's number crunching computers. – hliljegren Apr 21 '17 at 03:36
  • Why is the encrypted password in a database a bad idea? Yes i know the new messageDigest is coming, it was shifted from dp6 to dp7. So have to wait a little more. – jjsjjs Apr 21 '17 at 17:05
  • Ok did some more reading about hashing instead of encrypting the password, it's a one way solution, as you can't "de-hash" it. thanks! – jjsjjs Apr 21 '17 at 17:22

3 Answers3

3

Your issue is not with the database it is that php is url decoding your base64 string, that will convert '+' to ' ' (plus to space). You need to urlencode your password parameter before posting it. Here's the LiveCode doc for URLEncode.

It depends on the content type of your post as explained in more detail here.

Community
  • 1
  • 1
Dan Revel
  • 124
  • 6
  • By the way, i forgot to mention, Updating the table directly from Livecode (without PHP files in between) then it works correct. But i need some others to use it too – jjsjjs Apr 21 '17 at 17:26
  • Yes! This seems to work just great! --> Encrypt-->Base64Encode-->URLEncode (within Livecode) --> Post to DB via PHP/PDO. Then Get from DB via PHP/PDO --> (URLDecode not used) -->Base64Decode-->Decrypt. When i use URLDecode in LC then it is not working ok, without URLdecode it is OK. Thank you all! – jjsjjs Apr 21 '17 at 20:19
  • The reason is probably that you're basically submitting a web form to PHP. Web forms escape spaces as +. So if you want to transmit a "+", you need to URL-encode it to %2B, which won't accidentally be converted to a space. – uliwitness Apr 21 '17 at 23:11
  • indeed i added URLEncode as you can see in my answer above. – jjsjjs Apr 27 '17 at 20:49
0

I had the same problem but going from PHP to livecode and I got as far as concluding that the problem was with php encryption vs livecode encryption. if you try base encoding without encryption they will play nice. I don't have an answer for it though, but I remember the experts saying its got to do with the "header" of the encrypted binary. ideally i would like to see an answer to this problemnas I ended up going without encryption to get around it.

makeshyft_tom
  • 175
  • 8
  • 1
    Please see my solution above, i added URLEncode after base64Encode, but i did not use URLDecode before base64decode. This way it is Encrypted, base64encoded and URLEncoded. It then works correct. Did several tests now, and all ok. – jjsjjs Apr 27 '17 at 20:51
-1

When I use base64 encoding to share data with another system （javascript）I always use this code after encoding:

Replace space with empty in tEncodedData.

LC adds spaces for base64encode. It works fine when I remove them.

Jonathan
  • 1