Multitenant SaaS with MariaDB and Apache

--Create velydb database hosting application data (if it doesn't exist): create database if not exists db_multitenant_SaaS; create user if not exists vely identified by 'your_password'; grant create,alter,drop,select,insert,delete,update on db_multitenant_SaaS.* to vely; -- Create database objects needed for the application (eg. tables, indexes): use db_multitenant_SaaS; source setup.sql; exit

create table if not exists notes (dateOf datetime, noteId bigint auto_increment primary key, userId bigint, note varchar(1000)); create table if not exists users (userId bigint auto_increment primary key, email varchar(100), hashed_pwd varchar(100), verified smallint, verify_token varchar(30), session varchar(100)); create unique index if not exists users1 on users (email);

#ifndef _VV_LOGIN #define _VV_LOGIN typedef struct s_reqdata { bool displayed_logout; // true if Logout link displayed bool is_logged_in; // true if session verified logged-in char *sess_userId; // user ID of current session char *sess_id; // session ID } reqdata; void login_or_signup (); #endif

#include "vely.h" #include "login.h" // _before() is a before-request-handler. It always executes before // any other code that handles a request. It's a good place for any // kind of request-wide setting or data initialization void _before() { // Output HTTP header out-header default reqdata *rd; // this is global request data, see login.h // allocate memory for global request data, will be automatically deallocated // at the end of request new-mem rd size sizeof(reqdata) // initialize flags rd->displayed_logout = false; rd->is_logged_in = false; // set the data we created to be global request data, accessible // from any code that handles a request set-req data rd // check if session exists (based on cookies from the client) // this executes before any other request-handling code, making it // easier to just have session information ready _check_session (); }

#include "vely.h" #include "login.h" // Check if session is valid void _check_session () { // Get global request data reqdata *rd; get-req data to rd // Get cookies from user browser get-cookie rd->sess_userId="sess_userId" get-cookie rd->sess_id="sess_id" if (rd->sess_id[0] != 0) { // Check if session ID is correct for given user ID char *email; run-query @db_multitenant_SaaS = "select email from users where userId='%s' and session='%s'" output email : rd->sess_userId, rd->sess_id row-count define rcount query-result email to email end-query if (rcount == 1) { // if correct, set logged-in flag rd->is_logged_in = true; // if Logout link not display, then display it if (rd->displayed_logout == false) { get-app path to define upath @Hi <<p-out email>>! <a href="<<p-out upath>>/login/actions/logout">Logout</a><br/> rd->displayed_logout = true; } } else rd->is_logged_in = false; } }

#include "vely.h"
#include "login.h"

// Handle session maintenance, login, logout, session verification
// for any multitenant Cloud application
%% /login
    // Get URL input parameter "actions"
    task-param actions

    // Get global request data, we record session information in it, so it's handy
    reqdata *rd;
    get-req data to rd

    // If session is already established, the only reason why we won't proceed to
    // application home is if we're logging out
    if (rd->is_logged_in) {
        if (strcmp(actions, "logout")) {
            _show_home();
            exit-request
        }
    }

    // Application screen to get started. Show links to login or signup and show
    // home screen appropriate for this
    if-task "begin"
        _show_home();
        exit-request

    // Start creating new user. Ask for email and password, then proceed to create user
    // when this form is submitted.
    else-task "newuser"
        @Create New User<hr/>
        @<form action="<<p-path>>/login/actions/createuser" method="POST">
        @<input name="email" type="text" value="" size="50" maxlength="50" required autofocus placeholder="Email">
        @<input name="pwd" type="password" value="" size="50" maxlength="50" required placeholder="Password">
        @<input type="submit" value="Sign Up">
        @</form>

    // Verify code sent to email by user. The code must match, thus verifying email address    
    else-task "verify"
        input-param code
        input-param email
        // Get verify token based on email
        run-query @db_multitenant_SaaS = "select verify_token from users where email='%s'" output db_verify : email
            query-result db_verify to define db_verify
            // Compare token recorded in database with what user provided
            if (!strcmp (code, db_verify)) {
                @Your email has been verifed. Please <a href="<<p-path>>/login">Login</a>.
                // If matches, update user info to indicate it's verified
                run-query @db_multitenant_SaaS no-loop = "update users set verified=1 where email='%s'" : email
                exit-request
            }
        end-query
        @Could not verify the code. Please try <a href="<<p-path>>/login">again</a>.
        exit-request

    // Create user - this runs when user submits form with email and password to create a user     
    else-task "createuser"
        input-param email
        input-param pwd
        // create hashed (one-way) password
        hash-string pwd to define hashed_pwd
        // generate random 5 digit string for verify code
        random-string to define verify length 5 number
        // create user: insert email, hashed password, verification token. Current verify status is 0, or not verified
        begin-transaction @db_multitenant_SaaS
        run-query @db_multitenant_SaaS no-loop = "insert into users (email, hashed_pwd, verified, verify_token, session) values ('%s', '%s', '0', '%s', '')" : email, hashed_pwd, verify affected-rows define arows error define err on-error-continue
        if (strcmp (err, "0") || arows != 1) {
            // if cannot add user, it probably doesn't exist. Either way, we can't proceed.
            login_or_signup();
            @User with this email already exists.
            rollback-transaction @db_multitenant_SaaS
        } else {
            // Create email with verification code and email it to user
            write-string define msg
                @From: vely@vely.dev
                @To: <<p-out email>>
                @Subject: verify your account
                @
                @Your verification code is: <<p-out verify>>
            end-write-string
            exec-program "/usr/sbin/sendmail" args "-i", "-t" input msg status define st
            if (st != 0) {
                @Could not send email to <<p-out email>>, code is <<p-out verify>>
                rollback-transaction @db_multitenant_SaaS
                exit-request
            }
            commit-transaction @db_multitenant_SaaS
            // Inform the user to go check email and enter verification code
            @Please check your email and enter verification code here:
            @<form action="<<p-path>>/login/actions/verify" method="POST">
            @<input name="email" type="hidden" value="<<p-out email>>">
            @<input name="code" type="text" value="" size="50" maxlength="50" required autofocus placeholder="Verification code">
            @<button type="submit">Verify</button>
            @</form>
        }

    // This runs when logged-in user logs out.    
    else-task "logout"
        // Update user table to wipe out session, meaning no such user is logged in
        if (rd->is_logged_in) {
            run-query @db_multitenant_SaaS = "update users set session='' where userId='%s'" : rd->sess_userId no-loop affected-rows define arows
            if (arows == 1) {
                rd->is_logged_in = false; // indicate user not logged in
                @You have been logged out.<hr/>
            }
        }
        _show_home();

    // Login: this runs when user enters user name and password
    else-task "login"
        input-param pwd
        input-param email
        // create one-way hash with the intention of comparing with user table - password is NEVER recorded
        hash-string pwd to define hashed_pwd
        // create random 30-long string for session ID
        random-string to rd->sess_id length 30
        // Check if user name and hashed password match
        run-query @db_multitenant_SaaS = "select userId from users where email='%s' and hashed_pwd='%s'" output sess_userId : email, hashed_pwd
            query-result sess_userId to rd->sess_userId
            // If match, update user table with session ID
            run-query @db_multitenant_SaaS no-loop = "update users set session='%s' where userId='%s'" : rd->sess_id, rd->sess_userId affected-rows define arows
            if (arows != 1) {
                @Could not create a session. Please try again. <<.login_or_signup();>> <hr/>
                exit-request
            }
            // Set user ID and session ID as cookies. User's browser will return those to us with every request
            set-cookie "sess_userId" = rd->sess_userId path "/"
            set-cookie "sess_id" = rd->sess_id path "/"
            // Display home, make sure session is correct first and set flags
            _check_session();
            _show_home();
            exit-request
        end-query
        @Email or password are not correct. <<.login_or_signup();>><hr/>

    // Login screen, asks user to enter user name and password    
    else-task ""
        login_or_signup();
        @Please Login:<hr/>
        @<form action="<<p-path>>/login/actions/login" method="POST">
        @<input name="email" type="text" value="" size="50" maxlength="50" required autofocus placeholder="Email">
        @<input name="pwd" type="password" value="" size="50" maxlength="50" required placeholder="Password">
        @<button type="submit">Go</button>
        @</form>
    else-task other
        // do not display anything
    end-task
%%

// Display Login or Sign Up links
void login_or_signup() {
        @<a href="<<p-path>>/login">Login</a> &nbsp; &nbsp; <a href="<<p-path>>/login/actions/newuser">Sign Up</a><hr/>
}

#include "vely.h"
#include "login.h"

// Notes application in a multitenant Cloud 
%% notes
    // get global request data
    reqdata *rd;
    get-req data to rd

    // If session invalid, display Login or Signup
    if (!rd->is_logged_in) {
        login_or_signup();
    }
    // Greet the user
    @<h1>Welcome to Notes!</h1><hr/>
    // If not logged in, exit - this ensures security verification of user's identity
    if (!rd->is_logged_in) {
        exit-request
    }
    // Get URL parameter that tells Notes what to do
    task-param subreqs
    // Display actions that Notes can do (add or list notes)
    @<a href="<<p-path>>/notes/subreqs/add">Add Note</a> <a href="<<p-path>>/notes/subreqs/list">List Notes</a><hr/>


    // List all notes for this user
    if-task "list"
        // select notes for this user ONLY
        run-query @db_multitenant_SaaS = "select dateOf, note, noteId from notes where userId='%s' order by dateOf desc" : rd->sess_userId output dateOf, note, noteId
            query-result dateOf to define dateOf
            query-result note to define note
            query-result noteId to define noteId
            (( define web_enc
            p-web note
            ))
            // change new lines to <br/> with fast cached Regex
            match-regex "\n" in web_enc replace-with "<br/>\n" result define with_breaks status define st cache
            if (st == 0) with_breaks = web_enc; // nothing was found/replaced, just use original
            // Display a note
            @Date: <<p-out dateOf>> (<a href="<<p-path>>/notes/subreqs/delete_note_ask?note_id=<<p-out noteId>>">delete note</a>)<br/>
            @Note: <<p-out with_breaks>><br/>
            @<hr/>
        end-query

    // Ask to delete a note
    else-task "delete_note_ask"
        input-param note_id
        @Are you sure you want to delete a note? Use Back button to go back,\
            or <a href="<<p-path>>/notes/subreqs/delete_note?note_id=<<p-out note_id>>">delete note now</a>.

    // Delete a note
    else-task "delete_note"
        input-param note_id
        // Delete note
        run-query @db_multitenant_SaaS = "delete from notes where noteId='%s' and userId='%s'" : note_id, rd->sess_userId \
            affected-rows define arows no-loop error define errnote
        // Inform user of status
        if (arows == 1) {
            @Note deleted
        } else {
            @Could not delete note (<<p-out errnote>>)
        }

    // Add a note
    else-task "add_note"
        // Get URL POST data from note form
        input-param note
        // Insert note under this user's ID
        run-query @db_multitenant_SaaS = "insert into notes (dateOf, userId, note) values (now(), '%s', '%s')" : rd->sess_userId, note \
            affected-rows define arows no-loop error define errnote
        // Inform user of status
        if (arows == 1) {
            @Note added
        } else {
            @Could not add note (<<p-out errnote>>)
        }

    // Display an HTML form to collect a note, and send it back here (with subreqs="add_note" URL param)
    else-task "add"
        @Add New Note
        @<form action="<<p-path>>/notes/subreqs/add_note" method="POST">
        @<textarea name="note" rows="5" cols="50" required autofocus placeholder="Enter Note"></textarea>
        @<button type="submit">Create</button>
        @</form>
    else-task other
        // do not display anything
    end-task
%%