Where conditions when running UPDATE or DELETE

hisservant

Apologies for undoubtedly missing a thread that already addresses this; I've searched extensively but am still somewhat confused about adding WHERE restrictions to an UPDATE or DELETE. Per https://editor.datatables.net/manual/php/conditions I see it says

"It is important to note that the conditions applied by Editor->where() are used only on data fetch. When writing data (create and edit actions) you should use the Field->set() and Field->setValue() methods. These are discussed in detail in the Setting field values section below."

Understood, I think. But from an engineering/security perspective, I want to include a WHERE clause for updates or deletes. E.g. suppose there is a users table, containing users from various companies. Before updating the users table, I want to check that users.company_id matches the current user's company id. Is there some way to do this? That would be much easier than writing a separate query that grabs the ID of the row being edited, confirms its company_id is correct, then calls the ->set(true|false) method.

Thanks!

allan

Thanks for posting this - as far as I can recall this is the first time I remember such a post, so I don't think you've missed anything!

Editor 1.2 and earlier did in fact use the WHERE for updates and deletes, but it prove to be frustrating for most people using the conditions since they weren't taking account of changes in data for the conditions applied.

At the moment, there isn't a "good" simple way of doing what you are looking for I'm afraid. Editor 1.6 is going to introduce cancellable server-side events, but at the moment you'd need to perform the check before executing the Editor server-side code.

Something like:

if ( Editor::action( $_POST ) === Editor::ACTION_EDIT ) {
  ... do check on submitted data
}

Sorry I don't have a better answer for you at the moment.

Regards,
Allan

hisservant

Thanks for the clarification! I realize this would be somewhat hacky, but would using the preEdit and preRemove events then throwing an exception to abort illegal changes be another potential approach?

allan

Yes that should work nicely. One thing to note though is that Editor does a try/catch by default in its process method. In the .NET version you can disable that, but currently not in PHP (I'll address that!). You may want to remove the built in try/catch should you wish to you your own error handling.

Allan