SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,931
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    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?!

    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...
    Code:
    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...
    Code:
    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...
    Code:
    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...
    Code:
    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?!

    Sincerely,


    Debbie

  2. #2
    SitePoint Mentor silver trophybronze trophy
    Mikl's Avatar
    Join Date
    Dec 2011
    Location
    Edinburgh, Scotland
    Posts
    1,606
    Mentioned
    66 Post(s)
    Tagged
    0 Thread(s)
    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

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Mikl View Post
    It won't store a numeric value in an integer field, of course, nor the other way round.
    could you rephrase this a bit? perhaps you a word out
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Mentor bronze trophy
    John_Betong's Avatar
    Join Date
    Aug 2005
    Location
    City of Angels
    Posts
    1,889
    Mentioned
    74 Post(s)
    Tagged
    6 Thread(s)
    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.
    Learn how to be ready for The New Move to Discourse

    How to make Make Money Now with a *NEW* look

    Be sure to congratulate Wolfshade on earning Member of the Month for August 2014

  5. #5
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,931
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by John_Betong View Post
    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

  6. #6
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,931
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Mikl View Post
    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
    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

  7. #7
    SitePoint Mentor bronze trophy
    John_Betong's Avatar
    Join Date
    Aug 2005
    Location
    City of Angels
    Posts
    1,889
    Mentioned
    74 Post(s)
    Tagged
    6 Thread(s)
    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.




    Quote Originally Posted by DoubleDee View Post
    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
    Last edited by John_Betong; Oct 21, 2013 at 00:02. Reason: spelling and brackets: not my fortay
    Learn how to be ready for The New Move to Discourse

    How to make Make Money Now with a *NEW* look

    Be sure to congratulate Wolfshade on earning Member of the Month for August 2014

  8. #8
    SitePoint Mentor silver trophybronze trophy
    Mikl's Avatar
    Join Date
    Dec 2011
    Location
    Edinburgh, Scotland
    Posts
    1,606
    Mentioned
    66 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Mikl View Post
    It won't store a numeric value in an integer field, of course, nor the other way round.
    Quote Originally Posted by r937 View Post
    could you rephrase this a bit? perhaps you a word out
    My mistake. I should have said that it won't store a character value in an integer field.

    Mike

  9. #9
    SitePoint Mentor silver trophybronze trophy
    Mikl's Avatar
    Join Date
    Dec 2011
    Location
    Edinburgh, Scotland
    Posts
    1,606
    Mentioned
    66 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    Are there any downsides to running in STRICT ALL TABLES mode??
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •