Backend

All record operation are made by the php data access class "data_accessclass".

The configuration for the table is saved in the JSON datadefinition.

As database My SQL is used.

The advantages are:<\p>

control of the data access
access class for all tables with individual configuration and includes
access control to different users and roles
supports objects, thanks to child configuration
validation thanks to the table declaration (datadefinition)

My SQL

For support of the data access class the table structure has to follow this rules:

primary key, INT, autoincrement
creatorID, INT, if right check is needed
datetimecreated, DATETIME (optional)
clientID, INT (optional, needed for domain_rightmode=1)
rightgroupID, INT (optional, needed for domain_rightmode=2)
companyID, INT (optional, needed for domain_rightmode=3)

It's recommeded to add a second unique key:

display column, VARCHAR unique, as identifier in links

The basic tables
Table	Comment
k8login	The user table with login parameters and roles for the data access
k8loginfriends	sub table of k8login to add friends or follower
k8references	here are all uploads stored
k8languages	this is the place for translations in the database. The frontend translations are made in JavaScript.

The users are saved in k8login.

Data access can be granted for friends or followers in k8loginfriends.

Images for all tables are joined in k8references.

Basic pages and datadefinitions

The basic datadefinitions
Page	Datadefinition	Comment
register	k8loginregister	backend datadefinition: k8login
mydata	k8login	main definition for the form
	k8loginfriends	for the friends table (lineedit)
	k8loginsearch	for the search of users, only userID and username
user_list	k8login	user list for the admin
not yet available	k8references	images and attachments are stored here
not yet available	k8languages	translation for the backend

If you like to change this datadefinitions, copy it please in your project folder. The datadefintions in the project folder are read first and have priority.

JSON datadefinition

This is the important configuration for the backend:

{
  /* main parameters */
  "table":<tablename>",   
  "key":<primary key column>",
  "objectclass": "data_accessclass",
  "requiredfile": "masterdata\/class_data_accessclass.php",
  "rightcheck": true,

"columns":[
    {}
  ],
  masterdata:{
    "sql_derived":"SELECT *, <columnX> FROM <tablename1> LEFT OUTER JOIN <tablename2> ON ... WHERE 1=1",
    "sql_statement":"SELECT * FROM <tablename> WHERE 1=1",
    "sql_orderby":"<column>",
    "rights":{
      "create":{
        <role>:<method>
      },
      "read":{},
      "update":{},
      "delete":{},
    },
    "upload":{  
      "enabled":true,         /* reads the joined images/uploads */
      "table":"<tablename>",  /* only, if deviating */
      "key":"<columnname>"    /* only, if deviating */
    },
    "include":{
      "getEntries_sql":"{{projectpath}}/{{datadefID}}/{{datadefID}}_getEntries_sql.php",
      "getEntries":"{{projectpath}}/{{datadefID}}/{{datadefID}}_getEntries.php",
      "validate":"{{projectpath}}/{{datadefID}}/{{datadefID}}_validate.php",
      "beforeinsert":"{{projectpath}}/{{datadefID}}/{{datadefID}}_beforeinsert.php",
      "afterinsert":"{{projectpath}}/{{datadefID}}/{{datadefID}}_afterinsert.php",
      "beforeupdate":"{{projectpath}}/{{datadefID}}/{{datadefID}}_beforeupdate.php",
      "afterupdate":"{{projectpath}}/{{datadefID}}/{{datadefID}}_afterupdate.php",
      "beforedelete":"{{projectpath}}/{{datadefID}}/{{datadefID}}_beforedelete.php",
      "childdelete":"{{projectpath}}/{{datadefID}}/{{datadefID}}_childdelete.php",
      "afterdelete":"{{projectpath}}/{{datadefID}}/{{datadefID}}_afterdelete.php"
    }
  },
  "backend":{
    "deleteloadrecord":true,
    "foreignkeys":[
      {"table":"k8components","sparekey":"userID","key":"creatorID","text":"already creator of items!"}
    ]
  },
  "childs":[
    {
      "table": "TPareas",
      "key":"areaID",
      "objectclass":"data_accessclass",
      "fieldname":"areas",      /* the records will be appended with this array name */
      "rightcheck":0,           /* the rightcheck is made in the main object */
      "masterkey":"projectID",  /* the column of the child table joined to the master key */
      "childs":[]               /* this object can have more nested childs */
    }
  ],
  "parentcolumn":"parentID",
  "treearrayname":"_children",
};

