Automatically trimming length of string submitted to MySQL

Question

When I submit a form field to a mySQL database is there a way to set the database to automatically discard any data in excess of the data field length?

I know I can do it programatically, but can the database be set to discard the excess without throwning an error?

EDIT for clarity

heres my insert statement

<cfquery datasource='#arguments.dsn#' name="addPatient">        
                INSERT INTO patients(patientFirstname
                                ,patientLastname
                                ,nhsNumber
                                ,patientDOB
                                ,patientTitle
                                ,address1
                                ,address2
                                ,address3
                                ,address4
                                ,postcode
                                ,patientPhone1
                                )
                VALUES (<cfqueryparam value="#arguments.patientFirstname#" cfsqltype="CF_SQL_LONGVARCHAR"/>
                        ,<cfqueryparam value="#arguments.patientLastname#" cfsqltype="CF_SQL_LONGVARCHAR"/>
                        ,<cfqueryparam value="#arguments.nhsNumber#" cfsqltype="CF_SQL_LONGVARCHAR"/>
                        ,<cfqueryparam value="#arguments.patientDOB#" cfsqltype="CF_SQL_TIMESTAMP"/>
                        ,<cfqueryparam value="#arguments.patientTitle#" cfsqltype="CF_SQL_LONGVARCHAR"/>
                        ,<cfqueryparam value="#arguments.address1#" cfsqltype="CF_SQL_VARCHAR"/>
                        ,<cfqueryparam value="#arguments.address2#" cfsqltype="CF_SQL_LONGVARCHAR"/>
                        ,<cfqueryparam value="#arguments.address3#" cfsqltype="CF_SQL_LONGVARCHAR"/>
                        ,<cfqueryparam value="#arguments.address4#" cfsqltype="CF_SQL_LONGVARCHAR"/>
                        ,<cfqueryparam value="#arguments.postcode#" cfsqltype="CF_SQL_LONGVARCHAR"/>
                        ,<cfqueryparam value="#arguments.patientPhone1#" cfsqltype="CF_SQL_VARCHAR"/>
                        )
            </cfquery>

the table field patientPhone is VARCHAR(20)

If I dont validate the submission progamatically and just bang in a form value 30 characters long I error out (when what i thought it would do is simply store the first 20 characters)

Data truncation: Data too long for column 'patientPhone1' at row 8

When I set up the db with the wizard I remember selecting innodb and transactional and traditional emulation (which was recommended if i remember correctly)

score 12 · Answer 1 · answered Jul 06 '10 at 13:07

12

MySQL by default does this already! It shouldn't in my view, but it does.

If it is giving you error messages instead of just truncating the data, then perhaps traditional mode is on. Run SELECT @@SESSION.sql_mode and see what it says. If it spits out

STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER

then it is in traditional mode; issue SET SESSION sql_mode='' every time you connect, or connect using an account with SUPER privileges and issue SET GLOBAL sql_mode='modes'.

answered Jul 06 '10 at 13:07

Hammerite

21,755
6
70
91

I get STRICT_TRANS_TABLES, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION so i guess the first bit says its traditional mode? If I log on as superuser and do the set global sql_mode='' thinking that would cancel the modes but if i then run SELECT @@SESSION.sql_mode i get exactly the same output – Saul Jul 06 '10 at 13:23
1

Yes, `STRICT_TRANS_TABLES` implies that in most situations, MySQL will give an error message where it would normally truncate input. If you can't set the global value of sql_mode then it's best to automate the task of setting the session SQL mode every time you connect. Although my preference would be to have the database running in traditional mode and to check the length of input in the application logic. – Hammerite Jul 06 '10 at 13:33
even if I clear out my sql_mode to '' if I put in a string thats too long I get the same error – Saul Jul 06 '10 at 14:00

score 12 · Answer 2 · answered Jul 06 '10 at 13:18

You mean you want to set this in a data definition script for example when creating a table? According to the documentation you can't do this. Perhaps you could use a trigger to handle data before it is inserted? Something like this (I can not garantuee that the code below actually works):

CREATE TRIGGER chopdata BEFORE INSERT ON mytable
  FOR EACH ROW BEGIN
    NEW.myfield = SELECT SUBSTRING(NEW.myfield,1,20)
  END;

Else you can chop out the maximum length by using substring straight in your query/procedure:

SELECT SUBSTRING('your very long string goes here',1,20);

Itay Moav -Malimovka · Answer 3 · 2010-07-06T14:36:09.497

-1

Just set the DB field size to the max size you want to accept.
if you want max 20 chars, then set it to varchar(20) or char(20).
Just be careful with the utf8 inputs, some of them characters takes 2 places instead of one.
But, as you can read in comments. Choosing a utf8 charset will solve this.

If you are speaking about white spaces...Then usually you are suppose to use only one place in your code to do the insert/update/delete queries. In this place you can easily apply programmaticlly any filter you want, and be sure it will be applied to all of your inserts.

edited Jul 06 '10 at 14:36

answered Jul 06 '10 at 13:02

Itay Moav -Malimovka

52,579
61
190
278

3

If you set charset of field to utf-8, it accepts 20 characters instead of 20 bytes. – Naktibalda Jul 06 '10 at 13:05
Also, note that UTF-8 characters might take up to 4 bytes. Not that that should be a problem in this case, as Naktibalda said. – Christoffer Hammarström Jul 06 '10 at 13:37
3

4-byte UTF-8 characters are only accepted in MySQL 5.5 and later if you use the character set "utf8mb4". In previous versions and with the UTF8 character set, MySQL would give an error about bad input characters. – thenickdude May 27 '13 at 01:39

Automatically trimming length of string submitted to MySQL

3 Answers3

Linked