Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > BOOK: Beginning Database Design Solutions ISBN: 978-0-470-38549-4
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
BOOK: Beginning Database Design Solutions ISBN: 978-0-470-38549-4
This is the forum to discuss the Wrox book Beginning Database Design Solutions by Rod Stephens; ISBN: 9780470385494
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Beginning Database Design Solutions ISBN: 978-0-470-38549-4 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old May 8th, 2012, 03:39 PM
Authorized User
Points: 143, Level: 2
Points: 143, Level: 2 Points: 143, Level: 2 Points: 143, Level: 2
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2012
Posts: 32
Thanks: 9
Thanked 0 Times in 0 Posts
Default How to avoid the injection for an intersection table.

We have Jobs, Employees, Employers and JobsEmployees. When somebody applies to a job we record in JobsEmployees the ID of the job and the ID of the employee.

JobsEmployees
--------------
JobID FK PK
EmployeeID FK PK

It is not necessary to record the employer ID in this table, but in order to prevent injection what would be a good practice here?

The problem will be that any employee may change the parameter passed in url for jobid, this way seeing the applicants for jobs not posted by them.

I am not sure if it makes more sense (a) to add a field EmployerID to JobsEmployees or (b) before the main query on JobsEmployees to add another query that will check if the jobid belongs to employerid; (c) use a join to link the Employer table and check if the jobid belongs to employerid.

I am tempted to use (a) as it seems to be the last costly operation. I am not sure of the consequences in the long term. For example, if I read the same table from the employer side, I do not need the EmployerID.
Reply With Quote
  #2 (permalink)  
Old May 9th, 2012, 10:32 AM
Rod Stephens's Avatar
Wrox Author
Points: 3,141, Level: 23
Points: 3,141, Level: 23 Points: 3,141, Level: 23 Points: 3,141, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2006
Location: , , .
Posts: 640
Thanks: 2
Thanked 96 Times in 95 Posts
Default

I don't think I see how changing the job ID would let you see other applicants. Wouldn't it just make you apply for a different job and not the one you want? I mist be missing something.

I'm not completely sure I see all of the consequences. If you go with (a), I think you would need to add the requirement that job IDs never change and are never reused. Otherwise it could be a problem if a second employer uses a previously used job ID. I don't think that requirement is unreasonable.

You could encrypt the IDs in the URL so users would have a hard time generating valid encrypted IDs. It probably wouldn't need to be a very complicated encryption scheme to prevent most users from messing with the IDs.
__________________
Rod

Rod Stephens, Microsoft MVP

Essential Algorithms: A Practical Approach to Computer Algorithms

(Please post reviews at Amazon or wherever you shop!)
Reply With Quote
  #3 (permalink)  
Old May 9th, 2012, 12:56 PM
Authorized User
Points: 143, Level: 2
Points: 143, Level: 2 Points: 143, Level: 2 Points: 143, Level: 2
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2012
Posts: 32
Thanks: 9
Thanked 0 Times in 0 Posts
Default

If a constraint at the database level prevent things like duplicate entries into a table, do I have to write the same logic at the application level?

For example, let's say an Employee try to add the same skill twice, the database will prevent this to happen. Depending on how we set the action it may throw an error or not. Now I want the user to be able to see that he already added that skill and I was wondering if

a) I will have to write the entire logic independent of database

like before the INPUT query I have to make a SELECT query to see if the skill is or not in the database (specific table) and only if it's not to run the INPUT, else to display a message.

b) I will have to write only a part and work with the database

like I make just the select query, if the database throw an error I catch it and display the message.

c) I do not have to write anything, simply set the constraint to "no action" and discharge the INPUT silently.

Until now I wrote everything as (a), but now I am wondering if with all the power the db gave me... maybe I can cut some corners or maybe writing all like (a) it is just a waste. Until this project, I didn't worked with constraints, so all the logic went at the programming language level. Basically the database has the same constraints that may be found in the code, so the constraints in database act like a safety mechanism. Maybe this is wrong and I can safely do (b) or (c) or other things that I didn't think before.
Reply With Quote
  #4 (permalink)  
Old May 10th, 2012, 04:03 PM
Rod Stephens's Avatar
Wrox Author
Points: 3,141, Level: 23
Points: 3,141, Level: 23 Points: 3,141, Level: 23 Points: 3,141, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2006
Location: , , .
Posts: 640
Thanks: 2
Thanked 96 Times in 95 Posts
Default

Quote:
If a constraint at the database level prevent things like duplicate entries into a table, do I have to write the same logic at the application level?
The database is the best place to ensure that problems don't occur. That way if you forget or make an error in the application code, the program still cannot corrupt the database.

Of course the program needs to be able to handle a database error. It can help by preventing errors in the first place. For example, by giving the user a list of choices or check boxes so they cannot enter an invalid choice. That's more work (and it should get its list from the database so you don't need to update the code if the database changes) but it makes for a better user experience.

Quote:
a) I will have to write the entire logic independent of database

like before the INPUT query I have to make a SELECT query to see if the skill is or not in the database (specific table) and only if it's not to run the INPUT, else to display a message.
When the form loads, I would have it query the database to see what options are possible and then make a CheckListBox or series of check boxes so the user cannot add the same skill twice.

Then use error handling just in case something goes wrong despite your best efforts.

Quote:
b) I will have to write only a part and work with the database

like I make just the select query, if the database throw an error I catch it and display the message.
That's the minimum you should do. Start with that and then add the fancier CheckListBox stuff if you can.

Quote:
c) I do not have to write anything, simply set the constraint to "no action" and discharge the INPUT silently.
I wouldn't do that. The user will probably get mad that nothing seems to be happening but he doesn't know why.

Quote:
Until now I wrote everything as (a), but now I am wondering if with all the power the db gave me... maybe I can cut some corners or maybe writing all like (a) it is just a waste.
I start with (b). Then add (a) if you can. Just be sure to get the information about what choices are possible from the database instead of hard coding that into the program. That way if you add or remove a choice, the program automatically adjusts so you don't need to change the code in both places.
__________________
Rod

Rod Stephens, Microsoft MVP

Essential Algorithms: A Practical Approach to Computer Algorithms

(Please post reviews at Amazon or wherever you shop!)
Reply With Quote
The Following User Says Thank You to Rod Stephens For This Useful Post:
masterlayouts (May 10th, 2012)
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
finding intersection points of lines eolinnez Excel VBA 0 February 17th, 2010 01:28 AM
sql injection trufla Classic ASP Basics 2 June 16th, 2008 02:54 PM
Code Injection DaveGerard Classic ASP Professional 2 March 6th, 2008 06:04 PM
SQL Injection cygnusx04 Classic ASP Databases 1 November 6th, 2004 10:06 AM
What SQL Injection is ? minhtri Classic ASP Basics 2 October 20th, 2004 10:11 PM



All times are GMT -4. The time now is 11:15 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.