Main parameters

The main parameters are "table" and "primary key column". For the data access the class "data_accessclass" is used. This class is stored in the file "class_data_accessclass.php". This can be replaced by your own class. The rightcheck can be activiated. The details for it are placed in masterdata.

columns

Only the columns with the property "FIELD" can be inserted or updated. The data type is important for the header filter and the validation.

SQL Statement (sql_statement, sql_derived)

With a view more tables can be joined and columns can be added for the "read" method. The columns added by the view can not be inserted or updated. In the "sql_derived" the statement is nested. It's a bit slower, but easier to handle because you have all columns in the WHERE Statement.

rights

Each user can have several roles. Each role can have a method for the granted rights.

upload

If the upload is enabled, an array "imagearray" is added the recordset.

include

Thanks to the include individual functionality can be added.

backend / foreignkeys

Before the record will be deleted, foreign key can be checked. If the record is referenced by other tables, the deletion will be canceled and a message (text) is returned.

childs

All records of the child table are added with the "fieldname" to the main record. The childs can be nested and all parameters can used like for the main table.

parentcolumn

"parentcolumn" activates the reading of a tree structure. The "parentcolumn" contains the ID of the parent record. All records with the parentID are added in the array "treearrayname".

treearrayname

All records with the "parentID" are added to the array of this example: "_children".

Columns

columns[]
Property	description
fieldname	column name
mytype	Type without brackets
size	value between Type brackets
mydefault	set only to overwrite the column default
noupdate	true / false (not valid for admin)
noinsert	true / false (not valid for admin)
required	true / false
Field	column name in the table, empty -> not inserted or updated
Key	Example: PRI
Type	type of the column: varchar(50)

The columns array is the basic structure to build the SQL-Statements. For a 'SELECT' the column definition is used to build up the WHERE part. In this case only 'fieldname' and 'type' is needed. Without definition the column is handled like 'varchar'.

filter types:

VARCHAR: all char types
NUMBER: TINYINT,SMALLINT,MEDIUMINT,BIGINT,INT,FLOAT,DOUBLE,DECIMAL
BOOLEAN
DATE
DATETIME
TIME

In an 'Update' or 'Insert' statement only the columns with the 'Field' parameter are written.

The 'creatorID' is always filled with the logged in userID ($_SESSION['userID']).

mydefault with asterisk, example: 10000*

The field is automatically incremented with the maxnumber()+1.

SQL statement

sql_derived example:

Please write the sql_statement or sql_derived in the datadefinition in one line (JSON compliant)!
The "sql_derived" property is important by using the view with tabulator, otherwise the property "sql_statement" should be used because it's a little bit faster.

The placeholder are:

§select: additional fields are inserted:
- rightuser_update
- rightuser_delete
'' as k8select: same §select
§dateformat: the SQL date string is inserted
WHERE 1=1: Where condition, here the "WHERE ".$clause is inserted
§userID: $_SESSION['userID']
§clientID: $_SESSION['clientID']
§companyID: $_SESSION['companyID']
§domain_language: $GLOBALS['domain_language']

Alternatively the include "getEntries_sql" can be used to write the statement direct in php.

Data access rights

RBAC (Role Based Access Control) assigns the user to 1 or more roles. Roles restrict or authorize the access to tables and records. This rights can only be granted by using the login with php sessions. The RBAC is implemented in the PHP data access class. The access check is activated for an object in the datadefinition by setting "rightcheck=1". In the data table the column "creatorID" is added. Comparing the creatorID with the logged in user gives the result: own record or foreign record.

By opening the website the user is assigned to the role "0:public". His userID is also 0. By login the role is replaced by the default roles of the login table: k8login.roles="3,5". The userID is set. For each object or table, for each CRUD operation (create, read, update, delete) and role an access check is implemented.

