Simple SugarQuery using a custom field returns no results

Hi Artis Plocins 

As far I can see your code seems OK.

Add these lines before $sq->execute(); :

$sql = $sq->compileSql();

$GLOBALS['log']->fatal("sql:\n" . $sql);

You will see the very query executed in your database. Make sure it is OK.

Kind regards

This is the compiled query:

SELECT  cases.id id FROM cases INNER JOIN (select tst.team_set_id from team_sets_teams tst INNER JOIN team_memberships team_memberships ON tst.team_id = team_memberships.team_id
                    AND team_memberships.user_id = ''
                    AND team_memberships.deleted=0 group by tst.team_set_id) cases_tf on cases_tf.team_set_id  = cases.team_set_id
LEFT JOIN cases_cstm ON cases_cstm.id_c = cases.id  WHERE cases.deleted = 0 AND cases_cstm.ticket_id_c = 192

What is cases_tf? There is no such table.

But the cases_cstm.ticket_id_c definitely is 192 in the database and all cases have a deleted value of 0.

Running the same query through PhpMyAdmin does not return any results too.

If I take out the team tables join I get the right result in PhpMyAdmin with this query:

SELECT  cases.id id FROM cases 
LEFT JOIN cases_cstm ON cases_cstm.id_c = cases.id  WHERE cases.deleted = 0 AND cases_cstm.ticket_id_c = 192

But obviously SugarQuery has to insert the team permissions in there.

Could this have something to do with the SugarQuery being run on a REST endpoint that does not require authentication?

I found that it is possible to disable team security for SugarQuery like this to avoid the failing team table join. This change successfully returns results for me:

$sugarQuery->from($bean, array('team_security' => false));  

I don't know whether this is the right way of doing it because it's about ignoring security.

Somehow SugarQuery didn't identify the authenticated user. How did you run this query? Inside SugarCRM interface?, From inside an endpoint?

From an endpoint without authentication.

This explains why the team join were not properly populated. Without authentication SugarCRM can not reach the user which is important for team join.

Hi Artis Plocins,

If you really need to run SugarQuery without authentication, you can pass 'team_security' as false and it will not include team membership in query. Here you go:

$sq->from(BeanFactory::newBean("Tasks"), array('team_security' => false));

Regards.

Hats

Hi Artis Plocins

             Have you found the solutin to your question

Regards

Sidhu

Artis Plocins

What are you trying to achieve on the overall solution?

You mentioned you built an endpoint without authentication that looks like it might output data from your Sugar system, aren't you concerned that someone could find that url, and retrieve information from your system? 

If this is an integration between two systems (eg: a website that needs to show the case?), why wouldn't you use the authentication to gain the OAuth token and subsequently pass the OAuth token to the GET API for the Cases module? You wouldn't even need to create a custom API for that, but just leverage the filter API.

There are really really rare cases where you would leverage an unauthenticated endpoint. Same applies from overcoming Team security.

Authentication and authorisation are in place to make sure you authenticate as a specific user and then the system will allow the correct ACL available to the specific user (being that Team visibility or Roles actions).

I do not know and do not have visibility to your full use case, but from what I see and know, something does not feel right...

Hope this gives you some hints to think about

This is an integration between a customer support ticket system and SugarCRM.

None of the Authentication and SSO options are applicable to SugarCRM, to my knowledge

I would prefer to authenticate, but I didn't see a way here. Please do tell me if authentication is still possible in such a case.

This system sends data to my endpoint when something happens in it, but it can only send once per event, so I cannot make it authenticate with SugarCRM before sending the data. That's why my endpoint is without authentication.

This is an integration between a customer support ticket system and SugarCRM.

None of the Authentication and SSO options are applicable to SugarCRM, to my knowledge

I would prefer to authenticate, but I didn't see a way here. Please do tell me if authentication is still possible in such a case.

This system sends data to my endpoint when something happens in it, but it can only send once per event, so I cannot make it authenticate with SugarCRM before sending the data. That's why my endpoint is without authentication.

Hey Artis Plocins,

I am unsure what the screenshot is exactly.

This system sends data to my endpoint when something happens in it, but it can only send once per event

Do you have the possibility to modify the sending end to complete other actions? I suspect you do, otherwise how do you define what is sent and to what url?

In that code you could use the pseudocode:

call login

result = send data to sugar

if(!result) {
    call login again
    try again once, if something went wrong due to expired token over time
    if failed handle password error and stop by showing system is down message
}

function call login {
    get previously leveraged oauth token from local storage
    if empty retrieved oauth token {
        execute actual sugar login as the integration user
            (note that you should use a user with limited permissions, allowing just what it should be doing)
             You should only login when absolutely needed so that most of the times only one http request between the two systems is executed and get token back or if failed handle password error and stop by showing system is down message
        store the oauth token in local storage for re-use if valid
    }
}

function send data to sugar {
    send data to sugar
    if error {
        empty oauth token from local storage so that it won't be re-used
    }
}

If you cannot modify at all the code on the sending end, you could have a middleware layer that does the same and actually sends data to Sugar.

You could lock down the middleware so that only the sending service can connect to the middleware (basic auth, header auth, ip whitelist).

If you do not need real-time response to the user, you could just even store the posted info in a local database, and then parse it and send it to sugar at your own time, and then send back confirmation to the user, depending on the specific use case.

This is how I would approach the problem

Thank you very much for your suggestions! It will definitely be useful to keep in mind these options in such cases, but in this case, the only best option between what I could think of and your suggestions would have been to use a middleware to authenticate a trigger request to SugarCRM and make Sugar retrieve the data from a locked down database - so a combination of two of your suggestions! Just that it was not justified in terms of time for this version of the integration.

Hi Artis, Sugar supports OAuth, LDAP,  and AD as authentication mechanisms. We actually leverage opensource OneLogin libraries, so you can definitely also use OneLogin to authenticate.

Also, this may be helpful: http://support.sugarcrm.com/Documentation/Sugar_Developer/Sugar_Developer_Guide_7.9/Integration/Web_Services/v10/Examples/Bash/How_to_Authenticate_and_Log_Out/index.html

Thanks, that will be useful to know!There was nothing about these types of a

There was nothing about these types of authentication in the SugarCRM Developer Guide API authentication. And anyway, I found those authentication options only mentioned at most.