#!/bin/sh # # license #################################################################### # # sqliteweb # Copyright (c) 2005, Noprianto # All rights reserved. # # Redistribution and use in source and binary forms, with or without # modification, are permitted provided that the following conditions are met: # # - Redistributions of source code must retain the above copyright notice, # this list of conditions and the following disclaimer. # # - Redistributions in binary form must reproduce the above copyright # notice, this list of conditions and the following disclaimer in the # documentation and/or other materials provided with the distribution. # # - Neither the name of the Noprianto nor the names of its # contributors may be used to endorse or promote products derived from # this software without specific prior written permission. # # THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND # CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED # WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED # WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A # PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL # THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY # DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR # CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, # PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF # USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER # CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN # CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING # NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE # USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY # OF SUCH DAMAGE. # # # changelog #################################################################### # # 0.1 (apprx. 22 july 2005): # - initial release # # 0.2 (30 july 2005): # - add insert into table # # 0.3 (30 july 2005): # - add delete from table # # 0.4 (31 july 2005): # - add simple url encoding/decoding # - fix filename on database creating, replace space with _ # - fix filename on database rename and delete # # bug: # - when creating database, cannot handle space in filename, so will replace space with _ # - might be in url encoding/decoding function # # todo: # - implement SQL # - optimize code # - add error code # # # configurations #################################################################### # _config() { SQLITE_BINARY="/usr/bin/sqlite3" DB_PATH="/tmp" DB_EXT="*.db" } # # internal functions #################################################################### _program_info() { PROGRAM_NAME="sqliteweb" PROGRAM_VERSION="0.4" PROGRAM_LICENSE="BSD" PROGRAM_COPYRIGHT="Noprianto, 2005" } _check_prereq() { _config ERRSTR="" test ! -x $SQLITE_BINARY && ERRSTR="$ERRSTR
Cannot locate SQLITE binary" test ! -r $DB_PATH && ERRSTR="$ERRSTR
Database directory cannot be read" test -z $ERRSTR && return 0 || return 1 } _cgi_header() { echo Content-type: text/html echo echo } _html_header() { echo " $PROGRAM_NAME version $PROGRAM_VERSION " } _html_footer() { echo " " } _print_server_info() { echo " $CONTENT_TYPE
Server information
VariableValue
SERVER_SOFTWARE$SERVER_SOFTWARE
SERVER_NAME$SERVER_NAME
SERVER_PROTOCOL$SERVER_PROTOCOL
SERVER_PORT$SERVER_PORT
REQUEST_METHOD$REQUEST_METHOD
GATEWAY_INTERFACE$GATEWAY_INTERFACE
PATH_INFO$PATH_INFO
PATH_TRANSLATED$PATH_TRANSLATED
REMOTE_HOST$REMOTE_HOST
REMOTE_ADDR$REMOTE_ADDR
REMOTE_IDENT$REMOTE_IDENT
SCRIPT_NAME$SCRIPT_NAME
QUERY_STRING$QUERY_STRING
CONTENT_TYPE
CONTENT_LENGTH$CONTENT_LENGTH
" } _simple_urlencode() { ret="$1" ret=`echo $ret | sed -e 's/%/%25/ig' -e 's/ /%20/ig' -e 's/!/%21/ig' -e 's/"/%22/ig' -e 's/#/%23/ig' -e 's/\\$/%24/ig' -e 's/&/%26/ig' -e "s/'/%27/ig" \ -e 's/(/%28/ig' -e 's/)/%29/ig' -e 's/*/%2A/ig' -e 's/+/%2B/ig' -e 's/,/%2C/ig' -e 's/-/%2D/ig' -e 's/\./%2E/ig' -e 's/\//%2F/ig' \ -e 's/:/%3A/ig' -e 's/;/%3B/ig' -e 's//%3E/ig' -e 's/?/%3F/ig' -e 's/@/%40/ig' -e 's/\[/%5B/ig' \ -e 's/\\\/%5C/ig' -e 's/\]/%5D/ig' -e 's/\^/%5E/ig' -e 's/_/%5F/ig' -e 's/\`/%60/ig' -e 's/{/%7B/ig' -e 's/|/%7C/ig' -e 's/|/%7D/ig' \ -e 's/~/%7E/ig' -e 's/&127;/%7F/ig'` echo $ret } _simple_urldecode() { ret="$1" ret=`echo $ret | sed -e 's/%5C/\\\/ig' -e 's/%20/ /ig' -e 's/%21/!/ig' -e 's/%22/"/ig' -e 's/%23/#/ig' -e 's/%24/$/ig' -e 's/%25/%/ig' -e 's/%26/\&/ig' \ -e "s/%27/'/ig" -e 's/%28/(/ig' -e 's/%29/)/ig' -e 's/%2A/*/ig' -e 's/%2B/+/ig' -e 's/%2C/,/ig' -e 's/%2D/-/ig' -e 's/%2E/./ig' \ -e 's/%2F/\//ig' -e 's/%3A/:/ig' -e 's/%3B/;/ig' -e 's/%3C//ig' -e 's/%3F/?/ig' -e 's/%40/@/ig' \ -e 's/%5B/\[/ig' -e 's/%5D/\]/ig' -e 's/%5E/^/ig' -e 's/%5F/_/ig' -e 's/%60/\`/ig' -e 's/%7B/{/ig' -e 's/%7C/|/ig' -e 's/%7D/}/ig' \ -e 's/%7E/~/ig' -e 's/%7F/&127;/ig' -e 's/+/ /ig'` echo $ret } # # public functions #################################################################### print_doc() { echo " 
$PROGRAM_NAME version $PROGRAM_VERSION
Copyright $PROGRAM_COPYRIGHT
Licensed under $PROGRAM_LICENSE

