1#! /bin/sh 2## runsql.sh -- HotCRP database shell 3## Copyright (c) 2006-2018 Eddie Kohler; see LICENSE. 4 5export LC_ALL=C LC_CTYPE=C LC_COLLATE=C CONFNAME= 6if ! expr "$0" : '.*[/]' >/dev/null; then LIBDIR=./ 7else LIBDIR=`echo "$0" | sed 's,^\(.*/\)[^/]*$,\1,'`; fi 8. ${LIBDIR}dbhelper.sh 9export PROG=$0 10 11usage () { 12 if [ -z "$1" ]; then status=1; else status=$1; fi 13 echo "Usage: $PROG [-n CONFNAME | -c CONFIGFILE] [MYSQL-OPTIONS] 14 $PROG --show-password EMAIL 15 $PROG --set-password EMAIL [PASSWORD] 16 $PROG --create-user EMAIL [COLUMN=VALUE...]" | 17 if [ $status = 0 ]; then cat; else cat 1>&2; fi 18 exit $status 19} 20 21export FLAGS= 22mode= 23makeuser= 24makeusercols= 25makeuservals= 26makeuserpassword=true 27pwuser= 28pwvalue= 29cmdlinequery= 30options_file= 31while [ $# -gt 0 ]; do 32 shift=1 33 case "$1" in 34 --show-password=*|--show-p=*|--show-pa=*|--show-pas=*|--show-pas=*|--show-pass=*|--show-passw=*|--show-passwo=*|--show-passwor=*) 35 test -z "$mode" || usage 36 pwuser="`echo "+$1" | sed 's/^[^=]*=//'`"; mode=showpw;; 37 --show-password|--show-p|--show-pa|--show-pas|--show-pass|--show-passw|--show-passwo|--show-passwor) 38 test "$#" -gt 1 -a -z "$mode" || usage 39 pwuser="$2"; shift; mode=showpw;; 40 --set-password|--set-p|--set-pa|--set-pas|--set-pass|--set-passw|--set-passwo|--set-passwor) 41 test "$#" -gt 1 -a -z "$mode" || usage 42 pwuser="$2"; pwvalue="$3"; shift; shift; mode=setpw;; 43 --create-user) 44 test "$#" -gt 1 -a -z "$mode" || usage 45 makeuser="$2"; mode=makeuser; shift;; 46 --show-opt=*|--show-option=*) 47 test -z "$mode" || usage 48 optname="`echo "+$1" | sed 's/^[^=]*=//'`"; mode=showopt;; 49 --show-opt|--show-option) 50 test "$#" -gt 1 -a -z "$mode" || usage 51 optname="$2"; shift; mode=showopt;; 52 --json-dbopt) 53 test -z "$mode" || usage 54 mode=json_dbopt;; 55 -c|--co|--con|--conf|--confi|--config|-c*|--co=*|--con=*|--conf=*|--confi=*|--config=*) 56 parse_common_argument "$@";; 57 -n|--n|--na|--nam|--name|-n*|--n=*|--na=*|--nam=*|--name=*) 58 parse_common_argument "$@";; 59 --no-password-f|--no-password-fi|--no-password-fil|--no-password-file) 60 parse_common_argument "$@";; 61 --help) usage 0;; 62 -*) 63 if [ "$mode" = cmdlinequery ]; then 64 cmdlinequery="$cmdlinequery $1" 65 else 66 FLAGS="$FLAGS $1" 67 fi;; 68 *) 69 if [ "$mode" = makeuser ] && expr "$1" : "[a-zA-Z0-9_]*=" >/dev/null; then 70 colname=`echo "$1" | sed 's/=.*//'` 71 collen=`echo "$colname" | wc -c` 72 collen=`expr $collen + 1` 73 colvalue=`echo "$1" | tail -c +$collen` 74 makeusercols="$makeusercols,$colname" 75 makeuservals="$makeuservals,'`echo "$colvalue" | sql_quote`'" 76 test "$colname" = password && makeuserpassword=false 77 elif [ "$mode" = "" ]; then 78 mode=cmdlinequery 79 cmdlinequery="$1" 80 elif [ "$mode" = cmdlinequery ]; then 81 cmdlinequery="$cmdlinequery $1" 82 else usage; fi 83 esac 84 shift $shift 85done 86 87if ! findoptions >/dev/null; then 88 echo "runsql.sh: No options file" 1>&2 89 exit 1 90fi 91 92get_dboptions runsql.sh 93 94if test "$mode" = json_dbopt; then 95 eval "x0=$dbname;x1=$dbuser;x2=$dbpass;x3=$dbhost" 96 echo_n '{"dbName":'; echo_n "$x0" | json_quote 97 echo_n ',"dbUser":'; echo_n "$x1" | json_quote 98 echo_n ',"dbPassword":'; echo_n "$x2" | json_quote 99 echo_n ',"dbHost":'; if [ -z "$x3" ]; then echo_n 'null'; else echo_n "$x3" | json_quote; fi 100 echo '}' 101 exit 102fi 103 104check_mysqlish MYSQL mysql 105set_myargs "$dbuser" "$dbpass" 106exitval=0 107 108if test -n "$pwuser"; then 109 pwuser="`echo "+$pwuser" | sed -e 's,^.,,' | sql_quote`" 110 if test "$mode" = showpw; then 111 echo "select concat(email, ',', if(substr(password,1,1)=' ','<HASH>',coalesce(password,'<NULL>'))) from ContactInfo where email like '$pwuser' and disabled=0" | eval "$MYSQL $myargs -N $FLAGS $dbname" 112 else 113 showpwvalue=n 114 if [ -z "$pwvalue" ]; then 115 pwvalue=`generate_random_ints | generate_password 12` 116 showpwvalue=y 117 fi 118 pwvalue="`echo "+$pwvalue" | sed -e 's,^.,,' | sql_quote`" 119 query="update ContactInfo set password='$pwvalue', passwordTime=UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) where email='$pwuser'; select row_count()" 120 nupdates="`echo "$query" | eval "$MYSQL $myargs -N $FLAGS $dbname"`" 121 if [ $nupdates = 0 ]; then 122 echo "no such user" 1>&2; exitval=1 123 elif [ $nupdates != 1 ]; then 124 echo "$nupdates users updated" 1>&2 125 fi 126 if [ "$showpwvalue" = y -a $nupdates != 0 ]; then 127 echo "Password: $pwvalue" 1>&2 128 fi 129 fi 130elif test "$mode" = showopt; then 131 if test -n "`echo "$optname" | tr -d A-Za-z0-9._:-`"; then 132 echo "bad option name" 1>&2; exitval=1 133 else 134 opt="`getdbopt "$optname" 2>/dev/null`" 135 optopt="`echo "select data from Settings where name='opt.$optname'" | eval "$MYSQL $myargs -N $FLAGS $dbname"`" 136 if test -n "$optopt"; then eval "echo $optopt"; else eval "echo $opt"; fi 137 fi 138elif test "$mode" = makeuser; then 139 if $makeuserpassword; then 140 makeusercols="$makeusercols,password" 141 makeuservals="$makeuservals,''" 142 fi 143 echo "insert into ContactInfo (email$makeusercols) values ('`echo "$makeuser" | sql_quote`'$makeuservals)" | eval "$MYSQL $myargs -N $FLAGS $dbname" 144elif test "$mode" = cmdlinequery; then 145 if test -n "$PASSWORDFILE"; then ( sleep 0.3; rm -f $PASSWORDFILE ) & fi 146 echo "$cmdlinequery" | eval "$MYSQL $myargs $FLAGS $dbname" 147else 148 if test -n "$PASSWORDFILE"; then ( sleep 0.3; rm -f $PASSWORDFILE ) & fi 149 eval "$MYSQL $myargs $FLAGS $dbname" 150fi 151 152test -n "$PASSWORDFILE" && rm -f $PASSWORDFILE 153exit $exitval 154