Powershell Repository.Execute NullRef exception

[Guillaume]

Hi,
I'm working on a Powershell script that uses EA API. It works well except calling the Repository.Execute method to run INSERT/UPDATE/DELETE queries doesn't work.
I get the following exception:  CategoryInfo          : OperationStopped: ( [], NullReferenceException

Everything else like Repository.SQLQuery work fine

Is there a workaround to get the Execute method to work ?

Thanks

[Geert Bellekens]

Do you mean in version 17.2?

That is one of the new "features" of 17.2. Repository.Execute() doesn't work anymore.

Geert

[Guillaume]

Hi Geert

I'm indeed running the latest build and I confirm it is supported in earlier builds (I successfully ran it using a 16xx build)
Hopefully this "feature" will be removed in the next build 

I have both EA 17.2 and 16.1 installed on the same machine. I wonder if there's a way in PowerShell to force using EA16.1 when creating the object "$Repository = New-Object -ComObject EA.Repository" ?

[Eve]

I'm indeed running the latest build and I confirm it is supported in earlier builds (I successfully ran it using a 16xx build)

Minor correction. It was never supported, it was never documented.

I've personally commented many times here when someone suggested it here that it's unsupported and may be removed without notice.

Now it has.

It's come up a lot as a vulnerability just because it exists. Enough that we had to do something about it.

[Geert Bellekens]

I'm indeed running the latest build and I confirm it is supported in earlier builds (I successfully ran it using a 16xx build)

Minor correction. It was never supported, it was never documented.

I've personally commented many times here when someone suggested it here that it's unsupported and may be removed without notice.

Now it has.

It's come up a lot as a vulnerability just because it exists. Enough that we had to do something about it.

Hi Eve,

I understand that. For me personally, I have used it only in those cases where I had no other option.
This was either because something was not exposed in the API, or because the API was way too slow to be usable.

I've sent an email to Sparx support a while ago, listing all of the use cases of Repository.Execute() (and also the use cases of querying the t_sec*** tables), asking for alternatives.
It's been a few weeks now, but I haven't gotten any response yet.

If there is an alternative, I'm the first one to use that, instead of Repository.Execute.

Now I'm afraid I'll have to make some kind of workaround with a direct database connection. Not exactly ideal, especially in large corporate environments.

Geert

[Guillaume]

Hi Eve,

I understand the issue of having this method potentially identified as a vulnerability, however I share Geert's feedback i.e. this method is needed in many cases to address the following:
- Update or create information that are not available in the API
- Quicker execution time by 10 times or more compared with API methods, making the script usable (e.g. I've seen scripts running time dropping from over 60 minutes down to 3 minutes)

Is there a way to restore this function whilst adding some level of security?

[Eve]

Now I'm afraid I'll have to make some kind of workaround with a direct database connection. Not exactly ideal, especially in large corporate environments.

Yeah, that's not ideal. You know what's worse? Bypassing the security measures that are set up to prevent random changes in a database where the people who set up those restrictions have no visibility that it's happening.

- Update or create information that are not available in the API
- Quicker execution time by 10 times or more compared with API methods, making the script usable (e.g. I've seen scripts running time dropping from over 60 minutes down to 3 minutes)

Send in a request for the API changes you need detail exactly what's needed and why. I won't make any assurances that they'll be allowed though.

Is there a way to restore this function whilst adding some level of security?

I can't see any way that it will be allowed.

[Guillaume]

I'm not in favour of a direct database connection, especially as this is not possible in most cases since it's only accessible from the PCS.

My concern is that most users have custom addins, EA scripts or 3rd party scripts which often use the Repository.Execute method for the reasons I gave. Decomissioning this will generate a lot of issues and claims, with no workaround to provide due to the API limitations:
- Like you said, many direct updates in the DB won't probably addressed via API changes
- For technical reasons I can't explain, the API is very slow for specific actions that can be sorted via INSERT, DELETE or UPDATE queries in the DB. Unless a solution can be found, users won't accept to move backward with running times of a few minutes to an hour or more (I've seen scripts taking 3+hours via the API with a real life data set, dropping to 5 mins thanks to the use of queries + Execute method).

I'm not only saying this for my projects and the ones of EA consultants using this forum, but also for Sparx teams (support, marketing) as users start upgrading EA to 17.2 or greater.
I think it is an important topic to address and discuss.

[philchudley]

Agreed!

I created a Model-Based Add-in for one of my clients which automatically adds legend (or legends) to diagrams that require them. This is a massive productivity aid to them and apart from saving time it also ensures all diagrams are consistent across all models.

So how does Repository.Execute() feature.

Well, there is no API function to create a Legend. Therefore it is all performed in the script. Involving, writing a an entry into that well known cross-reference table t_xref.
Repository.Execute() is required to insert a row per legend.

Yes, I did perform extensive research to ascertain exactly what is required in the t_xref row, so as not to corrupt anything.

For me, the only work arounds would be:

• Inform the client to revert to manual addition of legends
• Wait for a Create Diagram Legend function to be added to the API, along with most likely a Legend Class

Until then I believe they will not be upgrading to EA v17.2

Phil

[Geert Bellekens]

I have the feeling that Sparx Systems is looking at this whole security feature in a different way.

For me the security feature is a feature to protect bonafide good actor users from themselves.
I think it was never intended to protect the model from some bad actors that are trying to "hack" into the model.

What is the threat we are protecting the models from? Users using backdoor tools to give themselves rights to... edit models???

But, with the whole security vulnerabilities scare, this viewpoint seems to have shifted.

For me it would be a perfectly acceptable solution to allow Repository.Execute in these conditions:

1. In case no security is setup, allow it always
2. In case security is setup, allow it with a new permission (that can be set to false by default)

That would make it a concious decision by the model admin to allow this feature or not, and for whom.

I don't need Sparx Systems to child-proof my EA models. I'm prefectly capably to assess the risk myself, and to allow it where appropriate.

Geert

[Geert Bellekens]

This is the list of use cases I've sent to Sparx support

Here's a list of things I use Repository.Execute() for.

- updating the code of internal scripts (EA-Matic)
- Updating the ActionKind of an Action (t_xref)
- Updating the PDATA(x) fields on elements, connectors, diagrams etc.. (MiscData is read-only and can only be used to read the data)
- Adding, and removing Workingsets
- Merging elements (replacing all the references to an element with references to another element). Here it's purely a question of performance.The API is so slow that it would take ages to do this using the API. Using an SQL Update query is at least 100 times faster for these types of bulk updates.
- Modifying the contents of Schema composer schema's or creating new schema's
- Updating user preferences in usys_system
- Automatically removing all userlocks at shutdown (removing them would be possible with the API, but again extemely slow. Getting the current user's locks would be impossible with the new restrictions in SQLQuery)
- Bulk updates to the model (e.g. removing old tagged values after an MDG update) Again, mostly a performance issue.
- updating the details of diagram legends (t_xref)
- updating elementID of a diagramObject
- updating user group permissions (in this case it was to remove all permissions for all groups except the administrators group)
- solving the problem when users had done "checkoutOffLine" by accident and then updated the (DBMS) repo (updating packageflags)

We use Repository.SQLQuery() on the  the security tables for the following use cases
- Getting a list of all the locks a user currently has
- Knowing if a user is part of a certain security group
- Getting the user name and firstname that currently has a lock on an element
- Getting a list of all users currently defined in the model
- Checking if the contents of a package(branch) is completely locked by the current user.
- Checking the RequireLock property in t_secpolicies

Geert