Features:
- DB
  - create
  - delete
  - rename
  - browse
- Table
  - create
  - drop
  - browse
  - insert into
  - delete from

Usage: 
- Use menu above to navigate

Installation:
- Make sure GNU coreutils and sed are installed (should be)
- Setup your web server
- Copy this software to your cgi-bin directory
- Create directory (to store databases, default: /tmp) readable/writeable by user who run web server
- Additionally, please change variable in _config function
- That's all.

Tools used:
- Shell script: BASH
- GNU Coreutils:
  - test
  - pwd
  - ls
  - wc
  - cut
  - rm
  - mv
  - touch
  - seq 
- sed
" } print_errmsg() { if [ ! -z "$1" ] then echo "
ERROR
$1
" fi } show_menu() { TASK="$PATH_INFO$QUERY_STRING" test -z $TASK && TASK=home TASK_MAIN=`echo $TASK | cut -d/ -f2` TASK_DETAIL=`echo $TASK | cut -d/ -f3` MENU="home browse create" echo " " for i in $MENU do bgcolor="" if [ $TASK_MAIN == $i ] then bgcolor="bgcolor='lightgrey'" fi echo "" done echo "
$i
" } go_home() { print_doc } go_browse() { if [ -z "$1" ] then OLDPWD=`pwd` DB_NUM=0 cd $DB_PATH ls $DB_EXT 1>/dev/null 2>&1 if [ $? -eq 0 ] then DB_NUM=`ls -l $DB_EXT | wc -l | tr -d [[:space:]]` fi echo "
Found $DB_NUM database(s) in $DB_PATH" echo "" echo "" for i in `ls $DB_EXT` do echo "" echo "" echo "" echo "" echo "" echo "" done echo "
databasebrowserenamedelete
$ibrowserenamedelete
" cd $OLDPWD else echo "
" echo Browse database "$1

" OLDPWD=`pwd` cd $DB_PATH TABLES=`$SQLITE_BINARY $1 .tables` echo "Add table" echo "" echo "" for i in $TABLES do echo "" echo "" echo "" echo "" echo "" echo "" done echo "
tablebrowsedropinsert
$ibrowsedropinsert
" cd $OLDPWD fi } go_delete() { if [ ! -z "$1" ] then echo "
Delete database $1

