• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 533
  • Last Modified:

Need to look up descriptions from a list of IDs

I have a field in my Customers table called Skills.  The customer could have more than one skill so I store the Skill_IDs as a comma separated list in the Skills field.  I also have a Skills table with Skill_IDs and Skill_Descriptions.  After retrieving the comma separated list of Skill_IDs, I need to display them as the Skill_Descriptions.  What is the best approach for this?

My approach was to spit the list into an array, then dynamically create a SQL statement on the Skills table to retrieve the descriptions whilst navigating through the array of Skill_IDs.  However, I am worried about security using this approach so I really need a way of passing the IDs to an sproc to retrieve the Skill_Descriptions.  Note the comma separated list could be anything - eg. 1,2,3 or 3,4 or 4,5,8.
2 Solutions
Personally,  I always find lists of things packed into one field to be a pain!    So if I had control over the data model,  I would implement many-to-many relationships like this via an intersection table e.g. CustomerId, SpecId

Assuming this is not an option,  I further assume that your security concern is related to SQL injection attacks?

If so,  why don't you just modify your current approach,  split the list on the client,   verify that each Id in the list is purely numeric,  then query the database either directly or using a procedure that accepts a single Id as a parameter and returns the description.

Of course, you could implement all of that in the stored procedure and pass in the string.   The important thing is to split the list and verify that each member is a legally formatted Id of a specialty.   That should prevent malicious attack.
Scott PletcherSenior DBACommented:
>> What is the best approach for this? <<

A Customer_Skills table, the "standard" intersection table when you have a potentially multi-valued column (a column should be a SINGLE value, NOT a list).

Required here anyway, since you will need additional info about THAT SPECIFIC COMBINATION of values:
    1) when did this specific customer acquire this specific skill?
    2) when did this specific customer LOSE this specific skill? (for example, accreditation or license lost)
    3) restrictions on this skill? / subskill
    4) etc.

NO WAY you want to store all that in the base Customers table.
Lorna70Author Commented:
Great - thanks guys.  We are implementing the first phase as a really simple one but you're both absolutely right - I need to change my data model to cope with future expansion :-)

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now