7

Let's say that I have a html form (actually I have an editor - TinyMCE) which through PHP inserts a bunch of text into Mysql table.

I want to know the following:

If I have TINYTEXT data type in Mysql column - what happens if the user tries to put more text than 255 bytes into Mysql table??

Does the application save first 255 bytes and "cuts off" the rest? Or does nothing get saved into Mysql table and mysql issues a warning?? Or none of the above?

Actually, what I want and intend to do is the following: Limit the size of user form input by setting the column data type in Mysql to TEXT data type, which can hold maximum of 64 KB of text. I want to limit the amount of text that gets passed from user to database, so that user can't put too much data to the server at once.

So, basically, I want to know what happens, if the user puts more text through TinyMCE editor than 65535 bytes, assuming TEXT data type in mysql table.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Matjaž Lojen
  • 75
  • 2
  • 5
  • And I also want to know, IF mysql server's reaction is the same for ANY data type (TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT) that gets too much data. Or are there differences? – Matjaž Lojen Sep 13 '12 at 20:55
  • Hehe... I'm building a small custom CMS and I'm not there yet... I mean I haven't yet included TinyMCE and designed the entire database. I'm just thinking ahead for potential problems and potential solutions, so I tried to save myself some time by asking this question instead of trying it out... – Matjaž Lojen Sep 13 '12 at 22:04
  • And answers could be useful for someone else too. – Matjaž Lojen Sep 13 '12 at 22:06

3 Answers3

4

MySQL, by default, truncates the data if it's too long, and sends a warning.

SHOW WARNINGS;
Data truncated for foo ..

Just to be clear: the data will be saved, but you will be missing the part that was too large.

Nim
  • 631
  • 6
  • 12
1

Default mysql configuration truncate the data if the value is greater than the maximum table field definition size, this will produce a non blocking warning.

If you want a blocking error you have to set the sql_mode to STRICT_ALL_TABLES

dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html#sqlmode_strict_all_tables

IMHO the best way is to manage this error via applicatin software.

Hope this helps

Cristian Porta
  • 4,393
  • 3
  • 19
  • 26
0

If you enter too much data to a TEXT field in MySQL it will insert the row anyway but with that field truncated to the maximum length, and issue a warning.

Even if MySQL did prevent the row from being added it would not be a good way of limiting the length of data that a user can enter. You should check the length of the POSTed string in PHP, and not run the query at all if it is too long - and perhaps tell the user why their data wasn't entered.

As well as this you can prevent the user from entering too many characters at the client side (although you should always do the check server side as well because someone could bypass the client side limit). It appears that there is no built-in way of doing this in TinyMCE, but it is possible by writing a callback: Limit the number of character in tinyMCE

Community
  • 1
  • 1
gandaliter
  • 9,863
  • 1
  • 16
  • 23