This is the data access definition in the datadefinition (masterdata.rights):

CRUD Operation
- Role-ID
  - Method for access check

Thanks to the roles and data access methods, you can easyly grant the rights of your CRUD operations.

RBAC with K8 Web Kit

Roles

$GLOBALS[domain_roles]:

roleID:
- name

Users and role membership

table k8login:

userID
roles (roleID-1, roleID-2)
comma separated roleIDs

Datadefinition / table

CRUD Operation
- Role-ID
  - Method for access check

Methods

0: not granted
1: table.clientID=SESSION[clientID]
2: granted
...
10: table.creatorID=SESSION[userID]

Roles

The roles are defined in:

masterdata/_init.php

Users and role membership

The initial roles are the default value in the columns array of the datadefinition "k8login". It can be changed in the user list by super user or admin.

Datadefinition / table

In the datadefinition the rights are written in:

masterdata
- rights

Methods

The methods are checked before or by executing the sql statement.

CRUD Operation by PHP data access class
Operation	SQL command	data access function
Create	INSERT	add()
Read	SELECT	getEntries()
Update	UPDATE	update()
Delete	DELETE	delete()

By each CRUD operation the data access is checked in the correspondent data access function.

Roles
ID	Role	Comment
0	public	user not logged in
1	admin	all rights in a client
2	superuser	all rights in the database
3	member	logged in user
5	friends	assigned in table: k8loginfriends
6	company, own data	domain_rightmode=3
7	company, admin	domain_rightmode=3
19	company login	domain_rightmode=3
21	company read	domain_rightmode=3
25	company create standard	domain_rightmode=3
30	rightgroups	assigned in tables: k8rightgroups, k8rightmembers
x	others	create by yourself

masterdata/_init.php, domain_roles:

The available roles are defined in the "_init.php".

Roles by login:

Each user can get assigned to 1 or several roles. The default roles can be set in masterdata/k8login/k8login.json, property: columns / roles / mydefault in a comma separated string like "3,5".

Admin / user list:

Superuser or admin can change the user roles in the user_list.

Methods for the access check
Number	Check method
0	no rights
1	access permission in this client: table.clientID=$_SESSION[clientID]
2	permission granted
3	user logged in: $_SESSION[userID]<>0
4	table.companyID=$_SESSION[companyID]
5	table.creatorID=$_SESSION[userID] table.companyID=$_SESSION[companyID]
10	table.creatorID=$_SESSION[userID]
11	check friend k8loginfriends.friendID=$_SESSION[userID]
13	checks membership in right groups
14	checks the rights of the active right group (rightgroupID) of the user: create: k8rightmembers.rightgroupID=$_SESSION[rightgroupID] and k8rightmembers.userID=$_SESSION[userID] and status>1 read: $table.rightgroupID=$_SESSION[rightgroupID] and k8rightmember.userID=$_SESSION[userID] and status>0 update: $table.rightgroupID=$_SESSION[rightgroupID] and k8rightmember.userID=$userID and ((k8rightmembers.status=2 and $table.creatorId=$userID) or k8rightmembers.status=3)) delete: same update
1000	check parent table access rights
...	programm your own check

By each CRUD operation the access rights are checked. If the operation by Create, Update or Delete is not granted, an error is returned. Reading a table can return an empty recordset, because of the missing rights.

By Read the results of the access check for Update and Delete are returned in advance like this:

in the datadefinitition:
- masterdata.rightuser_create: true
by reading the recordset:
- rightuser_update: true
- rightuser_delete: true

Using SQL Exists() as access method

To check the user right by foreign tables the table can be joined or checked by "EXISTS()":

Default Rights

This are the default rights in the data access class (masterdata.defaultrights=true (default)).

access matrix, cell number=Access check
Operation	0: public	1: admin	2: superuser	3: member
Create	0	1	2	2
Read	2	1	2	2
Update	0	1	2	10
Delete	0	1	2	10

