Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

securing query authorization in linq-to-sql

Tags:

c#

linq-to-sql

I have 3 tables: the User table, the Records table and the UserRecords table.

The columns are so:

UserTable
UserID | OtherUserDataFields

RecordsTable
RecordID | OtherRecordDataFields

UserRecords
UserID | RecordID

The UserRecords table tells me which users have authorization on which record. I have a function that updates the RecordsTable by receiving 2 paramaters: a record TheRecord (which contains a RecordID field) and a UserID.

I'm writing a query in the data context MyDC to fetch the record with the RecordID supplied in the parameter and test if the user is authorized on that record like this:

var RecordToUpdate = (
    from r in MyDC.RecordsTable
    from u in MyDC.UserRecords
    where r.RecordID == TheRecord.RecordID && TheRecord.RecordID == u.RecordID
    where u.UserID == TheUserID
    select r).SingleOrDefault();

Will this ensure me that only records the user is authorized will be fetched? I want to avoid cases where users maliciously send a record they're not authorized on and make changes to these unauthorized records.

Thanks for your advice.

like image 895
frenchie Avatar asked Jan 17 '23 20:01

frenchie


2 Answers

I agree with Anand, you will need a linq query:

var filterUserRecord = from u in MyDC.UserRecords
                       where u.UserID  == TheUserID
                       select u;

var q1 = from r in  MyDC.RecordsTable
          where r.RecordID = TheRecordID
          where filterUserRecord.Any(f => f.RecordID == r.RecordID)
          select r;

This will be converted to the SQL query like the following:

SELECT * FROM RecordsTable rt WHERE rt.RecordID = TheRecordID AND EXISTS
   (SELECT recordId FROM UserRecords ur WHERE ur.userId = TheUserID AND ur.recordID = rt.recordID)

Note that those are IQueryable<T>s and linq queries on it will produce another IQueryable<T> which will contain expressions to be wholly translated to SQL (or whatever the backend is) instead of naively evaluating it at client side.

like image 64
Krizz Avatar answered Jan 30 '23 23:01

Krizz


Well I think your problem could be solved by subquery

sql:

select * from RecordsTable where recordId in 
(select recordId from UserRecords where userId = @someUserId)

It could be represented in Linq as following

var filterUserRecord = from u in MyDC.UserRecords
                       where u.UserID  == TheUserID
                       select u

var q1 = from r in  MyDC.RecordsTable
         where filterUserRecord.Any(f => f.RecordID == r.RecordID)

Details for subquery in Linq - Read from here

like image 40
Anand Avatar answered Jan 31 '23 00:01

Anand