1

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!

mvo
  • 1,138
  • 10
  • 18

1 Answers1

1

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.

nbk
  • 45,398
  • 8
  • 30
  • 47
  • Thanks for your answer. I agree with what you say about the standards - I'm just curious if I'm overlooking something (like you could with the escape character where the strings won't match) or practical usages where a misinterpretation is easy to happen. – mvo Jun 18 '20 at 16:04
  • i rarely escape characters, and prepared statements help also, do avoid them, the rest is a lizzle bit of programming for the rare cases, where it fails, so try to use prepared statements and double quotes for strings(for o'brian and so also when double qutoes are unavoudable, use char(34) all sting convertion understand that – nbk Jun 18 '20 at 16:21
  • Sure, I already do that and use abstractions. My question was more from a security point of view. No matter what: thanks for your time + response. :-) – mvo Jun 19 '20 at 09:34
  • @mvo - "security" -- read all about "SQL Injection". Something as simple as ";" (semi-colon) can open a door for a hacker! – Rick James Jun 19 '20 at 17:09
  • Thanks, I'm am well aware of this. – mvo Jun 20 '20 at 20:05