Providing query authorization in linq-to-sql

Question

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

Columns:

 UserTable UserID | OtherUserDataFields RecordsTable RecordID | OtherRecordDataFields UserRecords UserID | RecordID 

The UserRecords table indicates which users have write permission. I have a function that updates RecordsTable, getting 2 parameters: TheRecord record (which contains the RecordID field) and UserID.

I write a query in the context of MyDC data to retrieve a record using the RecordID set in the parameter, and check if the user is allowed in this record as follows:

 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 guarantee that only records that the user has authorized will be registered? I want to avoid cases where users maliciously send an entry to which they are not authorized and make changes to these unauthorized entries.

Thank you for your advice.

Answer 1

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

 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 an SQL query, as shown below:

 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 this is an IQueryable<T> , and linq queries on it will create another IQueryable<T> that will contain expressions that are fully translated into SQL (or whatever the backend), rather than naively evaluating it on the client side.

Answer 2

Well, I think your problem can be solved by a subquery

SQL:

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

It can be represented in Linq as follows:

 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 the subquery in Linq - read here

Answer 3

Perhaps read the article here that discusses how SQL Injection (which I suppose should be the main security issue here) is handled in LINQ scripts.

There's also a good article on Microsoft security considerations for EF. It is worth a read for those who develop these tools!

[Edit] As for your last comment, you can use queries similar to the ones already on this page. To condensate a bit: if your database is normalized to the extent that RecordId is a unique primary key, you can bypass joins to make a query that reads a little better:

 var targetRecords = from userRecords in MyDC.UserRecords where userRecords.UserTable.UserID == TheUserID && userRecords.RecordsTable.RecordID == TheRecord.RecordID select userRecords; var targetRecordsResult = targetRecords.SingleOrDefault(); 

I have separated the query from this “var” result here to indicate that “targetRecords” is NOT evaluated until you call SingleOrDefault on it to assign targetRecordsResult. You can, of course, wrap this in one statement if you want.

If, as already mentioned, your RecordID is a unique Primary Key, you will either receive the corresponding report or null. Please note: if this is not so, that is, more than on the record, it can have the same identifier, then a call to SingleOrDefault can fail. If your database is designed this way, you will have to use a query similar to the one specified by Anand. This is a bit more details, but ANY post with the corresponding identifier for this particular user will be returned to you.

As for security, note that your SQL statement will be compiled containing UserID, which will make it very difficult to intervene. Therefore, I want to say that in this case, the scope and impact on UserID is your main problem. If, as you stated, the user (and any potential ill-fated user) does not have access to the variable (through the influence of property, etc.), then this should be more than suitable for your needs.

Answer 4

Try the following:

 var RecordToUpdate = (from u in MyDC.UserRecords where u.UserTable.UserID == TheUserID and u.RecordsTable.RecordID == TheRecord.RecordID).SingleOrDefault(); 

This will return the result for the query that indicated UserID and RecordID.