I came to know the SQL truncation vulnerability that I wasn't aware of. It is my understanding that this vulnerability is based on the fact that MySQL truncates the value of an "insert query" whether the value exceeds the width of the corresponding column. The fist appearance of this vulnerability is in CVE-2008-4106 which reports the problem in the WordPress CMS. The CVE reports that the problem was related to a WordPress implementation. But I would have said that this is a problem related to the MySQL implementation that truncates the input instead of raising an error and blocking the query.

So is SQL truncation vulnerability a problem in the DBMS (MySQL) or in the web application that doesn't check the string length?

I installed the latest version of MySQL (5.5) in order to make some tests and apparently if the inserted value exceeds the column width, I receive an error:

Error: Data too long for column 'name' at row 1 

and the query is not performed. This suggests me that MySQL now implements a better check for input length and thus the web application shouldn't be responsible for this check. What am I missing?

  • Maybe strict mode was off in the vulnerable version? stackoverflow.com/q/18459184/413180 – SilverlightFox Jun 28 '14 at 11:26
  • Well probably yes, I must have missed that feature of MySQL and wasn't able to understand what was going on. Just tried on my installation and it now behaves as expected. thank you very much :) – Federico Jun 30 '14 at 13:05

I answer my own question thanks to @SilverlightFox.

MySQL has two modes it can operate: STRICT_TRANS_TABLES and STRICT_ALL_TABLES and quoting MySQL doc:

Strict mode controls how MySQL handles invalid or missing values in data-change statements such as INSERT or UPDATE. A value can be invalid for several reasons. For example, it might have the wrong data type for the column, or it might be out of range. A value is missing when a new row to be inserted does not contain a value for a non-NULL column that has no explicit DEFAULT clause in its definition. (For a NULL column, NULL is inserted if the value is missing.)

If the two modes are disable, MySQL automatically truncates inserted string in the case they exceed the column length. So the behaviour of MySQL is "legitimate" and the reason why I should check the length of input in the web application is because I should not rely on the fact that MySQL has STRICT_TRANS_TABLES and STRICT_ALL_TABLES enabled.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.