[SOLVED] Ways MySQL/MariaDB could silently be changing values when storing

Ways MySQL/MariaDB could silently be changing values when storing

I'm searching for cases in MySQL/MariaDB where the value transmitted when storing will differ from the value that can be retrieved later on. I'm only interested in fields with non-binary string data types like VARCHAR and *TEXT.

I'd like to get a more comprehensive understanding on how much a stored value can be trusted. This would especially be interesting for cases where the output just lacks certain characters (like with the escape character example below) as this is specifically dangerous when validating.

So, this boils down to: Can you create an input string (and/or define an environment) where this doesn't output <value> in the second statement?

INSERT INTO t SET v = <value>, id = 1; // success
SELECT v FROM t WHERE id = 1;

Things I can think of:

strings containing escaping (\a → a)
truncated if too long
character encoding of the table not supporting the input

If something fails silently probably also depends on how strict the SQL mode is set (like with the last two examples).

Thanks a lot in advance for your input!

Solution

you can trust that all databases do, what the standards purpose, with strings and integer it is simple, because it saves the binary representation of that number or character in your choosen character set.

Decimal Double and single values are different, because the can't be saved directly and so it comes to fractals see decimal representation

That also follows standards, but you have to account with it.