Encode (mariadb) created "Incorrect string value"

(3 posts) (2 voices)


No tags yet.

  1. duane, Member


    I am trying to use 'encode' (and decode) in mariadb/mysql to pass an encrypted string. However when I do, myDBR displays an error:
    "Incorrect string value: '\x8Ep\x06U\x0C\xE8...' for column ``.``.`vEncoded` at row 1"
    which might be the encoded string not being parsed properly in php/myDBR (or might be something else unrelated!).

    You can replicate this with:

    CREATE DEFINER=`myuser`@`localhost` PROCEDURE `sp_DBR_test_encode`(inLogin varchar(100)) BEGIN

    DECLARE vDecoded varchar(75) DEFAULT sha2("qwerty", 256); DECLARE vEncoded varchar(75) DEFAULT '';

    SELECT vDecoded, ENCODE(vDecoded, "!myd6r") AS strEncoded, DECODE(ENCODE(vDecoded, "!myd6r"), "!myd6r") AS strDecoded; SET vEncoded = ENCODE(vDecoded, "!myd6r");


    This might be a mariadb/mysql character set setting issue, but I've tried a few things (change the server/system character set) that might have helped and it made no difference, so I'm also wondering if it is a utf8 to utf8mb4 issue in myDBR.

  2. myDBR Team, Key Master

    What you are trying to do is to assign a binary string (result of the ENCODE) to a variable with datatype of utf8mb4. As the binary string contains invalid utf8mb4 data, you will get an error from the database. myDBR just shows the error.

    What you should do is to declare the vEncoded-variable to be a binary and match the length of it:

    DECLARE vEncoded binary(100);

    myDBR Team

  3. duane, Member

    Wow - missed that aspect of encode! Thanks for the pointer.

    I'll try that (and looks like I'd need to convert it to/from base64 to pass through the query string as a u parameter).

    Based on some early experimentation I've found using blob instead of binary works to produce a shorter base64 rendering of the encode string. Your hints helps me find the solution I need - thanks!


You must log in to post.