Как вставить данные в таблицу при совпадении с другой таблицей?

Question

[ukoHka]

Имеются таблицы grades, students, subjects, teachers, groups, tsg( teachers subjects groups)
Как составить запрос, чтобы в grades оценка ставилась только если текущий преподаватель преподает выбранному учащемуся выбранный предмет? В tsg хранится информация по преподавателям, предметам и группам. В students указывается группа для каждого учащегося.

Answer 1

[Дмитрий Ковальский]

Вы конечно можете выполнить подобный скриптик

insert into grades (studentId,subjectId,grade)
select @studentId,@subjectId,@grade 
from tsg
inner join groups as g on g.id = tsg.groupId
inner join students as s on g.id = s.groupId
where tsg.teacherId = @teacherId AND s.studentId = @studentId and tsg.subjectId = @subjectId

но лучше просто на уровне интерфейса приложения выдавать преподавателю только те данные, с какими он имеет право работать. Дополнительно обвешивать логикой скрипты вставки не стоит - скрипт будет работать неоправданно медленнее

Comments

[ukoHka]

В интерфейсе он видит только свои данные (свои предметы, список учащихся по каждому предмету и т.д.), просто может подделать запрос, изменив, скажем, id предмета.
А насколько такой запрос будет медленнее, чем если бы я отдельно вытащил на сервере список его предметов и группу учащегося перед сохранением оценок и просто не стал выполнять вставку если этот учитель не преподает учащемуся этот предмет?

[Дмитрий Ковальский]

ukoHka: Ну раз у вас преподаватели - такие хакеры, то дерзайте. А скорость работы можете просто тестами оценить.

Answer 2

[inDeepCode]

копать в сторону triggers

Comments

[ukoHka]

То есть все равно выполняется 2 запроса? before update и сам запрос? Тогда проще будет из самой среды выполнить проверку. Просто думал, что можно обойтись одним запросом.

[inDeepCode]

ukoHka: а, ясно, вы криво вопрос написали.

Answer 3

[mletov]

Какая структура Grades? Что-то типа studentId, subjectId, grade?

Этот вопрос вообще нужно решать не на уровне БД, а на уровне пользовательского интерфейса.
Преподаватель в своей админке должен видеть только те группы студентов, которым он преподает, и иметь возможность ставить им оценки только по тем предметам, которые он ведет.

Т е какая-то дополнительная валидация в целях безопасности на сервере лишней не будет, но именно, что "дополнительная".

Comments

[ukoHka]

Структура: studentid, subjectid, grade, date
Да, преподаватель видит только тех, кто у него учится по определенному предмету. Просто другие преподаватели тоже видят этого учащегося, потому что он учится и у них, поэтому без проверки теоретически преподаватель сможет ставить оценки своим учащимся по всем предметам.

[mletov]

Так пусть преподаватель при заходе на страницу ученика видит только те предметы, которые он ему преподает. Или видит все, но редактировать может только преподаваемые. А то по вашей логике выходит так: преподаватель заходит, нажимает кнопку для выставления оценки по чужому предмету. Кнопка не срабатывает. Преподаватель недоумевает, почему кнопка не сработала, начинает на все лады ругать программиста. Его руководство недоумевает по другому поводу: почему преподаватель вообще имеет возможность эту кнопку нажимать, ведь это не его предмет. Меня всегда бесили сайты, которые делают видимость, что предоставляют некий функционал. Зашел на сайт, выбрал опции, жму кнопку, а он тебе "Эта функция только для администратора". Ну и зачем, спрашивается, мне ее тогда показывать?

[ukoHka]

mletov: Нет, преподаватель не видит чужие предметы. Он видит только свои, но, теоретически, если разбирается в программировании, то может подделать запрос, вставив id чужого предмета.

[mletov]

Вообще это очень здорово, что вы заботитесь о безопасности. Но тогда нужно и смотреть шире, например, если ученики вообще без авторизации подберут параметры и наставят себе оценок. Вариантов вырисовывается великое множество. Нужно срочно начинать читать книги по архитектуре и безопасности.
На деле же, думаю, достаточно будет не палить GET параметры в url типа setGrade?name=Ivanov&grade=5

[mletov]

Передавайте данные для апдейта через POST и если уж совсем боитесь, то шифруйте их как-нибудь, от школьников и учителей хватит