Web form with HANA sqlscript stored procedure
In continuation of previous example (see HCP Web form with HANA XSJS backend)
I hope to show usage of stored procedure in order to avoid SQL injection by moving INSERT to stored procedure. Versions respectively HANA 1.00.122.04.1478575636, HCP 1.120.14 and web ide Version: 170216.
From SAP HANA Web-based Development Workbench, navigate to Catalog to create procedure using HANA sqlscript for your purpose
drop procedure "ACTUALS"."actuals.procedures::updateContacts";
create PROCEDURE "ACTUALS"."actuals.procedures::updateContacts"(
IN system nvarchar(3),
IN client nvarchar(3),
IN GUID nvarchar(32),
IN FULL_NAME nvarchar(80),
IN ORGANIZATION nvarchar(80),
IN EMAIL nvarchar(80),
IN PHONE nvarchar(80),
IN NOTES nvarchar(1024),
OUT ev_result nvarchar(80)
)
language sqlscript
SQL SECURITY INVOKER
DEFAULT SCHEMA ACTUALS
AS
lv_lines integer := 0;
BEGIN
INSERT INTO ACTUALS.CONTACTS values (system, client, GUID , FULL_NAME , ORGANIZATION, EMAIL, PHONE, NOTES) ;
SELECT ::ROWCOUNT INTO lv_lines FROM DUMMY;
IF :lv_lines > 0 then
ev_result = '1';
END IF;
END;
From SAP HANA Web-based Development Workbench, navigate to Editor to call procedure using $.hdb loadProcedure
var Name = $.request.parameters.get("NAME");
var NameObj = JSON.parse(Name);
var Org = $.request.parameters.get("ORG");
var OrgObj = JSON.parse(Org);
var Email = $.request.parameters.get("EMAIL");
var EmailObj = JSON.parse(Email);
var Phone = $.request.parameters.get("PHONE");
var PhoneObj = JSON.parse(Phone);
var Notes = $.request.parameters.get("NOTES");
var NotesObj = JSON.parse(Notes);
var conn = $.hdb.getConnection();
var sysuuid = conn.executeQuery( 'SELECT SYSUUID FROM DUMMY;' );
var guid = JSON.stringify(sysuuid);
var replace2 = "{\"0\":{\"SYSUUID\":[";
var replace3 = "]}}";
var replace4 = /,/gi;
guid = guid.replace(replace2, '');
guid = guid.replace(replace3, '');
guid = guid.replace(replace4, '');
var trun = 32;
var guid32 = guid.substring(0,trun);
var proc = conn.loadProcedure("ACTUALS", "actuals.procedures::updateContacts");
var results = proc('HDB', '100', guid32, NameObj, OrgObj, EmailObj, PhoneObj, NotesObj);
var procstatus = results.ev_result;
conn.commit();
$.response.setBody(procstatus);
From internet browser test connectivity and results
From SAP HANA Web-based Development Workbench, navigate to Catalog in order to Confirm record(s) shown in HANA DB
Personally I have really enjoyed working with SAP HANA Cloud Platform and very pleased with new functionality.