You can develop secure procedures using SQLScript in SAP HANA by observing the following recommendations.
Using SQLScript, you can read and modify information in the database. In some cases, depending on the commands and parameters you choose, you can create a situation in which data leakage or data tampering can occur. To prevent this, SAP recommends using the following practices in all procedures.
- Mark each parameter using the keywordsINorOUT. Avoid using theINOUTkeyword.
- Use theINVOKERkeyword when you want the user to have the assigned privileges to start a procedure. The default keyword,DEFINER, allows only the owner of the procedure to start it.
- Mark read-only procedures usingREADS SQL DATAwhenever it is possible. This ensures that the data and the structure of the database are not altered.
Tip Another advantage to usingREADS SQL DATAis that it optimizes performance.
- Ensure that the types of parameters and variables are as specific as possible. Avoid usingVARCHAR, for example. By reducing the length of variables you can reduce the risk of injection attacks.
- Perform validation on input parameters within the procedure.
我是说HANA ，为什么每次后面都加那么一大串。 language sqlscript sql security definer reads sql data
这个可以直接创建的SQL窗口里写也行。不用那么麻烦。
我都不知道这下面这个什么意思。奇奇怪怪的没明白LOGIC。 很可能是随便复制的。
), OUT var_out "_SYS_BIC"."DEMO/DEMO_SQL_CV/proc/tabletype/VAR_OUT" ) language sqlscript sql security definer reads sql data
as
/********* Begin Procedure Script ************/
BEGIN
SELECT CURRENT_DATE,"_BIC_Z0CGDDH" AS DATE_V FROM "_SYS_BIC"."DEMO/DEMO_CV_IP"
where "_BIC_Z0CGDDH"= 'ZCCG-1403270935500D';
var_out =
SELECT
A."/BIC/Z0CGDDH" AS CGDDH,
A."/BIC/Z0DDZT" AS DDZT,
B."/BIC/Z0SL" AS SL,
B."/BIC/Z0JE" AS JE
FROM "SAPABAP1"."/BIC/AZEZJTO0100" A LEFT JOIN
"SAPABAP1"."/BIC/AZEZJTO0200" B ON A."/BIC/Z0CGDDH"=B."/BIC/Z0CGDDH"
WHERE B."/BIC/Z0JE" IS NOT NULL
AND A."/BIC/Z0CGDDH" IN(:IP_CGDDH)
;
END /********* End Procedure Script ************/
权限过滤，有空看看。
) , MD5_CODE ) , OUT AUTH_VALUE ) )
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
--DEFAULT SCHEMA <default_schema_name>
READS SQL DATA AS
BEGIN
/*****************************
Write your procedure logic
*****************************/
--根据MD5码 得到值列表。
) ;
--7380563cdbb1061c70bc76c19ef6d9a2
DECLARE CURSOR c_cursor FOR
select * from "SAPABAP1"."/BIC/OHZBWOHD2" where "USERNAME_MD5" = :MD5_CODE and
FILTER_COLOMN = :AUTH_IOBJ ORDER BY "VALUE_LOW" asc ;
val:= '';
FOR r1 AS c_cursor DO
--val:= :val || '''' || '''' || r1.VALUE_LOW || '''' || '''' || ',';
val:= :val || '''' || r1.VALUE_LOW || '''' || ',';
IF r1.VALUE_LOW = '*' THEN
val:= '''' || '*' || '''';
BREAK ;
END IF ;
END FOR;
-- AUTH_VALUE := :val ;
select RTRIM (:val,',') INTO AUTH_VALUE from dummy;
--AUTH_VALUE := :val || '''' ;
CLOSE c_cursor;
END;
这是一个权限判断的存储过程。想必还是有用，也没看懂。
) , ZIP_MD5 ) ,ZIP_IOBJ ) ,
OUT VAR_OUT ) , OUT VAR_FLAG ))
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
--DEFAULT SCHEMA <default_schema_name>
READS SQL DATA AS
BEGIN
/*****************************
Write your procedure logic
*****************************/
;
;
;
) ;
);
------------------------------------------------------------------------------------
;/* Custom Error Code = 10001*/
/*User Defined exception handler */
DECLARE EXIT HANDLER FOR CUSTOMCONDITION RESIGNAL;
--SELECT ::SQL_ERROR_CODE AS "Error Code", ::SQL_ERROR_MESSAGE AS "Error Message" FROM DUMMY;
/*权限检查段，根据输入的MD5获取权限值*/
------------------------------------------------------------------------------------
SCONST := '''' || '*' || '''';
VAR_OUT := '';
call "_SYS_BIC"."Purchase::ZAUTH_DYN_FILTER"(:ZIP_IOBJ,:ZIP_MD5,:SVAR);
IF :SVAR = '' THEN
SIGNAL CUSTOMCONDITION SET MESSAGE_TEXT = '^_^权限不足，请联系管理员增加权限！^_^';
return;
END IF;
IF :AUTH_IP = '' OR :AUTH_IP = :SCONST THEN
VAR_OUT := :SVAR ;
ELSE
/*对比用户输入和存储过程查到的数据*/
IF :SVAR = :SCONST THEN VAR_OUT := :AUTH_IP;
ELSE
AUTH_IP := :AUTH_IP || ',';
DO
ENO := LOCATE(:AUTH_IP , ',',:SNO) ;
FLAG := LOCATE(:SVAR , SUBSTRING(:AUTH_IP , :SNO , :ENO-:SNO)) ;
THEN
SIGNAL CUSTOMCONDITION SET MESSAGE_TEXT = '^_^权限不足，请联系管理员增加权限！^_^';
return;
END IF;
SNO : ;
END WHILE;
select RTRIM (:AUTH_IP,',') INTO VAR_OUT from dummy;
END IF;
END IF ;
IF :VAR_OUT = :SCONST THEN
VAR_FLAG := '%' ;
END IF ;
END;