Rights from the datadefinition overwrite the default rights. To allow the roles, public and member, to read all records in the object, add in the datadefinition:

Default rights as JSON

Access rights by master table

The access right of the table depend on another master table. In this case the master table needs to be checked for the access rights. This is declared in the datadefinition:

Link to master

The masterdatadefID declares the master datadefinition. The first right check is made by this datadefinition. The masterkey defines the colun of this table, which correspond to the master key column of the master datadefinition.

Access right: read

first the acces rights of the master is checked
The right of the master is heritated to the child by the method: 1000

Access rights: create, update, delete

first the right "Update" of the master is checked
The right of the master is heritated to the child by the method: 1000

In this example the the access for role 2 is granted to all CRUD methods. The role 3 herites the access from the master.

Using clients

Allow multiple clients to use your Web App. Each table has a clientID to differentiate the data from each other. Each Client has an own admin to govern his data and user. The user registers for 1 client. For each client he needs a different email.

Preparation

masterdata/_init.php:

The domain_rightmode==1 enables:

register: selection of the client
menu Admin: display "Clients"

Social groups (right groups)

Social groups are part of many social platforms like facebook and others. Properties of a social group are:

Title
Description
Owner
Picture
Members
- Admins
- Observers
- Members

This are the involved datadefinitions:

k8rightgroups: the group header (folder: masterdata)
k8rightmembers: the group members (folder: masterdata)
your table: for example the postings

The rights of the member are defined in the column "status" in "k8rightmembers".

The data access methods are:

13: all records with group membership
14: filtered to rightgroupID in SESSION

Create a group

Users with the role 30 have the right to create a right group
the creator is added as admin

Frontend

User
- Listing of his groups: membership
- Right group catalog
- Right group form
- Member list (request)
Admin
- Masterdata form with all groups

13, right group is added by the form

Attention:

rightgroupID needs to be a field in the form!

This is the "right" definition of the masterdata section:

14, filtered to rightgroup in SESSION

Attention:

right group has to be choosen in My data (SESSION variable)!

This is the "right" definition of the masterdata section:

By writing a new record the rightgroup is filled automatically with the SESSION rightgroupID.

Your datadefinition

Your table need to contain the column:

rightgroupID(INT), attention no update

You need to define special rights:

look above, rights for method 13 or 14

The Menu

The following variables are available in JavaScript:

dat_user.roles: create a special role for your rightgroups
rightgroupID: the SESSION[rightgroupID]
GLOBALS_config['rightidentifiers']: array with all identifiers of the rightgroups with membership.

Preparation (14)

masterdata/_init.php:

The domain_rightmode=2 enables:

my data: selection of the right group
data access class:
- validation: check the right group
- add: adds the rightgroupID from SESSION variable

Example for right groups (13): Bug Reports

Elements of Bug Reports:

Projects
- table: k8projects
- datadefID: projects
Bug Reports
- table: k8bugs
- datadefID: bugreports
Bug Fixes
- table: k8bugfixes
- datadefID: bugfixes

Data access:

Roles:
- 101: Bug fixes
rightgroups:
- identifier: Bug Reports

Right groups in master data form (13)

The project in the head is assigned to a rightgroup. Without project no new records are allowed. After selecting the rightgroup the rights are checked. The records are displayed. If the create right is granted, a new record can be added.

Example for projects (13)

extract of project datadefinition:

