Hi, I provide training in SAP UI5. Also feel free to checkout my new web page https://saptechblog.com

Introduction to SQL Script

SQL Stands for Structured Query Language. It is a standardized language for communicating with the Relational database.
SQL is used to retrieve, store, manipulate or remove information from database.
The tasks performed by SQL statements are as follows.
                  1. Schema definition and manipulation
                  2. Data manipulation
                  3. System Management
                  4. Session Management
                  5. Transaction Management
With SQL we will be able to perform the above operations in the database and we won’t be able to write the data intensive logic.
SQL Script:
The motivation of SQL Script is to embed data intensive application in the database. Most of the logic gets executed in the Application layer. This requires data to be transferred from database layer to application layer and vice versa.
While executing data intensive logic, this copying of data back and forth is very expensive in terms of processor and data transfer time.
While writing imperative logic like ABAP, developers tends to write algorithm that loop over a large rows of data which is hard to optimize and parallelize.
SQL Script allows developer to push such data intensive logic in the database layer. Conceptually SQL Script is related to stored procedures. But SQL Script is designed to provide superior optimization possibilities.
When should SQL Script be used?
SQL Scripts should be used in cases when the modelling constructs of HANA such as Analytic views and attribute views are not sufficient.

SQL Script addresses the following problems:
1. Decomposing a SQL Query can be done only using views. Views cannot be parameterized which limits their reuse. In particular, it can only be embedded in the SQL Statements just like tables.
2. SQL Queries do not have the features to express business logic.
3. SQL Queries can return only one result set a time.
4. With SQL, Imperative logic is required. SQL Script avoids the need for that.
SQL Script Security recommendations:
SQL Script is used to read or modify information. In some cases, depending on command or parameters you write can result in data leaking or data tampering. To prevent that SAP provides the following recommendations.
1. Mark each parameter with the Keyword IN or OUT. Avoid using INOUT keyword.
2. Use Invoker keyword, when you want user to have assigned privileges to start the
procedure. The default keyword definer, only allow the owner of the procedure to start it.
3. Mark read only procedure as “READS SQL DATA”. This ensures the data and structure of database are not altered. Another advantage is that it optimized the performance.
4. Ensure type of parameters and variables as specific as possible. E.g. avoid using VARCHAR. By reducing the length we can avoid the risk of injection attacks.
5. Perform validation on IN parameters within the procedure.
6. Dynamic SQL – Avoid use of Dynamic SQL which risks injection attack. E.g. Use SELECT in case of EXECUTE IMMEDIATE. Use Server side JavaScript to write this procedure instead of SQL Script

7. In case of Dynamic Query or Escape code use server side JavaScript instead of SQL Script procedure.

About Arun


Powered by Blogger.