" OLDPWD=`pwd` cd $DB_PATH DB_DEL="$1" DB_DEL=$(_simple_urldecode "$DB_DEL") DB_DEL=`echo "$DB_DEL" | tr ' ' '_'` rm -f "$DB_DEL" if [ $? -eq 0 ] then echo "Status: Database deleted successfully" else echo "Status: Failed to delete database (error code: $?)" fi cd $OLDPWD fi } go_rename() { if [ ! -z "$1" ] then echo "
Rename database $1

" echo "
New database name
" fi } go_rename_action() { if [ ! -z "$1" ] && [ ! -z "$2" ] then OLD_NAME=$1 OLD_NAME=$(_simple_urldecode "$OLD_NAME") OLD_NAME=`echo "$OLD_NAME" | tr ' ' '_'` NEW_NAME=`echo $2 | cut -d= -f2` NEW_NAME=$(_simple_urldecode "$NEW_NAME") NEW_NAME=`echo "$NEW_NAME" | tr ' ' '_'` echo "
Rename database from $OLD_NAME to $NEW_NAME

" echo "Note: space in name will be replaced with _ (underscore)

" OLDPWD=`pwd` cd $DB_PATH mv $OLD_NAME $NEW_NAME if [ $? -eq 0 ] then echo "Status: Database renamed successfully" else echo "Status: Failed to rename database (error code: $?)" fi cd $OLDPWD fi } go_create() { if [ -z "$1" ] then echo "
Create new database

" echo "
Database name
" else DB_TO_CREATE=`echo $1 | cut -d= -f2` DB_TO_CREATE=$(_simple_urldecode "$DB_TO_CREATE") DB_TO_CREATE=`echo "$DB_TO_CREATE" | tr ' ' '_'` echo "
Create new database $DB_TO_CREATE

" echo "Note: space in name will be replaced with _ (underscore)

" OLDPWD=`pwd` cd $DB_PATH touch "$DB_TO_CREATE" if [ $? -eq 0 ] then echo "Status: Database created successfully" else echo "Status: Failed to create database (error code: $?)" fi cd $OLDPWD fi } go_browse_table() { echo "
Browse table $2 of database $1

" OLDPWD=`pwd` cd $DB_PATH field_count=`$SQLITE_BINARY $1 ".schema $2" | cut -d\( -f2 | cut -d\) -f1 | sed -e "s/primary\s*key//i" | tr ',' '\n' | wc -l` fields=`$SQLITE_BINARY $1 ".schema $2" | cut -d\( -f2 | cut -d\) -f1 | sed -e "s/primary\s*key//i"` REAL_FIELD_COUNT=0 for i in `seq 1 $field_count` do temp=`echo $fields | cut -d, -f$i` field_name=`echo $temp | cut -d' ' -f1 | tr -d '[[:space:]]'` if [ ! -z $field_name ] then let REAL_FIELD_COUNT=$REAL_FIELD_COUNT+1 fi done echo "" # this can be used if you only want to browse # when you need to add button or hyperlink for specified row, then problem will arise # $SQLITE_BINARY -html -header $1 "select * from $2" FIELDS="" for i in `seq 1 $field_count` do temp=`echo $fields | cut -d, -f$i` field_name=`echo $temp | cut -d' ' -f1 | tr -d '[[:space:]]'` if [ ! -z $field_name ] then echo "" [ ! -z $FIELDS ] && FIELDS="$FIELDS|$field_name" || FIELDS="$field_name" fi done echo "" row_count=`$SQLITE_BINARY $1 "select count(*) from $2" | tr -d '[[:space:]]'` if [ $row_count -gt 0 ] then for i in `seq 1 $row_count` do temp_field_info="field_count=$REAL_FIELD_COUNT" let temp2=$i-1 temp=`$SQLITE_BINARY $1 "select * from $2 limit $temp2,1 "` echo "" temp_field="" for j in `seq 1 $REAL_FIELD_COUNT` do temp3=`echo $temp | cut -d\| -f$j` temp3b=$(_simple_urlencode "$temp3") temp4=`echo $FIELDS| cut -d\| -f$j` temp_field="$temp4=$temp3b" temp_field_info="$temp_field_info&$temp_field" echo "" done echo "" echo "" done fi echo "
$field_namedelete
$temp3delete
" echo "

