Trevor found an unusual bug. Every customer had a GUID, but for some reason, their JSON API failed if there were ever more that 75 results.
He checked the web layer, only to find that it didn’t actually build the JSON- it just returned a string. The string itself came from their Oracle database. That’s where this procedure came from:
create or replace PROCEDURE GET_CUSTOMERS
(
InCustomerGuid IN VARCHAR2,
OutCustomerGuidArray OUT VARCHAR2,
OutResult OUT NUMBER,
OutResultMessage OUT VARCHAR2
) AS
vCustomerGuidArray VARCHAR2(4000);
vCustomerGuid VARCHAR2(40);
BEGIN
OutResult := 0;
OutResultMessage := NULL;
vCustomerGuid := UPPER(RTRIM(LTRIM(InCustomerGuid, '{'), '}'));
IF( InCustomerGuid IS NOT NULL )
THEN
SELECT '{"keys":[{"key":"' || Customer_Guid || '","type":"CUSTOMER"}]}'
INTO vCustomerGuidArray
FROM Customers
WHERE Customer_Guid = vCustomerGuid;
ELSE
vCustomerGuidArray := '{"keys":[';
FOR Customer_Item IN (SELECT Customer_Guid FROM Customers) LOOP
vCustomerGuidArray := vCustomerGuidArray || '{"key":"' || Customer_Item.Customer_Guid || '","type":"CUSTOMER"},';
END LOOP;
vCustomerGuidArray := RTRIM(vCustomerGuidArray, ',');
vCustomerGuidArray := vCustomerGuidArray || ']}';
END IF;
OutCustomerGuidArray := vCustomerGuidArray;
EXCEPTION
WHEN NO_DATA_FOUND THEN
OutCustomerGuidArray := NULL;
WHEN OTHERS THEN
OutResult := 20000;
OutResultMessage := 'too many customer guids to send back to caller';
RETURN;
COMMIT;
END GET_CUSTOMERS;
Like the Nintendo game, somebody’s missing the point of JSON
Pick your WTF: generating JSON in the database, generating JSON by string concatenation
, not being more careful about sizing a VARCHAR2
variable, or using Oracle.