SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    constraints question

    Should a well-designed db have its constraints set to default/restrict and if changes need to be made to the db, by any script, then that script should change the constraint as necessary?

    Then having done its update or other work, should the script then re-establish the constraint, restrict?

    I ask because if a constraint is always set to update etc, then some wrong data or even a deletion might bust the db data.

    bazz

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    presumably you are talking only about foreign key constraints (there are other kinds)

    i don't see the difference between (a) having a foreign key constraint set to restrict, but allowing a script to disable the constraint, make changes, and then restore the constraint, and (b) having a foreign key constraint set to cascade, and allowing a script to make changes

    both scenarios can mess up your data just as surely
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks rudy,

    So, is it better to keep the FK constraint set to restrict and to make the script logic amend both the child and the parent records as necessary, instead of relying on the FK constraint to cascade the changes?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    no
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    That is a sure sign that the database and application logic is not properly established. Having application code turn on and off a foreign key… that is some type of madness. Additionally, having application code modify table structures after installation is a sign of bad things to come.
    The only code I hate more than my own is everyone else's.

  6. #6
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks oddz and rudy,

    I don't have any scripting established which would do that.

    Likely, my question arose from over-thinking. I had a concern that if someone were to find a way to delete a table or some records, the on delete cascade constraint would remove data through numerous tables. I wanted to find a balance between utilising constraints and preventing such a deletion.

    I do have the connections set up with the appropriate permissions. And I have the scripts (which allow user input), written only to allow sanitised data input. Indeed all params passed between scripts is sanistised or rejected. Those which don't invite/support user-generated input are connected with read only privileges so messing with params being passed from page to page, won't input anything.

    I suppose I should only use a fk constraint which allows on update cascade given that, deleting could be performed on each table, from child upwards. Indeed, preventing deletion by archiving the 'old' data to another table might be better too.

    Is my concern explained well enough? Does it sound like I have addressed or am at least aware of the issues to be secured and addressed?

    bazz

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    if all user interaction with the database is through your application code, and you do not let users get at your database through, for example, the mysql command line or a front-end tool like phpmyadmin, then you have no reason to worry about "someone [finding] a way to delete a table or some records"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    useful reassurance rudy. Thanks.

    My sole aim on this subject is to prevent anyone from breaking in and damaging the db. Security isn't perhaps a topic for this forum so I'll go look for another that may be appropriate.

    bazz

  9. #9
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    Actually security precautions can be taken at the database level to minimize the chances of such risks. People always talk about injection but the truth of the matter is under ideal circumstances the user should only have privileges to carry out tasks which they are allowed to. For example, it is highly unlikely that unless something is being installed schemas will need to change. So application side one should probably connect with a user who has restricted permissions to do so. Also, hypothetically it would be unlikely that an unauthenticated user would need to delete items (not always the case but hypothetically). Granted injection prevention is still a very important part there are things that can be done at a lower level to prevent the database from being compromised given an injection attack takes place. the details of which will always be specific to the business goals and model in question though.
    The only code I hate more than my own is everyone else's.

  10. #10
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks oddz.

    I hope I have achieved the reality that the only changes would be made by authenticated access to the cms. (no login user has delete privileges now but they do have insert and update privileges and I think I have all the inputs sanitised).

    But I wonder if someone may have malware in their pc and have their passwords compromised - what then?

    Someone not authorised could access the db using genuine passwords and do damage by updating existing records with perhaps empty data or worse.

    Of course that would be the users fault for having their details compromised but they aren't necessarily computer whizzes so I need to be gentle.

    so it looks like securing passwords is where I need to be checking.


    bazz

  11. #11
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    when I say securing pwds, I mean setting a monitoring service in place to check usage and also, another, to workaround using the email address to which new pwds may be sent in cases where they need to be updated.


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
  •