browse database $1" cd $OLDPWD } go_drop_table() { echo "
Drop table $2 of database $1

" OLDPWD=`pwd` cd $DB_PATH $SQLITE_BINARY $1 "drop table $2" 1>/dev/null 2>&1 if [ $? -eq 0 ] then echo "Status: Table $2 deleted successfully" else echo "Status: Failed to delete table $2" fi echo "

browse database $1" cd $OLDPWD } go_insert_table() { echo "
Insert into table $2 of database $1

" OLDPWD=`pwd` cd $DB_PATH field_count=`$SQLITE_BINARY $1 ".schema $2" | cut -d\( -f2 | cut -d\) -f1 | sed -e "s/primary\s*key//i" | tr ',' '\n' | wc -l` fields=`$SQLITE_BINARY $1 ".schema $2" | cut -d\( -f2 | cut -d\) -f1 | sed -e "s/primary\s*key//i"` REAL_FIELD_COUNT=0 for i in `seq 1 $field_count` do temp=`echo $fields | cut -d, -f$i` field_name=`echo $temp | cut -d' ' -f1 | tr -d '[[:space:]]'` if [ ! -z $field_name ] then let REAL_FIELD_COUNT=$REAL_FIELD_COUNT+1 fi done echo "
" echo "" echo "" echo "" for i in `seq 1 $field_count` do temp=`echo $fields | cut -d, -f$i` field_name=`echo $temp | cut -d' ' -f1 | tr -d '[[:space:]]'` if [ ! -z $field_name ] then echo "" echo "" echo "" echo "" fi done echo "" echo "
FieldValue
$field_name
 
" echo "
" cd $OLDPWD } go_insert_table_action() { echo "
Insert into table $2 of database $1

" FIELD_COUNT_TEMP=`echo $QUERY_STRING | cut -d\& -f1` FIELD_COUNT=`echo $FIELD_COUNT_TEMP | cut -d= -f2` FIELDS_TEMP=`echo $QUERY_STRING | cut -d\& -f2-` for i in `seq 1 $FIELD_COUNT` do temp=`echo $FIELDS_TEMP | cut -d\& -f$i` field=`echo $temp | cut -d= -f1 | tr -d '[[:space:]]'` value=`echo $temp | cut -d= -f2 | tr -d '[[:space:]]'` value=$(_simple_urldecode $value) echo $value if [ ! -z $field ] then if [ $i -lt $FIELD_COUNT ] then FIELDS="$FIELDS $field," VALUES="$VALUES '$value'," else FIELDS="$FIELDS $field" VALUES="$VALUES '$value'" fi fi done SQL_COMMAND="INSERT INTO $2($FIELDS) VALUES ($VALUES);" OLDPWD=`pwd` cd $DB_PATH echo $SQL_COMMAND echo "

" $SQLITE_BINARY $1 "$SQL_COMMAND" > /dev/null 2>&1 if [ $? -eq 0 ] then echo "Status: New row added successfully to table $2" else echo "Status: Failed to insert table $2" fi echo "

browse database $1" cd $OLDPWD } go_delete_from_table() { echo "
Delete from table $2 of database $1

" FIELD_COUNT_TEMP=`echo $QUERY_STRING | cut -d\& -f1` FIELD_COUNT=`echo $FIELD_COUNT_TEMP | cut -d= -f2` FIELDS_TEMP=`echo $QUERY_STRING | cut -d\& -f2-` for i in `seq 1 $FIELD_COUNT` do temp=`echo $FIELDS_TEMP | cut -d\& -f$i` field=`echo $temp | cut -d= -f1 | tr -d '[[:space:]]'` value=`echo $temp | cut -d= -f2 | tr -d '[[:space:]]'` value=$(_simple_urldecode $value) if [ ! -z $field ] then if [ $i -lt $FIELD_COUNT ] then DEL_TEMP="$DEL_TEMP $field='$value' and" else DEL_TEMP="$DEL_TEMP $field='$value'" fi fi done SQL_COMMAND="DELETE FROM $2 where $DEL_TEMP;" OLDPWD=`pwd` cd $DB_PATH echo $SQL_COMMAND echo "