{
  "head_end":[
    "<script src='{{root}}{{projectpath}}/projects/projects_head_end.js'></script>"
  ],
  "settingsadditional":["k8rightgroups"],
  "masterdata":{
    "defaultrights":false,
    "rights":{
        "create":{
            "3":13
        },
        "read":{
            "3":13
        },
        "update":{
            "3":13
        },
        "delete":{
            "3":13
        }
    },
    "sql_derived":"SELECT k8projects.*,k8rightgroups.name as rightgroupname, username as creatorname,'' as k8select FROM k8projects LEFT OUTER JOIN k8rightgroups ON k8rightgroups.rightgroupID=k8projects.rightgroupID LEFT OUTER JOIN k8login ON k8projects.creatorID=k8login.userID WHERE 1=1",
    "sql_orderby":"projectname"
  },
  "tabulator": {
    "columns": [
      {
          "title": "rightgroupname",
          "field": "rightgroupname",
          "headerFilter": true
      }
    ]
  },
  "k8form": {
    "fields": [
      {
          "name": "rightgroupID",
          "type":"hidden",
          "label": "rightgroupID"
      },
      {
        "name": "rightgroupname",
        "type": "text",
        "label": "rightgroupname",
        "required":true,
        "attributes":{
          "list": "rightgroupname_datalist",
          "data-fieldlist":"",
          "autocomplete": "OFF"
        },
        "datalist":{
          "datadefID":"k8rightgroups",
          "IDfield":"rightgroupID",
          "onlyoptions":true
        }
      }
    ]
  }
}

The following rescources are added:

main

JavaSript file: projects_head_end.js
datadefinitions: k8rightgroups

masterdata

the rights are defined
the SQL statement is expanded with rightgroupname

tabulator

the rightgroupname is added to the columns

k8form

the form element rightgroupname is added as datalist

JavaScript:

The following configurations are made:

GLOBALS_rightmode==2 (14)
- rightgroupname is not displayed
GLOBALS_rightmode!=2 (13)
- Button "New" is activated

Company with employees

Allow companies to create an account and to add employees:

Own data access methods

CRUD Operation and data access methods
Operation	Function	Return values
Create	bRecordAccess()	true / false
Read	bRecordReadPermission()	$out[]: "clause": sql clause
Read	bRecordUDPermission()	$out[]: "select": sql condition for rightuser_update or rightuser_delete
Update	bRecordAccess()	true / false
Delete	bRecordAccess()	true / false

bRecordReadPermission()

This function creates a clause which which determines which records are read.

bRecordUDPermission()

This function is called 2 times and creates the columns for the data access "update" and "delete":

rightuser_update: 0/1
rightuser_delete: 0/1

In masterdata/_init.php the variable $GLOBALS['domain_includes'] allows to include own PHP files to write your own methods:

[RBAC_Read]:bRecordReadPermission()
[RBAC_RUD]: bRecordUDPermission()
[RBAC_CUD]: bRecordAccess()

childs

childs definition of the invoice eaxample:

By declaring a childs array or nested childs arrays, an object with several arrays can be read and written to the database. The definition of a child is same to the master. If no columns array is added for the child, it is generated on the fly out of the table structure. Please regard this properties:

fieldname: "items" in the invoice example for all records of the k8documentitems table
masterkey: the column corresponding to the key column of the parent table
richtcheck: 0, no rightcheck for the childs

Avoid SQL injection

What is SQL injection

SQL injection (SQLi) is a web security vulnerability that allows an attacker to interfere with the queries that an application makes to its database.

expression in clause
UNION expression
more to SQL inection:
- portswigger.net SQL injections

Avoid clause

In developement a clause is fast written down. To reduce the risk of SQL injection, the following measurements are taken in PHP:

clause in brakets
remove comments: /* */ -- #
use db->real_escape_string($value)

masterdata/_init.php

developement:

0: clause is ignored
1: clause is executed

Attention: If you turn off your developement, clauses from the query string are no more accepted!

Filtering

filters array

With the filters array the filter fields are transfered to the backend:

In PHP the clause is created like this:

$clause="rightgroupID=1";

There are 2 filters:

filters: for the WHERE clause of the main SQL statement. If it's a derived statement the column name is without table preset otherwise with table preset.
filtersin: by an SQL derived statment, it is placed to the inner SQL statement

Complex filtering with PHP include

datadefinition:

The SQL statment is build in PHP by an include file. The file is declared in the datadefinition:

Example: masterdata/k8rightmembers/k8rightmembers_getEntries.php

