MySQL Data Integrity issues!

Okay, I am officially FREAKED OUT by what I have been witnessing with my database this past week.

It seems like there is NO DATABASE INTEGRITY as far as MySQL honoring attributes like Data-Type, Field-Size, Nullable, etc.

Here are some examples…

Example #1: (Working on this one now, and not sure what to do?!)
I just added a new field to my “member” table to log how long it takes a User (or Bot) to register.

I have a field called “registration_time” that is a SMALLINT, Null=No.

As an error-check, I tried to INSERT a record where registration_time = 99999.

You would expect that to fail, but my PHP script ran just fine.

When I looked in the table with phpMyAdmin, registration_time = 0?! :eek:

What is going on??

Example #2:
In my “member” table is another field called “photo_approved” which is a TINYINT, Null=No, Default=0.

If I run this query…


UPDATE member
SET photo_approved = '8888', last_activity = now()
WHERE id = 57

The query runs without any errors, and photo_approved = 127

How is that possible?!

Example #3:
Similar to above, when I run this query…


UPDATE member
SET photo_approved = 'xxx', last_activity = now()
WHERE id = 57

…there are no errors.

And photo_approved = 0?!

Example #4:
In the “member” table, “id” is an Integer, Not Nullable, AutoIncrement, PK.

If I run this query…


UPDATE member
SET id=NULL, last_activity=now()
WHERE id=57;

…the query runs without issue. And id=0

Example #5:
In the “member” table, there is an “email” field, which is VARCHAR(80), Null=No, UK.

If I run this query…


UPDATE member
SET email=NULL
WHERE id=61

…it runs without error. And email=NULL

How can this happen?

Bottom-line is that MySQL seems to be letting my PHP script get away with murder as far as what gets put into the database?! If that’s the case, then why not make every field “TEXT” and call it a day?! :rolleyes:

Sincerely,

Debbie

Hi Debbie,

What you are seeing is in fact the default behaviour. By default, MySQL will try to accept whatever data you try to store in a table. It won’t store a numeric value in an integer field, of course, nor the other way round. By it will try to coerce the value that you give it into the target data type.

In your first example, 99999 is too big for a smallint, so MySQL changed it to zero. In the second example, the value ‘8888’ (in quotes) is invalid for a tinyint, so MySQL converted it to 8888 numeric. But that’s too high for a tinyint, so it got truncated to 127.

Now, if you want to know why, in one case, the too-high value was truncated, and in the other it was set to zero, the answer’s easy: I don’t know. But that’s what it does.

The solution is to set the sql_mode system variable to indicate that you want a more restrictive mode. There are several possible modes to choose from, including TRADITIONAL and STRICT ALL TABLES. This article gives more details.

Mike

could you rephrase this a bit? perhaps you a word out

It appears as though rather than fail the programmers appear to insert default values.

After running your tests you are now aware of the essential validation checks before posting.

GIGO springs to mind.

I validate the hell out of may IN and OUT data in PHP, but that doesn’t mean jack if it gets bypassed…

Good Systems protect data on the Front-End and on the Back-End which is what this thread is about.

Debbie

Are there any downsides to running in STRICT ALL TABLES mode?? (For example, too many “false positives” or totally upending my current code.)

I can’t use that on my old MacBook, but once I get finished buiding my new MBP with the latest MAMP, I should be able to try that out.

Although I’m a little nervous about turning something like that on after I have already tested most of my code-base… :-/

Sincerely,

Debbie

First may I clarify any possible misunderstanding - I previously posted:

It appears as though rather than fail the programmers appear to insert default values.

When I meant to say was:

It appears as though rather than fail the MYSQL programmers appear to insert default values.

I re-read your original post and unsure where you are inserting your tests. It appears that with Example#1, the PHP post validation fails and allows the value 99999 to be passed to MYSQL. Subsequently MYSQL inserts a default value within the bounds specified in the table criteria of SMALLINT, Null=No.

GIGO is still applicable since the PHP post validation should have failed and not allowed invalid user data.

My mistake. I should have said that it won’t store a character value in an integer field.

Mike

Not that I know of. But that’s not to say that it might not impact other code elsewhere in the application.

But, even if you do set a more restrictive mode, that doesn’t avoid the need to validate the user-entered data. You shouldn’t rely on your database to trap data errors. You should take control of that yourself, in your application code.

In general, you should validate the actual range of values demanded by your business rules, not those imposed by your choice of data type. For example, if you want the user to enter a month number, you would check that the entered value is in the range 1 - 12 (because those are the only permitted values), not 0 - 127 (because you chose a tinyint for the data, and those are the values supported by that data type). And you should then generate an error message that relates to the failure of the business rule, not to the error condition detected by the database.

In fact, it’s a good rule to always validate any data that the user enters before it hits the database. (Whether you do that on the client side, the server side, or both, is a separate issue.)

Mike