" $SQLITE_BINARY $1 "$SQL_COMMAND" > /dev/null 2>&1 if [ $? -eq 0 ] then echo "Status: Data deleted successfully" else echo "Status: Failed to delete data" fi echo "

browse database $1" echo "

browse table $2" cd $OLDPWD } go_add_table() { if [ -z "$2" ] then echo "
Add table for database $1

" echo "
Table name
Field count
" else LOCAL_TASK_INFO=`echo $2 | cut -d\& -f1` LOCAL_TASK=`echo $LOCAL_TASK_INFO | cut -d= -f2` TABLE_NAME_INFO=`echo $2 | cut -d\& -f2` TABLE_NAME=`echo $TABLE_NAME_INFO | cut -d= -f2` FIELD_COUNT_INFO=`echo $2 | cut -d\& -f3` FIELD_COUNT=`echo $FIELD_COUNT_INFO | cut -d= -f2` case $LOCAL_TASK in step1) echo "
Please specify column name and type for table $TABLE_NAME

" echo "
" echo "" echo "" echo "" echo "" for i in `seq 1 $FIELD_COUNT` do echo "" echo "" echo "" echo "" echo "" echo "" done echo "" echo "" echo "" echo "
Field $i" echo "" echo "is primary key
" echo "
" ;; step2) echo "
Create table $TABLE_NAME ($FIELD_COUNT)

" CMD="CREATE TABLE $TABLE_NAME (" FIELDS=`echo $2 | cut -d\& -f4- | tr '\&' ' '` POS=0 POS_PK=0 for i in $FIELDS do KEY=`echo $i | cut -d= -f1 | cut -d_ -f1` VAL=`echo $i | cut -d= -f2` TEMP="" if [ $KEY == "field" ] then [ $POS -eq 0 ] && TEMP=$VAL || TEMP=",$VAL" PREV_FIELD=$VAL elif [ $KEY == "type" ] then TEMP="$TEMP $VAL" elif [ $KEY == "pk" ] then if [ $POS_PK -eq 0 ] then PK_TEMP="$PK_TEMP $PREV_FIELD" POS_PK=1 else PK_TEMP="$PK_TEMP, $PREV_FIELD" fi fi CMD="$CMD $TEMP" let POS=$POS+1 done PK_TEMP_TEST=`echo $PK_TEMP | tr -d [[:space:]]` test -z $PK_TEMP_TEST && CMD="$CMD);" || CMD="$CMD, primary key ($PK_TEMP));" echo $CMD echo "

" OLDPWD=`pwd` cd $DB_PATH $SQLITE_BINARY $1 "$CMD" if [ $? -eq 0 ] then echo "Status: Table $TABLE_NAME created successfully" else echo "Status: Failed to create $TABLE_NAME" fi cd $OLDPWD echo "

browse database $1" esac fi } # # main functions #################################################################### main() { _cgi_header _program_info _html_header _check_prereq if [ $? -ne 0 ] then print_errmsg "$ERRSTR" else show_menu TASK_MAIN=`echo $TASK | cut -d/ -f2` TASK_DETAIL=`echo $TASK | cut -d/ -f3` TASK_EXTRAINFO=`echo $TASK | cut -d/ -f4` case $TASK_MAIN in home) go_home ;; browse) go_browse $TASK_DETAIL ;; create) go_create $TASK_DETAIL ;; delete) go_delete $TASK_DETAIL ;; rename) go_rename $TASK_DETAIL ;; rename_action) go_rename_action $TASK_DETAIL $TASK_EXTRAINFO ;; browse_table) go_browse_table $TASK_DETAIL $TASK_EXTRAINFO ;; drop_table) go_drop_table $TASK_DETAIL $TASK_EXTRAINFO ;; insert_table) go_insert_table $TASK_DETAIL $TASK_EXTRAINFO ;; insert_table_action) go_insert_table_action $TASK_DETAIL $TASK_EXTRAINFO ;; delete_from_table) go_delete_from_table $TASK_DETAIL $TASK_EXTRAINFO ;; add_table) go_add_table $TASK_DETAIL $TASK_EXTRAINFO ;; esac fi _html_footer } # # main program #################################################################### main