<?php
  if(getfromArray($_REQUEST,'buildclause',0)==1){
    $userID=getFromArray($_SESSION,'userID',0);
    $clausein=gsclauseand($clausein,'EXISTS(SELECT 1 FROM k8rightmembers membersadmin WHERE k8rightgroups.rightgroupID=membersadmin.rightgroupID and membersadmin.status=3 and membersadmin.userID='.$userID.')');
  }
  $this->sql_base = $this->datadefinition['masterdata']['sql_derived'];
  $this->sql_base = str_replace('§userID',getFromArray($_SESSION,'userID',0),$this->sql_base);
  $this->sql_base = str_replace('§domain_languageID',getfromArray($_SESSION,'domain_languageID',3),$this->sql_base);
  $this->sql_base = str_replace('§dateformat',$GLOBALS['sqldateformat'],$this->sql_base);
  $this->sql_base = str_replace('§select',iif(gbnull($select),'',','.$select),$this->sql_base);
  if(!gbnull($select))$this->sql_base = str_replace("'' as k8select",$select,$this->sql_base);
  if(!gbnull($from))$this->sql_base = str_replace('WHERE 1=1',$from.' WHERE 1=1',$this->sql_base);
  if(!gbnull($clausein))$this->sql_base = str_replace('1=1','('.$clausein.')',$this->sql_base);
  $this->sql_base="SELECT derived.* FROM (".$this->sql_base.") as derived";
  if(!gbnull($clause))$this->sql_base.=" WHERE ".$clause;
  if(gbnull($orderby)){
    $sql_orderby=getFromArray($this->datadefinition['masterdata'],'sql_orderby');
    if(!gbnull($sql_orderby)){
      $this->sql_base.=" ORDER BY ".$sql_orderby;
    }
  }else{
    $this->sql_base.=" ORDER BY ".$orderby;
  }

buildclause

By the query parameter "buildclause" an expression "EXITS(...)" is added to the SQL statement.

JavaScript:

In JavaScript the parameter buildclause is added to the property: data_readfilter.

ProcessData.php

Parameters

All ajax requests are send to "ProcessData.php".

process_action
datadefID
additional parameter:
- filters[]
- clause
- keyvalue
- arr
- prefix

GetObject

process_action=GetObject&datadefID=<yourdatadefID>

It returns the datadefininition in the data property:

ReadFilter

process_action=ReadFilter&datadefID=<yourdatadefID>

additional parameters:

filters[]
- field
- type
- value
filters[]
filtersin[]
clause
clausein
mytable_offset
mytable_limit: only set, if mytable_offset is also set

It return the the records:

If mytable_limit is not set the maximum returned records are limited by $gdatareadlimit(50).

Load

process_action=Load&datadefID=<yourdatadefID>

additional parameters:

keyvalue=<keyvalue>

It returns the result "bok", the error or the record "dat".

Save

process_action=Save&datadefID=<yourdatadefID>

additional parameters:

object with form fields

If the primary key=0 a new record is inserted, otherwise the record is updated. It returns an error or the saved record:

Delete

process_action=Delete&datadefID=<yourdatadefID>

additional parameters:

keyvalue=<primarykey>

It returns "bok" with the the keyvalue or 0 by an error with the error message:

Data access with JavaScript

To control the data access is it important to use a datadefinition with the data access class:

user rights are checked
only fields in the table are handeled, others are ignored
field type and size and addintional field rules are checked
additional validation with the inclued file is easily added

In this example the JavaScript command fetch() is used. To make it similar to the ajax getJSON() the function postData(url,data) is created. In PHP the function file_get_contents('php://input') is added to get the data.

Example: table basket

kitsamples/basket/basket.html

table, er_basket:

currentID INT PRI
foreignID INT
quantity DOUBLE 19,6
creatorID INT

datadefintion:

basket

GetObject

To get the data access it is not necessary to load the datadefinition first. If you want to display an element: form, list, master data form or html output, it is important to load the datadefnition.

ReadFilter

By ReadFilter you can add a filter array or a clause.

Load

Additionally to the url you have to define the key with the parameter:

keyvalue: 14

Save

This example have no key set, so the values will be inserted into the table.

If the key: "currentID":14 would be set, the corresponing record would be updated or an error displayed.

Delete

Additionally to the url you have to define the key with the parameter:

keyvalue: 14