Take the 2-minute tour ×
Ask Ubuntu is a question and answer site for Ubuntu users and developers. It's 100% free, no registration required.

I have an Ubuntu server that is hosting my web application.

But due to some security reasons it is not visible through Internet. The customer needs to update the database and update some values in it, but due to same security reasons I am not allowed there any more so they asked me to create an executable file (like batch files in windows) that will update the database, hand it to the authorities and explain it to them so they can update the database.

Say I have a database named myDb with a user say myUser and myPassword, and I need to execute this query:

UPDATE myDb.member SET member_creditDueDate='2025-09-21 12:00:00';

How can I do this?

share|improve this question

2 Answers 2

up vote 6 down vote accepted

Making a command script

If the script you have to create is just to be executed as-is by people having access to the server and if the data to be put into the DB are delivered to you (so nothing dynamic), you would have to make a bash script like this :

#!/bin/bash

if [ "x$1" == "x" ]; then
   echo "Usage: $0 <SQL commands file>"
   exit 1
fi

if [ ! -f "$1" ]; then
   echo "The file specified in parameter ($1) does not exist or is not readable"
   exit 2
fi

USER=myUser
PASS=myPassword
DB=myDB
SERVER=ipMysql

mysql -u$USER -p$PASS -h$ipMysql $DB < $1    

Copy all the above in one test file that you can call dataUpload.sh and then make it executable : sudo chmod +x dataUpload.sh.

What does this script do?

  1. Check that you give a mandatory parameter : the path to a text file containing SQL command to update data in the DB (see below)
  2. Check that the file given in parameter is accessible by the script
  3. Set some variable with the credential to connect to the DB (attention that this is a quick approach here not taking some security aspect into consideration : for instance the password will be visible in clear text in the script and in the process list - ps -ef or ps auxwww when the script will run. You may look for a more secure approach)
  4. Load the SQL commands of the SQL file into the DB using the command line mysql client.

A SQL command file

This SQL command file is just a text file containing all the SQL command to INSERT or UPDATE data you would have done yourself if you could access the server. One command by line, like in this example:

UPDATE myDb.member SET member_creditDueDate='2025-09-21 12:00:00';
UPDATE myDb.member SET member_credutDueDate='2025-10-11 00:00:00';

and so on.

If you write a file, lets say, called 20140620_dbupdate.sql, then the person who has access to the DB will just have to execute dataUpload.sh 20140620_dbupdate.sql at the command line prompt.

By splitting the shell and SQL commands, you can:

  1. easily test the logic that loads data into the DB
  2. pass only once the executable script
  3. for later update, just focus on building a SQL commands file

More to do

This is a first quick approach to solve your problem, to be exhaustive I would need to cover :

  • Securing the password
  • Making use of SQL transactions
  • More error detection and reporting in the script
share|improve this answer

Have you tried the shell script for this it is same as batch script in windows (but more powerfull)

share|improve this answer
    
no, do you know how to do it with shell script? i am not sure if it can work with dbms –  dave Jun 20 at 7:53

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.