We’ve developed some resources to help you work effectively from home during COVID-19 Click to learn more

Off-by-one error in ForeignSystem agent SaveForeignKey

Hi, we're using the ForeignSystem agent via the REST API, and have seen some random issues. I think I have tracked the error down to a bug in the ForeignSystemAgent when the value is split over multiple lines in the ForeignKey table.

Basically, in some rare cases the value saved is not the same as the value returned back by SaveForeignKey.

The issue happens when the value has one of these lengths:





(and so on)

The subvalue field in the ForeignKey SQL table is defined with length 239. Seems like for each time the value is split into a new row in ForeignKey table there is an off-by-one error.

Issue can be reproduced with this CRMScript:

#setLanguageLevel 3;

String value = "";

for (Integer i = 1; i < 1000; i++)
  value.append("A"); //Make string one char longer
  NSForeignSystemAgent agent;
  NSForeignKey key;
  NSForeignKey savedKey = agent.SaveForeignKey(key, "MyApplication", "MyDevice", "MyDeviceIdentifier");

  // Does the value we sent and the returned value match?
  if (key.GetValue() != savedKey.GetValue())
    printLine("Error for length " + i.toString());

Result after running is

Error for length 236
Error for length 471
Error for length 472
Error for length 706
Error for length 707
Error for length 708
Error for length 941
Error for length 942
Error for length 943
Error for length 944


Python script to reproduce same via REST api.

import requests
import json

access_token = "8A:Cust11737.AUcvWCs(REDACTED....)"
cust_id = "Cust11737"
url = "https://sod.superoffice.com/" + cust_id + "/api/v1/Agents/ForeignSystem/SaveForeignKey"

for i in range(1, 1000):

    headers = {
        "Authorization": "Bearer " + access_token,
        "Content-Type": "application/json"

    # For each loop, make the value one char longer...
    sendValue = "A" * i

    body = {
        "ForeignKey": {
            "Key": "MyKey",
            "Value": sendValue,
            "CreatedDate": "0001-01-01T00:00:00",
            "UpdatedDate": "0001-01-01T00:00:00",
            "CreatedBy": None,
            "UpdatedBy": None,
            "TableName": None
        "ApplicationName": "MyApplication",
        "DeviceName": "MyDevice",
        "DeviceIdentifier": "MyDeviceIdentifier"

    r = requests.post(url, data=json.dumps(body), headers=headers)

    returnedValue = json.loads(r.content)["Value"]

    if sendValue != returnedValue:
        print("ERROR: ", i)

I'll send to bug@superoffice.com, but wanted to share here incase anyone else has random errors with ForeignKeys.

RE: Off-by-one error in ForeignSystem agent SaveForeignKey

Interessting finding Frode

Can you give some example when you are using the ForeginKey table/functionality of SuperOffice?


Av: Anders Larsson 13. maj 2020

RE: Off-by-one error in ForeignSystem agent SaveForeignKey

Hi, we have an Online-app where we need to store some JSON objects into the SuperOffice database, and just needed "somewhere" to put those JSON data. I guess we could also have used userpreference or blob instead, but foreignkey has worked fine up until now.

Av: Frode Lillerud 13. maj 2020

RE: Off-by-one error in ForeignSystem agent SaveForeignKey

Hi Frode, 

Great find! Just wanted to let you know this has been fixed. Should be out in Online within a couple weeks, and in the next Onsite release.

Best regards!

Av: Tony Yates 26. maj 2020

RE: Off-by-one error in ForeignSystem agent SaveForeignKey

Super, thanks!

Av: Frode Lillerud 26. maj 2020